CR Stored Procedure Conditional Date

[codrutza]

Hi

Maybe you can help me with this. Maybe it isn't the right Forum, but I have a report built in CR, which works fine, but I have to base it on a stored procedure. I wrote in Microsoft SQL Server my question, as well.

SQL Server Management Studio Express

I need to write a stored procedure, which has the same result as in Crystal Reports XI.

In Crystal Reports is like that:

@record selection
…..
and ({@Year}={?Year} or {@year}=({?Year}-1))
and date({@dep arr})<date(year(currentdate),month(currentdate),1)

where the formulas are:
@dep arr
if {M.MType} ="Import" then {M.ArrivalDate}
else if {M.MType} ="Export" then {M.DepartureDate}

@Year
if {M.MType} ="Import" then {%yeararr}
else if {M.MType} ="Export" then {%yeardep}

%yeararr
year("M"."ArrivalDate")

%yeardep
year("M"."DepartureDate")

 

[Madawc]

Do SQL code without dummy values in place of parameters. (Note that Database > Show SQL Query in Crystal can have the data.

Within DataBase Expert, choose the database. Choose 'Add Command'. Open the worksheet. Paste the code from 'Query'.

Then add parameters and reference them in the code. You'll find also that they have been placed like normal parameters within Crystal. But parameters set up in Crystal cannot be used by the SQL code.

Note also that you could use SQL to get unselected data and then select for it in the Crystal. Inefficient but would work.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[codrutza]

Thanks for your reply. I'll go for the second option. I will write a SP that retrieves all data and I will add the parameter {?Year} in Crystal Reports.