Automatically run a cr without parameters? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Automatically run a cr without parameters?

Author

Topic

swenri
Yak Posting Veteran

72 Posts

Posted - 2012-12-17 : 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]

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-12-19 : 11:12:33

Are you defo using CR Server and not another version...only Crystal report server automates reports.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources