|
Return to Newsletter Contents...
Customizing an LOV to include "All"
by: Joe Roberts, Principal Consultant, BECP, SLE, MCP
When someone asks me “can I create an
‘All’ option in a List of Values to use in my reports?” what they are really
asking is “how can I avoid making the user select all of the values in a prompt
if they don’t really want filter to a specific value in that prompt. Take for
instance a report that prompts the user for a state but the user wants to see
data for all states; he must select every state in the list and move them to the
“selected items” list. For example the
prompt below asks the user to select a state, this user has selected not to
filter by selecting all states (confusing…?
you bet!).
A more intuitive way to present that
to the user would be by giving them an option to select “All States” or select
an individual option as the example below shows.
Now isn’t that better? So how do we do
that? I’m glad you asked.
1.)
First we must create a new dimension object in the universe.
We’ll call that object State_All. The purpose of this object is to return the word “All”
for each State Name. This object
should be hidden in the universe since it will not ever be directly accessed as
a dimension by end users. The select portion of this object will have to have a
case statement to accomplish our intended results. That select statement should
look like this:
CASE @SELECT(Customer\State)
WHEN @SELECT(Customer\State) THEN
‘** All States’
ELSE
@SELECT(Customer\State)
END
Note: the string constant returned
says ** All States instead of just All States to force it to the top of the list
in this particular instance.
2.)
Next we are going to modify the List of Values for the All_States object and add
a second or combined query. We want this object to bring back the word ‘** All
States’ but we also want it to actually bring back a list of states. To do that
we must edit the List of Values and click on the Combined Queries button. This
will add a second query joined to the first query as a union.
In the second query replace the
State_All object with the original State object. The first query should look
like this:
The second query should look like
this:
Now view the SQL syntax and it should
look something like this.
Click ‘OK’ twice to return to the
properties tab of the State_All object. Before saving the object, make sure you
check mark the “export with universe” check box or it won’t export your modified
SQL.
3.)
Now all we have left to do is create a prompting filter object that uses our
modified List of Values. For that I have created a new filter object called
prompt for state. The filter object will use the @Prompt function and reference
our new List of Values in our hidden class called State_All. The SQL for that
filter object should look like this:
State.state In
@Prompt('Select A State or All States','A','Hidden Objects\State_All',multi,free)
OR ('** All States') IN
@Prompt('Select A State or All States','A','Hidden Objects\State_All',multi,free)
Now when the user runs any query
against this universe and selects the “Select A State” condition they will see
this instead:
That should make their selection
process much easier and your life at least a little better. Happy analyzing and
see you next time.
Go to Top |
Return to Newsletter Contents
|
