Rpt Filtering

[ridhirao22]

Using CR 2008 and Oracle 10.

I am using a command object( Sql query) for the rpt and checking for a single state in the where clause for a give date range. Now I need to be able to run the report for All states or a state. I removed the hardcode state and in the report created a parameter with default as 'All'. Report has slowed down a lot after this. Can we use this logic in command object?
(if ?state ='All' then true else
tbl.state = ?state)

Report has a on demand subreport with ?dates and ?state linking to main report.
Please advice, how I could improve rpt performance.

Thanks,
RR

 

[lbass]

Create the parameter in the command screen on the right, and then use syntax in the 'where' clause like this:

(
(
'{?state}' <> 'All' and
tbl.state = '{?state}'
) or
'{?state}' = 'All'
)

Add the appropriate punctuation for tbl.state. You can then go into the field explorer->parameter and edit the parameter to add a picklist if you wish in the main report.

-LB

 

[ridhirao22]

LB, Thank you for the reply. The main report works fine with the above advice, but the On demand subreport linked thru the parameter from main report doesn't return (just shows accesing database)
please help!

Thanks!

 

[ridhirao22]

Linking main report to subreport thru the parameter in the main report and in the subreport -> Record selection
I change it to as follow:
({Command.QUARTER} <= {?Pm-?Start Quarter}
and
{Command.QUARTER} >= {?Pm-?End Quarter })
and
(If {?Pm-?Company} = 'All' then true else {Command.COMPANY} = {?Pm-?Company})
and
(if {?Pm-?State} = 'All' then true else
{Command.STATE} = {?Pm-?State})

Subreport report uses almost same sql from command object as main report( except for the prompt condition in the where clause) which is checking in the record selection.

How can I improve the subreport performance?

TIA,

 

[lbass]

You could try creating the same parameters within the subreport, using them in the subreport record selection formula, and then linking the parameters to each other by using the dropdown in the lower left to select, for example, {?State} instead of the default {?pm-?State}. Not sure this will help dramatically, but it might.

-LB

 

[ridhirao22]

Thank you, LB. It really helped