How to Remove Null Values from prompts in OBIEE

Question

How to remove invalid Null values that are not present in database but showing in prompts in OBIEE. For some of the columns Null Values is valid but for other it is not. So i want to display null value in prompt only if it is valid.

Answer 1

you will have to got to rpd and modify the column in physical layer; mark it Not Nullable (uncheck the Nullable check-box)

Answer 2

Seven years later... is this still useful?

Anyway, I've found out that there is a better solution if you want to avoid using a Choice List wih the "SQL Result" option and stick to the "All Column Values" option (because this one allows you to use the "Limit Values by" option, which I need).

There is a parameter in the instanceconfig.xml file that defines this behavior: "ShowNullValueWhenColumnIsNullable" (wrongly documented in some versions as "ShowNullValueInPromptsWhenDatabaseColumnIsNullable", as per this Support Doc)

You can see its usage in the Fusion Middleware System Administrator's Guide (search of one of the two property names above).

Answer 3

If you have a limited number of possible values, you could chose Specific column Values under Options in the edit prompt dialog box.

Also, checking the box to require user input will sometimes resolve this null value problem, however this is not always the case nor is it always possible depending on your situation.

Additionally, go to Edit Dashboard Prompt, in Choice List Values drop-down list select SQL Results, then write the SQL statements as columnname is not equals to “Unspecfied” (In this way we can remove Null’s also).

Finally, you could go to the Physical Column properties in Physical Layer and Disable the Nullable option by unchecking the box.

Answer 4

I prefer to work in my filter in the where statement (is not null or not like ''). Another situation you might notice is an extra blank row in the filter. This can be removed by setting the preferences is required in the prompt filter.

Good luck.