|
swenri
Yak Posting Veteran
60 Posts |
 Posted - 12/17/2012 : 15:33:32
|
Hi,
I've created a crystal report using the below stored procedure.I'm trying to schedule the report on the crystal report server as a weekly report. But, the report doesn't get fired off at the specified time. When I run the report manually it runs fine and I get the results. The report doesn't have any parameters, my question is how do I get the report to fire off at a specified time automatically? Thank you for the help.
CREATE PROC [dbo].[SP_Report]
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @Location varchar(255)
--if (ISNULL(@Location,'')='')
begin
/* Declare Date Parameters */
Declare @FromDate datetime
Declare @ToDate datetime
Declare @TimeFrame VARCHAR(50)
SET @TimeFrame='2' --Weekly
if @TimeFrame='2' -- Weekly
Begin
select @FromDate =convert(datetime,convert(varchar(10),dateadd(dd,-7, getdate()), 101))
select @ToDate=dateadd(ss, -1, convert(datetime,convert(varchar(10), getdate(),101)))
End
SELECT
("MO_Demographics"."LastName" + ' ' + "MO_Demographics"."FirstName") AS PatientName,
("MO_Demographics".DateOfBirth) AS DOB,
"MO_Demographics"."MRN",
"MO_Demographics"."Race",
("SPDeliveryLog_Table"."GA") AS Gestational_Age,
("SPDeliveryLog_Table"."TimeOfDelivery") AS DeliveryTime,
("SPDeliveryLog_Table"."NeonateGender") AS NeonateSex,
("SPDeliveryLog_Table"."OB Formula TPAL (postpartum)") AS Obstetric_History,
--("SPDeliveryLog_Table"."Parity (postpartum)") AS OB_Parity,
("SPDeliveryLog_Table"."Delivering Providers") AS Delivering_MD_CNM_Attending_Surgeon,
("SPDeliveryLog_Table"."Assistants") AS Delivering_MD_CNM_Assitant_Surgeon,
("SPDeliveryLog_Table"."Nurses") AS Primary_Nurse_Circulating_Nurse,
("SPDeliveryLog_Table"."Type of Delivery") AS TypeofDelivery,
"SPDeliveryLog_Table"."FetalPresentation",
("MO_Neonate"."LiveBornStillBorn") AS LiveBorn,
"MO_Neonate"."BirthWeightGrams",
"SPDeliveryLog_Table"."BirthWeight(lb+oz)" AS BirthWeight,
"MO_Neonate"."Apgar1Min",
"MO_Neonate"."Apgar5Min",
"MO_Neonate"."Apgar10Min",
(select top 1 IPR_Neonate_Interventions_T_Delivery_Report_Neonate.Mom_And_Infant_Bonding
from IPR_Neonate_Interventions_T_Delivery_Report_Neonate
where MO_Demographics.SessionID = IPR_Neonate_Interventions_T_Delivery_Report_Neonate.SessionID) AS Moms_abdomen_directly,
(select top 1 IPR_General_Description_FU_Oth_Int_Del_Rep_Neo.[General_Description_f_Other_Intervention_Delivery__1_0]
from IPR_General_Description_FU_Oth_Int_Del_Rep_Neo
where MO_Demographics.SessionID = IPR_General_Description_FU_Oth_Int_Del_Rep_Neo.SessionID) AS Other,
"MO_Neonate"."InfantDisposition",
(select top 1 IPR_Feeding_Method_T.Infant_Feeding_Method
from IPR_Feeding_Method_T
where MO_Demographics.SessionID = IPR_Feeding_Method_T.SessionID) AS Feeding_Methodin_LDR
FROM
MO_Demographics
LEFT OUTER JOIN MO_Neonate
ON "MO_Demographics"."SessionID"="MO_Neonate"."sessionid"
LEFT OUTER JOIN BLSession_Extended
ON "MO_Demographics"."SessionID"= "BLSession_Extended"."sessionid"
LEFT OUTER JOIN "SPDeliveryLog_Table"
ON "MO_Demographics"."SessionID" = "SPDeliveryLog_Table"."sessionid"
LEFT OUTER JOIN "IPR_General_Description_FU_Oth_Int_Del_Rep_Neo" --Check for other in the specifications Sudha 10182012
ON "MO_Demographics"."SessionID" = "IPR_General_Description_FU_Oth_Int_Del_Rep_Neo"."sessionid"
WHERE SPDeliveryLog_Table.TimeOfDelivery
BETWEEN CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-7,getdate()),101))+ '00:00:00 AM')
AND CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'11:59:59 PM')
ORDER BY MO_Demographics.MRN,
"SPDeliveryLog_Table"."TimeOfDelivery" ASC
END
END
SET NOCOUNT OFF
SET ANSI_NULLS OFF
GO
GO
GRANT EXECUTE ON [dbo].[SP_Report] TO [Public]
|
|
Automatically run a cr without parameters?
