Creating a dashboard prompt for Essbase UDAs

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

Invalid Alias Format : Table_name.Column_name required. OK (Ignore Error)

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.