In my last Blog I looked at how to import
Essbase UDAs into OBIEE and how to use them to filter reports. The problem with the basic approach to using
UDAs is that the filters need to be hard coded into a report (or a saved filter) - you can’t treat
them as a dimension and let the user interact with them when they view the report.
If you simply try to create a dashboard prompt you merely end up with multiple prompts - one for each UDA - with values of 0 and 1, not overly friendly for your users!
However with some creative use of filters
you can expand their functionality and here I’m going to show how to create a
dashboard prompt to determine which UDA the user wants to filter a report by.
Rather than creating a simple prompt on the UDA column we are going to create a Variable prompt, get it to
set a presentation variable and then create a function on the filter to use
that variable.
So let’s start by creating a new dashboard
prompt. Create a new prompt as usual.
Use the + icon to create a new Variable
prompt:
Enter a name for the presentation variable,
a label for the prompt and then select ‘Choice List’ as the User input option.
From the Choice List Values drop down
select ‘Custom Values’ then use the + icon to manually add the values. I
usually add an ‘All’ option as the default, so once all the values have been
entered, click on the options icon and select
‘Custom Specific Value’ as the Default Selection and click the + icon to
select the ‘All’ option as the default:
Click
ok and save the prompt.
If you now add this to a dashboard page you
should see the drop down list as entered with the All option as the default:
Now we need to add filters to the report to
use the prompt.
I’ve created a simple report which uses
just three fields, the Sales Channel dimension against which we are going to
use the UDAs plus time and the data measure:
Which without any filters obviously shows
all Countries.
We now need to add filters to the report.
However because each UDA is a separate column, we need to add one filter per
column. In this case we have 5 ‘Area’ UDA columns, so we need 5 filters.
Start by adding a new filter for the first UDA Column
Which brings up the New Filter dialog
window:
Now we can’t simply add this column as it
stands as the filter – we need to reference the presentation variable, plus we
also need to allow for that fact that Area A can be selected either by itself
or implicitly when the ‘All Areas’ option is selected.
Also remember from my first blog on using
UDAs that the UDA Columns contain just two values, 1 or 0.
We therefore need to replace the column
formula with a new formula that:
a) Checks the presentation variable
b) Allows for selecting the individual UDA or
the All opton
c) returns a 1
or 0 for OBIEE to perform the filter with.
The following double case statement will do
the job:
case when "Sales
Channel"."Country - Area A" = 1 and
(case
when '@{VAR_SALES_AREA}{All
Areas}' = 'All Areas' then 1
when '@{VAR_SALES_AREA}{All
Areas}' = 'Area A' then 1
else 0
end) = 1
then 1 else 0 end
This formula basically says return a value
of 1 when the UDA column of ‘Country – Area A’ is 1 AND the presentation value
is either ‘All Areas’ or ‘Area A’, otherwise return a value of 0.
This is the link between the UDA column and
the text value we placed in the dashboard prompt – they do not necessarily need
to match, you could take the opportunity to give the UDA columns a more
meaningful name to the users, for example rather than ‘Area A’, the prompt
could have ‘Northern Europe’. In this case the formula would be:
case when "Sales
Channel"."Country - Area A" = 1 and
(case
when '@{VAR_SALES_AREA}{All
Areas}' = 'All Areas' then 1
when '@{VAR_SALES_AREA}{All
Areas}' = 'Northern Europe' then 1
else 0
end) = 1
then 1 else 0 end
Note that I’ve used 'All Areas' as the
default value in the formula: @{VAR_SALES_AREA}{All
Areas}. This isn’t strictly necessary, but does mean the report will run ok
when editing the analysis as the presentation variable won’t have been set.
We enter this into the column formula
screen:
Click ok to save.
If you get the following message
Do as it says and click the 'OK (Ignore Error)'
link.
Back in the New Filter screen we just need
to complete the filter by entering 1 in the value field:
This filter is now complete.
So now we need to repeat this and create a
filter for each of the UDA columns. Each time replacing the two references to
‘Area A’ with the appropriate UDA code.
Once all filters have been created you then
need to change them from AND filters to OR: (you will never get any results
with all five UDA columns filtered with AND!)
If you're creating a report with other filters
then you will need to ensure the groupings are correct – that these UDA filters
are grouped together separately from other filters or groups of filters.
Now just save the report and place it on
the dashboard. The default will of course show all areas:
And changing the prompt will filter the
report as required:
The final step I usually take is to save
the filter itself – that way it can easily be added to any report without the
hassle of adding all of the individual filters.
Note also that the dimension associated with the UDAs (Sales Channel in
this case) does not need to appear in the report – like any other filter it
will be applied to the data even if the dimensions are not specifically shown in the
report.
