5 Replies Latest reply on Jul 24, 2014 10:25 AM by obiee_newbie

    OBIEE 11G - Null in Prompts




      I have a text field which is nullable. My requirement is to use it in prompts and dropdown must not display null.

      Changing the physical column properties (uncheck nullable) is ruled out since i need to display the same text field in report which should display all that is available in table.

      Also, I cannot create a prompt with SQL results since i have to apply 'limit values by' clause for the prompt. Even i tried hardcoding the nulls to some text but still the prompt renders a Null in addition to the hardcoded value. Is it the default behaviour for a text field with nulls when used in prompts or am i missing something to remove the null. Please help me in this regard.




        • 1. Re: OBIEE 11G - Null in Prompts
          Rajesh Gurram - BI

          Apply column formula on the prompt:

          CASE WHEN column_name is not null then column_name end


          Pls mark if correct/helpful.

          • 2. Re: OBIEE 11G - Null in Prompts

            Make the column prompt as mandatory (in options, click on "Require User Input" check box), Include all column values if user does n't want to select specific value, this will eliminate the NULL values

            • 3. Re: OBIEE 11G - Null in Prompts

              make the column as not null column in the repository.




              • 4. Re: OBIEE 11G - Null in Prompts


                If you cannot uncheck the "Nullable" property of the physical column and cannot use SQL to limit values in your prompt, there are very limited chances to resolve your requirement. I think you cannot simply select "Specific Column Values" in your prompt, since your dimension table is not static. Furthermore, there's no way to implement the "Limit values by" in alternative ways ("SQL Results", etc).

                I think that (dimension) columns that are used in prompts should always have a value (at least a default value) - in this way you can always treat columns as not "nullable", simplifyng the whole thing. Furthermore, if you exclude the NULL value from your prompt, you are excluding the chance to filter on this "values".

                If you cannot have a default value for your physical column, consider the chance to modify your BMM so to default your column to some conventional value when NULL, for example: IFNULL("Customers"."Geographic Area", 'N/A').

                Hope this helps,


                • 5. Re: OBIEE 11G - Null in Prompts

                  Thanks for the reply guys..


                  I resolved the issue with creating a new group without null (using selection steps and saved it) and used the same in prompt selecting 'Members of Groups' in the choice list value. Thanks for the suggestions again.