'Optional' Filter

[c8guru]

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

[workdan]

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.

[sir_jeroen]

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'