How to "Push down" record selection to the database server

[nikhil27]

Hi,

In my report I am using 'station code' as a parameter. These parameter values are retrieved from the database and then they are passed to the report.These values are stored in the database like this:

parameter parameter_value
station_code "LAX|PHX|SEA|NY"

( In this case the report will include the data only for these 4 stations)

And if a user wants to have the data for all the stations, the parameter value will be like this:

parameter parameter_value
station_code "ALL"

The report uses the following formula for record selection.

//@select_code
StringVar array x := Split({?station_code},"|");
NumberVar y := Ubound(x);
if {?station_code} = "ALL" then
1 = 1
else
if y > 1 then
{station_codes.station_code} in x
else
{station_codes.station_code} = x

And then this formula is used in my 'Record selection formula' along with other condition. For example my record selection formula can be like this:

@select_code and table.product_code in ('A','B')

My question is:

Is there any way I can push @select_code to the database server because my table has 10 million records and out of them only 100k are required in this report (If somehow I can push this formula to the database server)

Any pointers regarding this will be of great help.

Thanks,
Nikhil

 

[vidru]

If you switched to a multiple value parameter instead of a single parameter that has the values separated by "|", you'd have a much better chance at getting this to pass. Your record selection formula would be as simple as...

("ALL" in {?station_code}) Or ({station_codes.station_code} in {?station_code})

... and should pass to the server.

-dave

 

[nikhil27]

Thanks for the reply dave !!

But even this doesn't seem to work..Still In the 'Show Sql Query' I don't see this condition.

 

[nikhil27]

I am using the following 'Record Selection' formula

("ALL" in [{?station_code}] OR {system_bills.station_code} in [{?station_code}]) and
{customer_accounts.trade_indicator_code} <> "IC"

The station_code value is "ROC"

But the 'Show Sql Query' shows the follwoing conditions in 'where' clause.

{customer_accounts.trade_indicator_code} <> "DHL"

 

[vidru]

Ok, a little fine tuning then...

( If not ("ALL" in [{?station_code}]) then
{system_bills.station_code} in {?station_code}
Else If "ALL" in [{?station_code}] then
True)
and
{customer_accounts.trade_indicator_code} <> "IC"

-dave

 

[nikhil27]

Thanks dave, but even this doesn't seem to work..

If I use the

{system_bills.station_code} = {?station_code}

it works fine, but If I use "in" instead of "=" it fails

{system_bills.station_code} in {?station_code}

Any Idea why this is happening.

Can't we use ("in") in the record selection formula.

 

[vidru]

When you say it fails, do you mean it's generating an error, or not returning the expected data?

You can use 'In' only if the parameter is set up to 'Allow multiple values'. If you do indeed have it set up correctly, make sure you're entering each value separately, then clicking the 'Add' button. If you're still entering the parameters as 'Val1|Val2|Val3' it won't work correctly.

-dave

 

[nikhil27]

Thanks for all your help Dave!!

It is working fine when I export the report from the Crystal Report itself.

But in my case I am generating this report using the 'exporting process'. Thus we pass the parameter to the report from the ASP pages. I have changed this parameter for the 'multiple values' but in that case do we have to make any changes in the ASP code.

For us since this 'station_code' is a single parameter, we set the value of this parameter from the ASP page using the following code.


Param1.SetCurrentValue(Cstr(trim(parametervalue)),12)

where parametervalue is LAX|PHX|ROC|SEA|JFK

I am not sure how we should pass the values now so that it works fine.

Again thanks for all your inputs.

 

[nikhil27]

It looks like I have found a way to achieve this.

Thanks Dave!!