Wednesday, 8 April 2015

OBIEE Dashboard Prompt Eliminate Required Field with Implicit Defaults

Sometimes report requisitioners insist on getting table views showing each data row in detail. Without applying a filter such reports in general don't return results within an acceptable runtime. Additionally the Oracle BI server and database are stressed unnecessarily. To avoid long running queries when opening a dashboard you can implement a simple solution. Just set a filter to a value which doesn't exist:


Such a filter implies to provide a prompt for the same field which must be set to be required (see asterisk):


If the user doesn't need to insert a value then the value n/a in the report isn't overriden and query returns an empty result set again. Setting a proper default for the field isn't always possible or makes no sense at all, because users don't know adequate values or want to filter other detail fields, e.g. Title in our case. Thus, Product No has to be set with a default implicitly after selecting a Title. To do so OBIEE dashboard prompt settings should be:


Especially the options Include "All Column Values" choice in the list and Limit values by Title are necessary. Implication after setting the Title:


Product No is set to (All Column Values) without the need to insert values explicitly with the result that the required field is eliminated.

2 comments:

  1. I'm not sure I'm following the last part of your example. Are you keeping the analysis filter for product no. = n/a ?

    ReplyDelete
    Replies
    1. No, it's not kept. As the filter is set in the report it needs to be overriden. Otherwise the result set would stay empty. OBIEE recognizes the value "(All Column Values)" as valid for the required field in the prompt. But when running the query OBIEE removes the report filter for Product No.

      Delete