Skip to Content
0
Oct 14, 2019 at 12:44 PM

Crystal Reports: ways to enhance the performance?

62 Views

Hello.

I have a report that I utilize the "IF THEN" function in the command section and I think it is causing the performance to lag. For example, below is like 1/5 of the entire SQL in the command section:

if ('{?pgmid}' like'1' + '%') and '{?geotype}' = 'station_response_area'
begin
select incident_num, 
unit, 
incident_date, 
station_response_area geotype, 
measure_time,
CONVERT(CHAR(8),DATEADD(second,measure_time,0),108) as Response_Time,
pgmid, 
NFPAStandard,
responsecategory,
meetstandardcnt,
cast(measure_time as varchar) as time_sec,
pgmdorder,
incident_type,
property_use
from vw_nfpa_firstArv_RPT
where incident_date between '{?BeginDate}' and '{?EndDate}'
and pgmid = {?PgmID}
order by measure_time asc
end

if (('{?pgmid}' like '2' + '%')  or ('{?pgmid}' like '3' + '%')) and '{?geotype}' = 'station_response_area'
begin
select incident_num, 
unit, 
incident_date, 
station_response_area geotype, 
measure_time,
CONVERT(CHAR(8),DATEADD(second,measure_time,0),108) as Response_Time,
pgmid, 
NFPAStandard,
responsecategory,
meetstandardcnt,
cast(measure_time as varchar) as time_sec,
pgmdorder,
incident_type,
property_use
from [DW_PROD].[dbo].[vw_nfpa_ERF_RPT]
where incident_date between '{?BeginDate}' and '{?EndDate}'
and pgmid = {?PgmID}
order by measure_time asc
end

Would there be a way to substitute the "IF THEN" with something else to enhance the performance of the report?