It's almost two years now I joined the Rittman Mead team and never wrote on this blog before, time to start. For this first post nothing long, I wanted to share something I had to setup few weeks ago on a project and it can be interesting for others as well.
My need was related to dashboard prompts: it's easy to have all of them optional (the default behavior) or the make a prompt (or many of them) mandatory. But if you need to have at least one mandatory field between many in a dashboard prompt? For example if you have a prompt to select a customer and it contains 2 fields: "customer name" and "customer number". A user can select a customer based on its name or number and I want the user to always select a customer, so at least one of these 2 fields needs to be mandatory.
There is not a magic checkbox in OBIEE to achieve this behavior, setting both fields as mandatory means the user need to enter the customer details twice all the time, not acceptable from a usability point of view.
There is a way to simulate this behavior and will not impact (or in a negligible way) performances without generating unneeded queries on the database.
First I create my dashboard prompt and for my 2 fields I set a presentation variable: pv_CustomerName and pv_CustomerNumber in my example.
Then I create an analysis where I will perform the check to detect if at least one of my prompt fields is set. Add a single column, not important which one of your subject area because I will edit the formula to set a fixed value, a value I'm absolutely sure doesn't exist in my prompted fields, so for example something like 'x0xx_I_can_not_exist_xx0x'. Thanks to this unique column with a fixed value this analysis will never send a query to the database, the BI server will manage it.
Time to add some filter to this analysis, my filters will be set on this single column and will use the presentation variables I defined in the dashboard prompt. I set the condition to be 'is equal to / is in' and as value I choose "Presentation variable" and enter the name of the first of my variables and, really important, I set a default value: x0xx_I_can_not_exist_xx0x. Does it remind you something? Yes, it's the same strange value I manually set as being the value of the my unique column (really important, it must be exactly the same value).
What will this filter do? If there is no variable set the filter is like a "1=1", if there is a variable set from the prompt it will behave like a "1=2". Now add all the other presentation variables in the same way using a "AND" condition.
This analysis will return exactly a unique row with the value of "x0xx_I_can_not_exist_xx0x" or no rows at all, I will use it as a condition to know if at least one dashboard prompt is set or not.
Save this analysis ideally with the word "condition" in the name as we will use it as condition to know if the prompt is filled or not (mine is called "condition_no_prompt_set").
Time now to finish the work as we have all the elements. Edit the dashboard where you want to add the "at least one mandatory" field functionality, add the dashboard prompt and 2 additional sections: in one of these sections add the analysis you want to run when at least one field is set, in the other one add a text object with a polite message asking the user to use at least one of the dashboard prompt fields.
Last step is to add conditions to decide when to display the first or the second section.
For the one with the message set a condition when the "condition_no_prompt_set" analysis return 1 row, in the other section (the one with the analysis to be executed if the prompt is correctly set) set a condition on the same "condition_no_prompt" analysis when it return 0 rows.
Done! I have my "at least one mandatory dashboard prompt" as you can see in the next screenshots:
And the nice thing is that the condition managing the display doesn't generate any query on the database (as you can see in the logs below), only the BI server will get the query and it's a really simple one, similar to a "SELECT 'xxxx' FROM dual WHERE 1=1".
