Can I use an IF statement to apply these filters via the prompt page?

[emd023]

I built a report that has several optional parameter prompts such as employee search, expense type search, date range, etc. but id like to be able to choose whether or not to include or exclude itemizations. These filters are included in the data warehouse in the Common Report Entry Filters folder.

I currently have two copies of my report (one excluding itemizations and the other including them) but to make it easier id just like to a have a single report and choose which one of these filters i would like to apply from the prompt page. Is it possible to set up some kind of "Yes/No" parameter prompt asking something like "Would you like to include itemizations and exclude parent entries? Y/N?"  If yes is selected it will apply the "Exclude Parent Entries" filter and if no is selected it will apply the "Exclude Itemizations" filter and if nothing is selected it will default to "Exclude Parent Entries."

Would I need to use some kind of IF statement saying something like:
IF param = yes then apply X filter
IF param = no then apply Y filter

Am I making this too complicated? Is there an easier way to do this? Thanks!

[Nagendar]

I built a report that has several optional parameter prompts such as employee search, expense type search, date range, etc. but id like to be able to choose whether or not to include or exclude itemizations. These filters are included in the data warehouse in the Common Report Entry Filters folder.

I currently have two copies of my report (one excluding itemizations and the other including them) but to make it easier id just like to a have a single report and choose which one of these filters i would like to apply from the prompt page. Is it possible to set up some kind of "Yes/No" parameter prompt asking something like "Would you like to include itemizations and exclude parent entries? Y/N?"  If yes is selected it will apply the "Exclude Parent Entries" filter and if no is selected it will apply the "Exclude Itemizations" filter and if nothing is selected it will default to "Exclude Parent Entries."


Would I need to use some kind of IF statement saying something like:
IF param = yes then apply X filter
IF param = no then apply Y filter

Am I making this too complicated? Is there an easier way to do this? Thanks!

Is this link meets your requirement.. http://www.cognoise.com/index.php/topic,29757.msg96523.html#msg96523

Thanks
Nagendar

[BigChris]

Filter statements don't really like if...then...else conditions.

You'd be better creating your filter to look something more like:

(?Param?= 'Yes' and X Filter) or (?param? = 'No' and Y Filter)

[Penny]

Hi there
I had a similar requirement and did the following:

1. Created a prompt with static choices where user selects what type of filter they wanted.  Store response in paramPromptFilter
2. For each static choice I created a Boolean variable that was set based on the static choice selected.  Only one can be true.  In each case the report expression for the variable was ParamValue('paramPromptFilter') = 'staticvalueselected'.  Now there is a Boolean variable for each filter type.
3. Created a separate prompt page for each possible filter type listed in the static choices to get the actual filter values from the user.   For each of the these prompt pages I set the render variable property to the corresponding Boolean variable.  Only the prompt page corresponding to the filter choice is displayed. 
4. Created several detail filters on the main query for the report, each with usage property set to Optional (one filter for each staticchoice).  The detail filter itself looks something like ?paramPromptFilter? = 'staticvalueselected' and queryitem = ?promptedfiltervalue?

The icon for an optional filter is the funnel with empty brackets beside it.  It is different than a regular filter.  What happens is that ONLY if the first part of the condition is true in the filter (the paramPromptFilter=) will the second half of the expression be evaluated. 

I am not sure if this will help you.  I hope I explained it properly and didn't miss anything.  I am working with a relational data source and built this report in Cognos 10.2.1