Topic
2 replies Latest Post - ‏2013-04-25T15:13:10Z by skandal
skandal
skandal
2 Posts
ACCEPTED ANSWER

Pinned topic NULL Value selection in Prompt

‏2013-04-24T17:44:31Z |

Hi,

 

I have a multi select checkbox prompt, which displays Class One of the values in the prompt is NULL so it shows a box with text after it. If the report is executed, after selecting this NULL value explicitly, then it gives error, UDA-SQL-0357.

I changed the Prompt Query, and used NVL( CLASS, '-1' ) to replace the NULL value coming from database. Then in my Report Query filter, I checked for

NVL (CLASS, '-1' ) = ?p_Class?

Now, I do not get any error message, but the report does not display any data.

How do I handle this NULL value selection in Prompt?

Thanks

Sachin

 

  • OpherB
    OpherB
    638 Posts
    ACCEPTED ANSWER

    Re: NULL Value selection in Prompt

    ‏2013-04-24T19:47:42Z  in response to skandal

    Your question is not entirely clear. Did you mean that "...so it shows a box with NO text after it."?

    Regardless, the checkbox prompt is populated by a query - why not just filter out the Null values in that query?

     

    Regards,

    Opher

     

    • skandal
      skandal
      2 Posts
      ACCEPTED ANSWER

      Re: NULL Value selection in Prompt

      ‏2013-04-25T15:13:10Z  in response to OpherB

      The prompt page as 3 different prompts

      Territory, Class , Date Range

      The Class prompt is optional depending on the selection of the Territory. Some Territories have valid values for the Class and also a NULL value, There is fact data associated with Class = NULL. So NULL values cannot be filtered out from the prompt or the main report queries. Since, the prompt is optional, no selection would include all Class values (including NULL). But in rare cases users have been selecting the NULL value from the prompt and that leads to report error.

      I have tried to replace the NULL value with a default like NVL (CLASS , 'Blank') in the prompt query so that the prompt does not show just a check box with no label. Now, I have to also change the main report query so, if some users select 'Blank', then report filter, I have to include

      NVL (CLASS , 'Blank') in (?p_Class?)

      This does not give any error, but the report comes back with NO DATA.

      How do I change the report so that, NULL (or Blank ) selection still executes the report successfully.