Monday, January 31, 2011

NULL value in multi-select prompt


There is a NULL value in the Multi Select Prompt. When this blank value is selected with other selections from the prompt, the prompt either blanks out or the results of the report returns all values of the field instead of filtering only for the selected criteria.
Cause
The cause of the issue is to have a NULL value (it comes from the datawarehouse) as an option in the multi-select prompt because when is selected, in the textbox this symbol ” appears as the selected option and the filter does not work.
It was tested inhouse adding NULL values in the dimension and fact tables of a business model.
Solution
There are four options to resolve this issue:
1.- Validate if NULL values are relevant for the bussiness analysis (for some reason in the datawarehouse NULL values were stored). If they are not, you have to delete them in Dimensions and Fact tables.
2.- Change the physical table type in your rpd in all tables you have NULL values and use a SELECT like this:
SELECT LOGIN, RESP
FROM NQ_LOGIN_GROUP
WHERE NOT LOGIN IS NULL
Doing this, you only have NON-NULL values in your Multi-select prompt.
3.- Replace the NULL value with a new name like UNSPECIFIED in ALL tables you which are related.
4.- Log an enhancement request to review that multi-select prompts works with NULL values.
Customer decided to apply the third option, change the NULL values by a value like UNSPECIFIED. They couldn’t delete the NULL values so that was the best option for them.
Also I’ve already logged an enhancement request to review that multi-select prompts works with NULL values or display an error or warning message.

No comments:

Post a Comment