Thursday, April 23, 2009

Detecting all choices from dashboard prompts in Answers

If you are using all choices option in dashboard prompts and presentation variable, there is a way to detect this if you write a filter manualy (using convert filter to SQL option in Answers).



Detecting all choices for heading:

case when LENGTH('@{pv_char}')>0 then concat('Calendar month is ', '@{pv_char}') else 'All choices' end

or you can write it like this:

case when LENGTH('@{pv_char}') is null then 'All choices' else concat('Calendar year is ', '@{pv_char}') end

Detecting all choices in filter:

TIMES.CALENDAR_MONTH_DESC = ifnull('@{pv_char}',' ')
ifnull('@{pv_char}',' ')=' '

Put a heading in narrative view below tittle.

Test (all choices):


Test (selected value):


The same is if you are using number column in dashboard prompt and variable pv_number, just replace pv_char with pv_number and CALENDAR_MONTH_DESC with CALENDAR_YEAR for example.


Fiston said...

Very nice. Thanks for this posting.

Anonymous said...

Good Post, even I had used it like this in one of my projects where I had to use variables.

Unknown said...

Thanks a lot! Works like that!

Deniz Su said...


We are using session variable :

And in dashboard page; customer field is prompted.

How can implement all choices with session variable and customer is prompted filter?