Getting formula output to be included in report SQL statement

[SteveRemington]

A series of reports that I am writing all include a formula ({@Report Period}) whose output is a 6 digit integer that represents the financial period (eg 200203 for the 3rd month of the financial year 2002). The input to this formula is a single user entered parameter (string). The code of the formula is:

Select {?Financial Period}
Case "Auto": // Run report for previous period
If Month(CurrentDate) <= 7 then
((Year(CurrentDate)) * 100) + Month(CurrentDate) + 5
Else
((Year(CurrentDate)+1)) * 100 + Month(CurrentDate) - 7
Default: // User enters desired period
If (IsNumeric({?Financial Period})
and Len({?Financial Period}) = 6
and CDbl(Right({?Financial Period}, 2)) <= 12
and CDbl(Left({?Financial Period}, 4)) >= 1950) then
CDbl({?Financial Period})
Else
0; // Invalid period entered

The default value for {?Fianaical Period} is &quot;Auto&quot; and the user can enter their own number if they choose.

The output of this formula is then used in the record select statement:

...and
{BILL.BILLPERIOD} = {@Report Period}
and...

This line does not get included into the SQL statement which results in many millions of records being returned only to have a few hundred thousand records can actually be processed.

I would like to know if there is some way that I get the output of this formula included in the SQL statement so that my report would run much faster.

I think SQL Expressions may be the answer but I'm not sure how to include the value of user parameters in them.

I would appreciate any suggestions on how to solve my problem.

Thanks in advance to all who reply.

 

[AndrewBaines]

What's the database you're using? Some databases support a form of the CASE statement so you could push it all across.

SQL Expressions are unlikely to be a solution as you're using conditionals.

Could you create a parameter pick list that displayed what the user expect to see but returns something useful to the where clause?

Otherwise, you're stuck as the Crystal formula is only evaluated after the data is read in.