Skip to Content

Crystal Reports IF statement in Command window

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jan 16 at 05:58 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 12 at 08:07 AM

    Hi Kenshin,

    Here's what I would do:

    1. Create a parameter in the Command Window called "YearSelection". It would have two static values in its list:

    • Calendar Year
    • Fiscal Year

    2. Create two parameters to accept the Start and End years. You can name them "BeginYear" and "EndYear"

    3. Modify the SQL to use this code:

    DECLARE @YearType varchar 
    SET @YearType = {?YearSelection} 
    
    IF @YearType = 'Fiscal Year' 
    BEGIN 
    SELECT fiscal_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 Year' 
    BEGIN 
    SELECT 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

    Make sure to add the list of values to the "YearSelection" prompt by editing the parameter from the "Field Explorer".

    When this report is run, the user will be prompted for three prompts:

    • Year Type Selection
    • Start Year
    • End Year

    When the user selects Year Type = 'Calendar Year', the report returns data for the Calendar year based on the date range in the Start and End prompt.

    When the user selects Year Type = 'Fiscal Year', the report returns data for the Fiscal year based on the date range in the Start and End prompt.

    I hope this helps.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded