Conditional Crystal Reports Command

[Former Member]

Hello experts,

I have a report that has two date parameters that I need to massage depending on the user's input.

{?FiscalYear} and {?FiscalMonth} are parameters type Number that users can change it to any valid year i.e.: 2017 and 04, so these values pass to a sub-report to bring in the data for that year and fiscal month.

The intention here is, making the existing report flexible to schedule and run any time with the current dates.

By setting the Parameter to 9999 as Default, we want to test it and enable it to change automatically to Current Year if there was no change made.

When I tried this in the Record Selection, it wont work because it was already processed and the if/else statements ignored.

So I am trying to do it (load the current date in the date parameters before is being processed by the report) in the CRs Command window, something like:

If {?FiscalYear} = 9999 then

{?FiscalYear} := Year(CurrentDate);

{?FiscalMonth} := Month(CurrentDate) ;

DBYearField := {?FiscalYear};

DBMonthField := {?FiscalMonth};

Else...

(the regular selection with the entered year and month values)

So my questions are:

a) does the CRs Command window allow If/Else statements? If so, what is the correct

syntax? or,

b) How can this achieved by using a SQL (Oracle) statement like:

Select * from v.View where...etc.?

Thank you,

erollano

[Former Member]

Vitaly,

This solution worked just fine in the reports I used it. I am still going thru others that have similar set up and requirement and hopefully will work on those as well.

Thank you!

[vitaly_izmaylov]

Have you tried the solution I suggested in my previous post?

Create two formulas in the main report:

1. @Month

If {?FiscalYear} = 9999 then Month(CurrentDate) else {?FiscalMonth}

2. @Year

If {?FiscalYear} = 9999 then Year(CurrentDate) else {?FiscalYear}

Place them in the main Report Header

Then link the formulas to the relevant subreport paramteres.

[Former Member]

indeed, the main report is only a container and it links to the sub report by the {?FiscalYear} and {?FiscalMonth}. That is why I need to populate the {?FiscalYear} and {?FiscalMonth} first to then, pass it to the sub report.

When I tried this on the Record Selection formula, there was no error message but both values remained unchanged, meaning the parameters were passed to the sub report already by the time it reached the logic to change them. This way I am trying to make the test and change in the Command window but I get the following MS SQL error in the statement: If {?FiscalYear} = 9999 then {?FiscalYear} = year(currentDate): "Incorrect syntax near the word 'then'. SQL State 42000"

This gives me pause that I can use If/Else statements but I cannot figure what it is complaining about.

[vitaly_izmaylov]

If the main report used as a container only and the actual data data is coming to subreports only then you can create a formula in the main report like:

If {?FiscalYear} = 9999 then Year(CurrentDate) else {?FiscalYear}

and then use it to link the Subreport {?FiscalYear} parameter

another formula:

If {?FiscalYear} = 9999 then Month(CurrentDate) else {?FiscalMonth}

use to link the subreport {?FiscalMonth} parameter;

But if the main report also has fitered data, then you need to create a logic withim Command Object. Support for If/else

statements in Command Object depends on the Driver used to connect. If you find a logic that works outside the Crystal Reports , then it should work inside the command object.