Thursday, 24 October 2013

How to Make Null value disappear in Dashboard Prompt OBIEE 11.1.1.7.1 with Essbase or any other data Source?


By default OBIEE 11.1.1.7.1 dashboard prompt shows the ‘NULL’ value as one of the drop down value though the actual value in underline column does not have any occurrence of the ‘NULL’ value.  It does not make sense to display a ‘NULL’ value though it does not exist in within the underline database.

This blog is an attempt to explain how I have resolved this issue at one of the customer project, believe me it is very easy to remove the ‘NULL’ value from the drop down list.

Problem Description

A dashboard prompt is created for ‘Product’ attribute from a subject area based on Essbase data source. 
The list of values for the dashboard prompt is as follows, it is clearly showing ‘NULL’ as one of  drop down list value.




Solution
 
Initially I thought let us check the column property in the physical layer, it was unchecked which I believe is right as there are no ‘NULL’ values in Essbase data storage.

I have started looking into the various options linked with the prompt column; the initial settings are as follows.




Then I realized there is a radio button ‘Require user input’ which is unchecked. The option for me is to check that option and check the output. It worked.


 
The solution works for ‘Choice List’ but not for ‘Check Boxes’, ‘Redo Buttons’ etc.  The only option which I can think of is to choose values as – ‘Specific Column Values’ and choose all possible values for dimensional  attributes.


Unfortunately this option is available at prompt column level. Unfortunately I could not able to find out a central place where I can set this for all columns in prompt by default.

I hope you find this information useful.

3 comments:

  1. Good reads, I just found this out for myself. It's unfortunate that check boxes and radio buttons require statically-assigned values in order to get rid of it, which means that you need to make all your prompts choice lists where their source is a column that you can expect to get new values for over time.

    ReplyDelete
  2. Pravine, I realize this is about 18 months old, but I just saw this and thought you might be interested. You have described how to get rid of the NULL from the list. But do you know why the NULL appears in the first place? It's because the column is marked as Nullable in the database and, more importantly, marked as Nullable in the physical layer.

    ReplyDelete
  3. In the choice list values, select sql query to remove the nulls

    ReplyDelete