swenri
Yak Posting Veteran
72 Posts |
Posted - 2013-08-13 : 10:29:18
|
Hi All:How to schedule a report automatically without changing the parameters every month the server?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 the help....Below is the code that I wrote USE [DatamartDB2]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[SP_ARRA_MeaningfulUse_Monthly](@TimeFrame2 varchar(3)) ASSET NOCOUNT ONSET 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 datetimeDeclare @ToDate datetime--Declare @TimeFrame varchar(2)if @TimeFrame2='3' -- MonthlyBeginselect @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))))EndDECLARE @StartTimeOut datetimeDECLARE @EndTimeOut datetimeDECLARE @TimeFrameOut varchar(50)EXEC [dbo].[SPGetReportTimeFrame] @StartTime, @EndTime, @TimeFrame, @StartTimeOut output, @EndTimeOut output, @TimeFrameOut outputSELECT @StartTimeOut = @FromDate SELECT @EndTimeOut = @ToDate--SELECT @FromDate --SELECT @ToDate--SELECT @TimeFrameOutIF (@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]) ENDENDELSEBEGIN 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 sWHERE @SelectMeasure in ('All','Problem List')--############################################################################################# UNION ALLSELECT 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 sWHERE @SelectMeasure in ('All','Medication List')--############################################################################################# UNION ALLSELECT 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 sWHERE @SelectMeasure in ('All','Allergy List')--############################################################################################# UNION ALLSELECT 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 sWHERE @SelectMeasure in ('All','Demographics')--############################################################################################# UNION ALLSELECT 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 sWHERE @SelectMeasure in ('All','Education Resources')--############################################################################################# UNION ALLSELECT 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 sWHERE @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 ALLSELECT 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 sWHERE @SelectMeasure in ('All','Vital Signs')--############################################################################################# UNION ALLSELECT 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 sWHERE @SelectMeasure in ('All','Smoking Status')and dbo.EFgetFindingValue(64896,'',s.sessionid) >= 13 --############################################################################################# UNION ALLSELECT 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 sWHERE @SelectMeasure in ('All','Advance Directives')and dbo.EFgetFindingValue(64896,'',s.sessionid) >= 65 --############################################################################################# UNION ALLSELECT 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 sJOIN BLExtLabOrderResult l ON s.sessionid = l.sessionidWHERE @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 ALLSELECT 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 sWHERE @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 ALLSELECT 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 sWHERE @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 ALLSELECT 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 sWHERE @SelectMeasure in ('All','Medication Reconciliation')--############################################################################################# UNION ALLSELECT 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 sWHERE @SelectMeasure in ('All','Summary of Care Record')and EXISTS (select top 1 1 from BVfindings where sessionid = s.sessionid and objectname in ('Transfer_To_Other_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 --ENDSET NOCOUNT OFFSET ANSI_NULLS OFF GO |
|