I have a report that is using filters included in the universe. These filters include currency, manager initials etc. These are all added by clicking the small filter tab in the bottom right hand corner.
I now want to add manager dept which is a data element on the report. When I add this one additional filter, the report does not calculate correctly. It seems to include All departments.
What I need is a new filter using an existing data element on the report that will allow me to select one dept, multiple depts or “All” for all depts.
I can incl the sql code if necessary. I think maybe my initial post added too much info, making it confusing.
How would I do this:
Add a filter from an existing data element on the report that will allow me to select one value, multiple values or a wildcard for all values.
Assume we are filtering on states:
If I select NH, only NH data will appear on the report.
If I select NH,ME only NH and ME data will show on the report.
If I select All, or % or * (whatever the wildcard is), all states will show on the report.
I have been able to create a filter in a different reporting tool that will allow me to add a filter where… field LIKE prompt. By using this %=ALL
You should do a search for “optional prompt”. This has been discussed thousands of times.
This is a sample of the SQL that can be used in a pre-defined condition
to accomplish “1, many or all” functionality. You don’t need to do this
if it’s 1 or all. You can simply use the “matches pattern” operator.
Unfortunately, matches pattern doesn’t work if the user wants to enter
several items.
(PER_JOBS.ATTRIBUTE3 IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for
all)’,‘A’,‘Job Information\Add. Job Details Survey Code’,multi,free) OR
‘ALL’ IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for all)’,‘A’,‘Job
Information\Add. Job Details Survey Code’,multi,free) OR
‘All’ IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for all)’,‘A’,‘Job
Information\Add. Job Details Survey Code’,multi,free) OR
‘all’ IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for all)’,‘A’,‘Job
Information\Add. Job Details Survey Code’,multi,free) OR
‘*’ IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for all)’,‘A’,‘Job
Information\Add. Job Details Survey Code’,multi,free) OR
‘%’ IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for all)’,‘A’,‘Job
Information\Add. Job Details Survey Code’,multi,free))
(BOB member since 2002-06-25)