How to schedule a report automatically without changing the parameters every month the server ?

[swenri1]

Hi All:

I have a crystal report which has 4 different dynamic parameters which the user has to choose from How to I automate this report on the crystal server so that the report is generated automatically without the user requiring to change the parameters every month ? Thank you for all the help.... Su

Below is the code that I wrote
USE [DatamartDB2]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[SP_ARRA_MeaningfulUse_Monthly]
(@TimeFrame2 varchar(3))


AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON


Declare @StartTime DATETIME
Declare @EndTime DATETIME
Declare @TimeFrame VARCHAR(50)
Declare @FacilityID INT
Declare @SessionID INT
Declare @SelectMeasure VARCHAR(100)

SET @FacilityID = -99
SET @SessionID = -99
SET @TimeFrame ='Last Month'
SET @SelectMeasure = 'ALL'


Declare @FromDate datetime
Declare @ToDate datetime
--Declare @TimeFrame varchar(2)

if @TimeFrame2='3' -- Monthly
Begin
select @FromDate = convert(datetime,convert(varchar(10),dateadd(dd,(-1*(day(getdate()))+1),dateadd(m,-1,getdate())) ,101))
select @ToDate= dateadd(ss,-1,dateadd(dd,(-1*(day(getdate()))+1),convert(datetime,convert(varchar(10),getdate(),101))))

End


DECLARE @StartTimeOut datetime
DECLARE @EndTimeOut datetime
DECLARE @TimeFrameOut varchar(50)

EXEC [dbo].[SPGetReportTimeFrame] @StartTime, @EndTime, @TimeFrame, @StartTimeOut output, @EndTimeOut output, @TimeFrameOut output
SELECT @StartTimeOut = @FromDate SELECT @EndTimeOut = @ToDate

--SELECT @FromDate
--SELECT @ToDate
--SELECT @TimeFrameOut

IF (@SessionID = -99
AND EXISTS (select top 1 1
from ott_objecttable
where DestinationTableName = 'SP_ARRA_MeaningfulUse_Table'
AND (IsStatic = 1 OR IsInUse = 1 )
AND LastSyncTime IS NOT NULL
)
)
BEGIN
IF dbo.EFGetSiteName() <> 'MMC'
BEGIN
SELECT *
FROM SP_ARRA_MeaningfulUse_Table
WHERE (
@FacilityID = -99
OR Facilityid = @FacilityID
)
AND [Chart Archive Time] between @StartTimeOut and @EndTimeOut
AND @SelectMeasure in ('All',[Category])
END
ELSE --dbo.EFGetSiteName() = 'MMC'
BEGIN
SELECT *
FROM SP_ARRA_MeaningfulUse_Table
WHERE (
@FacilityID = -99
OR Facilityid = @FacilityID
)
AND [Chart Start Time] between @StartTimeOut and @EndTimeOut
AND @SelectMeasure in ('All',[Category])
END
END
ELSE
BEGIN

CREATE TABLE #TMP_Session
(
SessionID int,
MRN varchar(255),
PatientID int,
[Chart Archive Time] datetime,
ChartTypeName varchar(255),
FacilityID int,
[Last Name] varchar(255),
[First Name] varchar(255),
[Account Number] varchar(255),
[Date of Birth] datetime,
[Location] varchar(255),
[Chart Start Time] datetime,
[Site] varchar(255)
PRIMARY KEY (SessionID)
)

INSERT INTO #TMP_Session
SELECT
S.SessionID,
S.MRN,
S.PatientID,
S.FirstArchiveTime AS [Chart Archive Time],
S.ChartTypeName,
S.FacilityID,
S.LastName AS [Last Name],
S.FirstName AS [First Name],
dbo.EFgetFindingValue(108,'',s.sessionid) AS [Account Number],
cast(dbo.EFgetFindingValue(2072,'',s.sessionid) as datetime) AS [Date of Birth],
NULL AS [Location],
S.open_time AS [Chart Start Time],
dbo.EFGetSiteName() AS [Site]
FROM BLSession_Extended AS S
JOIN CR_ARRA_MeaningfulUse_Filtration F
ON s.sessionid = f.sessionid
WHERE
@sessionid = -99
AND dbo.EFGetSiteName() <> 'MMC' AND (@FacilityID = -99 OR s.Facilityid = @FacilityID)
AND isnull(s.ChartTypeName,'hospital') = 'hospital'
AND s.FirstArchiveTime between @StartTimeOut and @EndTimeOut

UNION ALL
SELECT
S.SessionID,
S.MRN,
S.PatientID,
S.FirstArchiveTime AS [Chart Archive Time],
S.ChartTypeName,
S.FacilityID,
S.LastName AS [Last Name],
S.FirstName AS [First Name],
dbo.EFgetFindingValue(108,'',s.sessionid) AS [Account Number],
cast(dbo.EFgetFindingValue(2072,'',s.sessionid) as datetime) AS [Date of Birth],
NULL AS [Location],
S.open_time AS [Chart Start Time],
dbo.EFGetSiteName() AS [Site]
FROM BLSession_Extended AS S
JOIN CR_ARRA_MeaningfulUse_Filtration F
ON s.sessionid = f.sessionid
WHERE
@sessionid <> -99
AND dbo.EFGetSiteName() <> 'MMC' AND s.Sessionid = @sessionid
AND isnull(s.ChartTypeName,'hospital') = 'hospital'
AND s.FirstArchiveTime is not null

UNION ALL
SELECT
S.SessionID,
S.MRN,
S.PatientID,
S.FirstArchiveTime AS [Chart Archive Time],
S.ChartTypeName,
S.FacilityID,
S.LastName AS [Last Name],
S.FirstName AS [First Name],
dbo.EFgetFindingValue(108,'',s.sessionid) AS [Account Number],
cast(dbo.EFgetFindingValue(2072,'',s.sessionid) as datetime) AS [Date of Birth],
NULL AS [Location],
S.open_time AS [Chart Start Time],
dbo.EFGetSiteName() AS [Site]
FROM BLSession_Extended AS S
JOIN CR_ARRA_MeaningfulUse_Filtration F ON s.sessionid = f.sessionid
WHERE
@sessionid = -99
AND dbo.EFGetSiteName() = 'MMC' --Maimonides-
AND (@FacilityID = -99 OR s.Facilityid = @FacilityID)
AND isnull(s.ChartTypeName,'hospital') = 'hospital'
AND s.open_time between @StartTimeOut and @EndTimeOut

UNION ALL
SELECT
S.SessionID,
S.MRN,
S.PatientID,
S.FirstArchiveTime AS [Chart Archive Time],
S.ChartTypeName,
S.FacilityID,
S.LastName AS [Last Name],
S.FirstName AS [First Name],
dbo.EFgetFindingValue(108,'',s.sessionid) AS [Account Number],
cast(dbo.EFgetFindingValue(2072,'',s.sessionid) as datetime) AS [Date of Birth],
NULL AS [Location],
S.open_time AS [Chart Start Time],
dbo.EFGetSiteName() AS [Site]
FROM BLSession_Extended AS S
JOIN CR_ARRA_MeaningfulUse_Filtration F ON s.sessionid = f.sessionid
WHERE
@sessionid <> -99
AND dbo.EFGetSiteName() = 'ABC' AND s.Sessionid = @sessionid
AND isnull(s.ChartTypeName,'hospital') = 'hospital'



--#############################################################################################
SELECT
'Problem List' AS [Category]
,10 AS [Sort]
,s.SessionID
,s.ChartTypeName
,s.FacilityID
,s.MRN
,s.[Chart Archive Time]
,s.[Last Name]
,s.[First Name]
,s.[Account Number]
,s.[Date of Birth]
,s.[Location]
,CASE
WHEN exists (select top 1 p.sessionid
from BVProblems p
where p.sessionid = s.sessionid
and p.existence in ('exists','suspected','rule out','resolved','status post')
and exists (select top 1 pp.atom_id
from Problem pp
where p.atomid = pp.atom_id
and (pp.snomed_code is not null
or
pp.atom_id = 180567 --
)
)
)
THEN 1
ELSE 0
END AS [Outcome]
,NULL AS [Lab Result]
,NULL AS [Lab Result Time]
,NULL AS [Lab Result Value]
,s.PatientID AS [int_for_counting]
,s.[Chart Start Time]
,s.[Site]
FROM #TMP_Session s
WHERE @SelectMeasure in ('All','Problem List')

--#############################################################################################
UNION ALL

SELECT Distinct
'Medication List' AS [Category]
,20 AS [Sort]
,s.SessionID
,s.ChartTypeName
,s.FacilityID
,s.MRN
,s.[Chart Archive Time]
,s.[Last Name]
,s.[First Name]
,s.[Account Number]
,s.[Date of Birth]
,s.[Location]
,CASE
WHEN exists (select top 1 d.sessionid
from BVDrugs d
where d.sessionid = s.sessionid
and d.CancelledSigTime is null
UNION ALL
select top 1 f.sessionid
from BVFindings f
where f.sessionid = s.sessionid
and f.atomid = 163829 -- and f.instance is null
and f.ValueStr = 'true'
)
THEN 1
ELSE 0
END AS [Outcome]
,NULL AS [Lab Result]
,NULL AS [Lab Result Time]
,NULL AS [Lab Result Value]
,s.PatientID AS [int_for_counting]
,s.[Chart Start Time]
,s.[Site]
FROM #TMP_Session s
WHERE @SelectMeasure in ('All','Medication List')

--#############################################################################################
UNION ALL

SELECT Distinct
'Allergy List' AS [Category]
,30 AS [Sort]
,s.SessionID
,s.ChartTypeName
,s.FacilityID
,s.MRN
,s.[Chart Archive Time]
,s.[Last Name]
,s.[First Name]
,s.[Account Number]
,s.[Date of Birth]
,s.[Location]
,CASE
WHEN exists (select top 1 p.sessionid
from BVProblems p
join ObjectsList ol on p.atomid = ol.atom_id and ol.item_type = 'Allergy'
where p.sessionid = s.sessionid
and p.existence in ('exists','suspected','rule out')
UNION ALL
select top 1 p1.sessionid
from BVProblems p1
where p1.sessionid = s.sessionid
and p1.atomid in (74675,74679,74680,74681,74682) --
and p1.existence in ('exists','suspected','rule out')
UNION ALL
select top 1 f.sessionid
from BVFindings f
where f.sessionid = s.sessionid
and f.atomid = 51914 and f.instance is null
and f.ValueStr = 'true'
UNION ALL
select top 1 f2.sessionid
from BVFindings f2
where f2.sessionid = s.sessionid
and f2.atomid = 8326 and f2.instance is null
and f2.ValueStr = 'false'
)
THEN 1
ELSE 0
END AS [Outcome]
,NULL AS [Lab Result]
,NULL AS [Lab Result Time]
,NULL AS [Lab Result Value]
,s.PatientID AS [int_for_counting]
,s.[Chart Start Time]
,s.[Site]
FROM #TMP_Session s
WHERE @SelectMeasure in ('All','Allergy List')

--#############################################################################################
UNION ALL

SELECT Distinct
'Demographics' AS [Category]
,40 AS [Sort]
,s.SessionID
,s.ChartTypeName
,s.FacilityID
,s.MRN
,s.[Chart Archive Time]
,s.[Last Name]
,s.[First Name]
,s.[Account Number]
,s.[Date of Birth]
,s.[Location]
,CASE
WHEN isnull(dbo.EFgetFindingValue(2181,'',s.sessionid),dbo.EFgetFindingValue(2181,char(127)+'32236',s.sessionid)) is not null --
and dbo.EFgetFindingValue(38037,'',s.sessionid) is not null and dbo.EFgetFindingValue(2076,'',s.sessionid) is not null --
and isnull(dbo.EFgetFindingValue(2193,'',s.sessionid),dbo.EFgetFindingValue(183465,'',s.sessionid)) is not null and dbo.EFgetFindingValue(2072,'',s.sessionid) is not null
and
1 = (case when dbo.EFgetProblemLastExistenceState(32088,s.sessionid) = 'exists' --
or
dbo.EFgetFindingValue(31249,char(127)+'6782',s.sessionid) = 'patient death'
or
dbo.EFgetFindingValue(57079,char(127)+'6782',s.sessionid) = 'morgue' --
or
dbo.EFgetFindingValue(176829,char(127)+'176828',s.sessionid) is not null then (select top 1 1
from BVFindings f
where f.sessionid = s.sessionid
and f.atomid = 176829 -- and f.instance = char(127)+'176828' -- and f.ValueStr is not null
and exists (select top 1 f1.sessionid
from BVFindings f1
where f1.sessionid = s.sessionid
and atomid not in (176829,176828) and f1.instance = char(127)+'176828'
and f1.ValueStr is not null
)
)
else 1
end
)
THEN 1
ELSE 0
END AS [Outcome]
,NULL AS [Lab Result]
,NULL AS [Lab Result Time]
,NULL AS [Lab Result Value]
,s.PatientID AS [int_for_counting]
,s.[Chart Start Time]
,s.[Site]
FROM #TMP_Session s
WHERE @SelectMeasure in ('All','Demographics')

--#############################################################################################
UNION ALL

SELECT Distinct
'Education Resources' AS [Category]
,50 AS [Sort]
,s.SessionID
,s.ChartTypeName
,s.FacilityID
,s.MRN
,s.[Chart Archive Time]
,s.[Last Name]
,s.[First Name]
,s.[Account Number]
,s.[Date of Birth]
,s.[Location]
,CASE
WHEN EXISTS (select 1
from bvfindings f
where f.sessionid = s.sessionid
and f.atomid = 172604
and f.ValueStr = 'true'
)
THEN 1
ELSE 0
END AS [Outcome]
,NULL AS [Lab Result]
,NULL AS [Lab Result Time]
,NULL AS [Lab Result Value]
,s.PatientID AS [int_for_counting]
,s.[Chart Start Time]
,s.[Site]
FROM #TMP_Session s
WHERE @SelectMeasure in ('All','Education Resources')

--#############################################################################################
UNION ALL

SELECT Distinct
'CPOE' AS [Category]
,60 AS [Sort]
,s.SessionID
,s.ChartTypeName
,s.FacilityID
,s.MRN
,s.[Chart Archive Time]
,s.[Last Name]
,s.[First Name]
,s.[Account Number]
,s.[Date of Birth]
,s.[Location]
,CASE
WHEN exists (select top 1 d2.sessionid
from BVDrugs d2
where d2.sessionid = s.sessionid
and d2.CancelledSigTime is null
and ISNULL(d2.Approverid, 0) = 0
and exists
(select top 1 1
from BLUser_Names u
where d2.OrderedSigner = u.userid
and u.auth_level_num >= 3 )
)
THEN 1
ELSE 0
END AS [Outcome]
,NULL AS [Lab Result]
,NULL AS [Lab Result Time]
,NULL AS [Lab Result Value]
,s.PatientID AS [int_for_counting]
,s.[Chart Start Time]
,s.[Site]
FROM #TMP_Session s
WHERE @SelectMeasure in ('All','CPOE')
and EXISTS (select top 1 d.sessionid
from BVDrugs d
where d.sessionid = s.sessionid
and d.CancelledSigTime is null
)

--#############################################################################################
UNION ALL

SELECT Distinct
'Vital Signs' AS [Category]
,70 AS [Sort]
,s.SessionID
,s.ChartTypeName
,s.FacilityID
,s.MRN
,s.[Chart Archive Time]
,s.[Last Name]
,s.[First Name]
,s.[Account Number]
,s.[Date of Birth]
,s.[Location]
,CASE
WHEN isnull(dbo.EFgetFindingValue(14106,'',s.sessionid),dbo.EFgetFindingValue(23974,'',s.sessionid)) is not null and isnull(dbo.EFgetFindingValue(290,'',s.sessionid),dbo.EFgetFindingValue(23975,'',s.sessionid)) is not null and dbo.EFgetFindingValue(25,'',s.sessionid) is not null -- and dbo.EFgetFindingValue(31,'',s.sessionid) is not null -- THEN 1
ELSE 0
END AS [Outcome]
,NULL AS [Lab Result]
,NULL AS [Lab Result Time]
,NULL AS [Lab Result Value]
,s.PatientID AS [int_for_counting]
,s.[Chart Start Time]
,s.[Site]
FROM #TMP_Session s
WHERE @SelectMeasure in ('All','Vital Signs')

--#############################################################################################
UNION ALL

SELECT Distinct
'Smoking Status' AS [Category]
,80 AS [Sort]
,s.SessionID
,s.ChartTypeName
,s.FacilityID
,s.MRN
,s.[Chart Archive Time]
,s.[Last Name]
,s.[First Name]
,s.[Account Number]
,s.[Date of Birth]
,s.[Location]
,CASE
WHEN dbo.EFgetFindingValue(49496,'',s.sessionid) is not null -- THEN 1
ELSE 0
END AS [Outcome]
,NULL AS [Lab Result]
,NULL AS [Lab Result Time]
,NULL AS [Lab Result Value]
,s.PatientID AS [int_for_counting]
,s.[Chart Start Time]
,s.[Site]
FROM #TMP_Session s
WHERE @SelectMeasure in ('All','Smoking Status')
and dbo.EFgetFindingValue(64896,'',s.sessionid) >= 13
--#############################################################################################
UNION ALL

SELECT Distinct
'Advance Directives' AS [Category]
,90 AS [Sort]
,s.SessionID
,s.ChartTypeName
,s.FacilityID
,s.MRN
,s.[Chart Archive Time]
,s.[Last Name]
,s.[First Name]
,s.[Account Number]
,s.[Date of Birth]
,s.[Location]
,CASE
WHEN dbo.EFgetFindingValue(30882,char(127)+'32236',s.sessionid) is not null
or
dbo.EFgetFindingValue(178708,char(127)+'32236',s.sessionid) is not null THEN 1
ELSE 0
END AS [Outcome]
,NULL AS [Lab Result]
,NULL AS [Lab Result Time]
,NULL AS [Lab Result Value]
,s.PatientID AS [int_for_counting]
,s.[Chart Start Time]
,s.[Site]
FROM #TMP_Session s
WHERE @SelectMeasure in ('All','Advance Directives')
and dbo.EFgetFindingValue(64896,'',s.sessionid) >= 65

--#############################################################################################
UNION ALL

SELECT Distinct
'Lab Results' AS [Category]
,100 AS [Sort]
,s.SessionID
,s.ChartTypeName
,s.FacilityID
,s.MRN
,s.[Chart Archive Time]
,s.[Last Name]
,s.[First Name]
,s.[Account Number]
,s.[Date of Birth]
,s.[Location]
,1 AS [Outcome]
,l.DisplayName AS [Lab Result]
,l.ResultTime AS [Lab Result Time]
,l.ResultValue AS [Lab Result Value]
,1 AS [int_for_counting] --not s.PatientID
,s.[Chart Start Time]
,s.[Site]
FROM #TMP_Session s
JOIN BLExtLabOrderResult l
ON s.sessionid = l.sessionid
WHERE @SelectMeasure in ('All','Lab Results')
and
( isnumeric(l.ResultValue) = 1
or
l.ResultValue in ('positive','negative','pos','neg','reactive','non-reactive','non reactive','nonreactive','nonreac')
)
--#############################################################################################
UNION ALL

SELECT Distinct
'Health Information' AS [Category]
,110 AS [Sort]
,s.SessionID
,s.ChartTypeName
,s.FacilityID
,s.MRN
,s.[Chart Archive Time]
,s.[Last Name]
,s.[First Name]
,s.[Account Number]
,s.[Date of Birth]
,s.[Location]
,CASE
WHEN exists (select top 1 f.sessionid
from BVFindings f
where f.sessionid = s.sessionid
and f.atomid = 181327
and f.instance is null
and ValueStr = 'true'
and dbo.CRBusinessDaysFunction (dbo.EFgetFindingValue_TimedFinding(181329,'',s.sessionid,f.ValueTime), dbo.EFgetFindingValue_TimedFinding(181330,'',s.sessionid,f.ValueTime) --
) <= 3 )

THEN 1
ELSE 0
END AS [Outcome]
,NULL AS [Lab Result]
,NULL AS [Lab Result Time]
,NULL AS [Lab Result Value]
,s.PatientID AS [int_for_counting]
,s.[Chart Start Time]
,s.[Site]
FROM #TMP_Session s
WHERE @SelectMeasure in ('All','Health Information')
and EXISTS (select 1
from bvfindings f
where f.sessionid = s.sessionid
and f.atomid = 181326
and f.instance is null
and f.ValueStr = 'true'
union all
select 1
from bvactions a
where a.sessionid = s.sessionid
and a.atomid = 196715 –
and a.CancelledSigTime is null
)

--#############################################################################################
UNION ALL

SELECT Distinct
'Discharge Instructions' AS [Category]
,120 AS [Sort]
,s.SessionID
,s.ChartTypeName
,s.FacilityID
,s.MRN
,s.[Chart Archive Time]
,s.[Last Name]
,s.[First Name]
,s.[Account Number]
,s.[Date of Birth]
,s.[Location]
,CASE
WHEN EXISTS (select 1
from bvfindings f
where f.sessionid = s.sessionid
and f.atomid = 181327 and f.instance is not null
and f.ValueStr = 'true'
)
THEN 1
ELSE 0
END AS [Outcome]
,NULL AS [Lab Result]
,NULL AS [Lab Result Time]
,NULL AS [Lab Result Value]
,s.PatientID AS [int_for_counting]
,s.[Chart Start Time]
,s.[Site]
FROM #TMP_Session s
WHERE @SelectMeasure in ('All','Discharge Instructions')
and EXISTS (select 1
from bvfindings f
where f.sessionid = s.sessionid
and f.atomid in (181326,181327) and f.instance is not null
and f.ValueStr = 'true'
union all
select 1
from bvactions a
where a.sessionid = s.sessionid
and a.atomid = 196714 -- and a.CancelledSigTime is null
)

--#############################################################################################
UNION ALL

SELECT Distinct
'Medication Reconciliation' AS [Category]
,130 AS [Sort]
,s.SessionID
,s.ChartTypeName
,s.FacilityID
,s.MRN
,s.[Chart Archive Time]
,s.[Last Name]
,s.[First Name]
,s.[Account Number]
,s.[Date of Birth]
,s.[Location]
,CASE
WHEN dbo.EFgetFindingValueByValueSTR(163829,'',s.sessionid,'true') is not null and
(s.[Site] <> 'MMC' or
dbo.EFgetFindingValueByValueSTR(163821,'',s.sessionid,'admission') is not null )
THEN 1
ELSE 0
END AS [Outcome]
,NULL AS [Lab Result]
,NULL AS [Lab Result Time]
,NULL AS [Lab Result Value]
,s.SessionID AS [int_for_counting]
,s.[Chart Start Time]
,s.[Site]
FROM #TMP_Session s
WHERE @SelectMeasure in ('All','Medication Reconciliation')

--#############################################################################################
UNION ALL

SELECT Distinct
'Summary of Care Record' AS [Category]
,140 AS [Sort]
,s.SessionID
,s.ChartTypeName
,s.FacilityID
,s.MRN
,s.[Chart Archive Time]
,s.[Last Name]
,s.[First Name]
,s.[Account Number]
,s.[Date of Birth]
,s.[Location]
,CASE
WHEN dbo.EFgetFindingValueByValueSTR(127781,char(127)+'49421',s.sessionid,'true') is not null
THEN 1
ELSE 0
END AS [Outcome]
,NULL AS [Lab Result]
,NULL AS [Lab Result Time]
,NULL AS [Lab Result Value]
,s.SessionID AS [int_for_counting]
,s.[Chart Start Time]
,s.[Site]
FROM #TMP_Session s
WHERE @SelectMeasure in ('All','Summary of Care Record')
and EXISTS (select top 1 1 from BVfindings
where sessionid = s.sessionid
and objectname in ('Transfer_Tther_Facility', 'Transfer_To_Perinatal_Center', 'Transferred_Another_Facility_Boolean!Transfer_Data', 'Transfer_To_Perinatal_Center!Transfer_Data', --
'Patient_Disposition!Discharge', 'Patient_Disposition!Discharge_Report' --
)
and ValueStr in ('true',
'transport to other facility','transport_to_other_facility', 'transfer to other facility', 'intermediate care facility', 'skilled nursing facility',
'rehabilitation facility',
'HMO holding facility', 'perinatal regional center' )
UNION ALL
Select top 1 1 from BVfindings
where sessionid = s.sessionid
and s.[Site] <> 'Banner' and objectname in ('User_Defined_Transfer', --
'Transfer_T', 'Transfer_To_Facility',
'Transfer_To_Facility!Discharge', 'Transfer_To_Facility_Enum!Discharge' )
and ValueStr is not null
UNION ALL
Select top 1 1 from BVfindings
where sessionid = s.sessionid
and s.[Site] not in ('Banner','MMC') and objectname in ('Transfer_to_ICU', 'Transfer_To_CCU' --action
)
and ValueStr is not null
)

END



--END

SET NOCOUNT OFF

SET ANSI_NULLS OFF



GO

 

[hilfy]

Please provide a sample of the parameters that you use every month. Is this report run interactively by users in addition to being scheduled?

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[hilfy]

Oh, and are you using any subreports?

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[swenri1]

Thank you for the reply. The parameters that I use are

Select Measure = All
Show Patient Data = No
Start Time = 01-01-2013
End Time = 02-01-2013
Facility ID = -99 (All locations No filtration)

No , I'm not using any sub reports .....

Thank you and I need your help urgently.

 

[hilfy]

I'm glad you're not using subreports - that will make this MUCH easier!

1. Create what will be a "Main" report. Use a command (SQL Select statement) that returns a single record. I usually use something like "Select GetDate()" for something like this (assuming you're on SQL Server).

2. Create the same parameters in this report as you have in the stored proc - make them all optional. Set the default values for each of the parameters except the dates.

3. Create a formula for each of the date parameters. They will look something like this:

if HasValue(?StartTime} then {?StartTime} else Date(Year(CurrentDate), Month(CurrentDate), 1)

if HasValue(?EndTime} then {?EndTime} else DateAdd('m', 1, Date(Year(CurrentDate), Month(CurrentDate), 1))

4. Add your existing report to this new report as a subreport.

5. Right-click on the subreport and select "Change Subreport Links".

6. For the non-date parameters, add the parameters to the list on the top right. For the date parameters, add the formulas you just created.

7. Select each of the params/formulas in the top right. Uncheck "Select data in subreport based on field:" in the bottom right. In the bottom left, select the corresponding parameter from the subreport. This will provide values from the main report to the parameters in the subreport based on either the main report parameter or the formula that sets the default dates.

You may decide to do some reformatting as there are some limitations in subreports. There are no page header or footer sections. Headers are easy to simulate - create a group on a field that occurs only once in the data and turn on "Repeat Header on Every Page". There's no way that I know of to get page footers in a subreport, though.

-Dell


DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[PeterDimitrov]

There are cheap 3rd party schedulers, which are able to handle this task with just few mouse clicks. They cannot replace Crystal server , but will save you a lot of time in cases like this one.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[IdoMillet]

For a list of such 3rd-party schedulers, see

http://kenhamady.com/bookmarks.html

- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com