Crystal Reports IF statement in Command window

[#1]

Hello.

I'm trying to create a report that depends on what a user enters, a different SELECT statement is to run. For example, if a user enters a calendar year value, then a SELECT statement for the calendar year runs. If a user enters a fiscal year value, then a SELECT statement for the fiscal year runs. I didn't even know that I could do this from the Command windows to begin with. My code looks like:

IF ( ({?BeginCY}) = ' ' )
BEGIN
select fiscal_year,station_response_area_category, count(*) as Incident_count
from dw_prod.dbo.vw_unit_response
where fiscal_year between {?BeginFY} and {?EndFY}
group by fiscal_year,station_response_area_category
END
ELSE 
BEGIN
select year,station_response_area_category, count(*) as Incident_count
from dw_prod.dbo.vw_unit_response
where year between {?BeginCY} and {?EndCY}
group by year, fiscal_year,station_response_area_category
END

Of course the code isn't working right now. I'm trying to figure out how to do the IF section... Please help.

[abhilash_kumar · Accepted solution]

OK. I see why it's doing that.

Add a common 'column' alias for Fiscal_Year and year columns.

IF '{?YearType}' = 'Fiscal'
BEGIN 
SELECT fiscal_year Year, 
station_response_area_category, 
count(*) as Incident_count 
FROM dw_prod.dbo.vw_unit_response 
WHERE fiscal_year between '{?BeginYear}' and '{?EndYear}'
GROUP BY fiscal_year,station_response_area_category 
END 
IF '{?YearType}' = 'Calendar' 
BEGIN 
SELECT year Year, 
station_response_area_category, 
count(*) as Incident_count 
FROM dw_prod.dbo.vw_unit_response 
WHERE year between '{?BeginYear}' and '{?EndYear}'
GROUP BY year,station_response_area_category 
END

-Abhilash

[former_member548403 · Accepted solution]

Thank you! Saved my butt again!