COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Topic started by: c8guru on 02 Jul 2007 06:26:56 PM

Title: 'Optional' Filter
Post by: c8guru on 02 Jul 2007 06:26:56 PM
We have a fact table with account balances that are restated each day.  For the majority of users, they will want to select an effective date in order to limit the results for a specific day.  However, we don't want two separate fact tables, one with a mandatory prompt filter and another without.  Ideally, we would like a prompt to show when the user runs a report that includes a column from the fact table but only applies the filter if they select a value.  I could put a description of 'Please select an effective date or select 'Cancel' for all dates'.  Is there a method to create a prompt filter that only filters when the user chooses something but everything when they don't select something?

Steve
Title: Re: 'Optional' Filter
Post by: workdan on 04 Jul 2007 10:19:03 AM
I believe you simply need to set the filter's Usage property to Optional when you set it up - if using the Prompt Wizard in Report Studio, there's a checkbox you can select to "Make this an optional prompt."

Hope this helps.
Title: Re: 'Optional' Filter
Post by: sir_jeroen on 04 Jul 2007 06:59:53 PM
you could also try:
#prompt('parameter1','token','1=1')# as a filter.

1=1 means no filter and parameter1 can get values like '[date] = sysdate'