Crystal Reports IF statement in Command window

[former_member548403]

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!

[abhilash_kumar]

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

[former_member548403]

Thanks, Abhilash, as usual!

I followed your instruction, and I'm getting an error right now. When I run the report, I get an error and it says, syntax near 'Year'. I presume the error is due to a parameter and am trying to debug this thing.

In the meantime, any idea about the error?

[abhilash_kumar]

Hi Kenshin,

Try running the query in the database. Replace the parameter fields with static values:

DECLARE @YearType varchar 
SET @YearType = 'Fiscal Year'

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 2016 and 2017 
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 2016 and 2017 
GROUP BY year,station_response_area_category 
END

[former_member548403]

It seems to be fine in the database. I get, "Command(s) completed successfully."

[abhilash_kumar]

Do you see any results?

Could you check if the datatype of the parameters and the fields match?

For e.g. datatype of the parameters {?BeginYear} and {?EndYear} should be same.

You may also try enclosing the parameters in quotes like so:

WHERE year between '{?BeginYear}' and '{?EndYear}'

[former_member548403]

No, sir. It did not give me any result other than the message.

The datatype of the parameters is 'varchar' in the database. So I think the datatype is correct here.

I also tried enclosing the parameters in quotes, but the same syntax error...

[former_member548403]

OK. I tried this and this doesn't give me any error, BUT it doesn't ask to select a year type when I try to refresh...

DECLARE @YearType varchar
--SET @YearType = {?YearType}

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

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

[abhilash_kumar]

Do you still have the YearType prompt in the command window?

Have you trued without the quotes?

-Abhilash

[former_member548403]

Yes, and now it seems to be running all right. A HUGE THANKS TO YOU!!

My next question is, how do I show the result - fiscal year data to the calendar year data, upon user selection? I guess I could modify the code to show both calendar and fiscal data and create a formula to show the one that user select?

[abhilash_kumar]

Expand the Field Explorer > Look for the 'YearType' selection prompt.

Drag and drop this prompt on the Report Header and it should display what is selected.

-Abhilash

[former_member548403]

It's like one mountain after another...The reports does ask me to select the year type, but it's not asking for the begin and end year, even though the parameters are in the code. What could it be now?...

IF '{?YearType}' = 'Fiscal'
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' 
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

[abhilash_kumar]

Are the BeginYear and EndYear in the command prompt?

Do you see them to the right in the prompts panel?

-Abhilash

[former_member548403]

They are in the command prompt, but weirdly I wasn't prompted for them until I added them to the report design section....

About the data selection between fiscal and calendar, I did try your suggestion of dragging the 'Year Type" to the report header, but if I refresh with different year type, the report cannot process and throws an error:

One of more fields could not be found in the result set. Use verify database to update the report.

[abhilash_kumar]

OK and what happens after you 'verify database'?

-Abhilash

[former_member548403]

Then it brings the data, but I have to do this each time when I switch from fiscal to calendar and so on.