How to choose null, not null or all values from drop down list

Example

Prompt the user to select only null, not null or all values from drop down harcoded list and reference it to the column.

Table for this example:

create table value_test (id number,col1 number, col2 varchar2(100))


After importing table to Administrator we join it to the dummy fact table in the BMM (not shown).

Dashboard prompt with drop down harcoded list:


SQL Results:

SELECT case when VALUE_TEST.COL1 is not null then 'Not null' when VALUE_TEST.COL1 is null then 'Null' end FROM "Write back test" union all SELECT case when 1=2 then VALUE_TEST.COL2 else 'All' end FROM "Write back test"

Put All as default:



Answers report:


Advanced SQL filter:

case
when '@{pv}'='All' then 'All'
else
case when VALUE_TEST.COL1 is not null then 'Not null' else 'Null' end
end='@{pv}'

Test

All (column col1 all values):


NQQuery.log:



Not null (column col1 is not null):


NQQuery.log:



Null (column col1 is null):
 

NQQuery.log: