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).

Prompt:



Report:



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}',' ')
or
ifnull('@{pv_char}',' ')=' '

Put a heading in narrative view below tittle.

Test (all choices):



NQQuery.log:



Test (selected value):



NQQuery.log:



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.

4 comments:

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.

Satya said...

Thanks a lot! Works like that!

Deniz Su said...

Hi,

We are using session variable :
NQ_SESSION.Customer

And in dashboard page; customer field is prompted.

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