Tuesday, April 14, 2009

Using presentation variable from the first dashboard prompt as filter in the second dashboard prompt

There was a question on OTN forum
http://forums.oracle.com/forums/thread.jspa?threadID=884660&tstart=0 on how to filter second dashboard prompt which use EVALUATE function with presentation variable which is populated from the first dashboard prompt.

Here is the solution with POWER function in EVALUATE.

First dashboard prompt:

Second dashboard prompt:

Edit column formula:

case when 1=2 then TIMES.CALENDAR_MONTH_ID
EVALUATE('POWER(%1, %2)',@{pv_test}{2},@{pv_test}{3})

If you write this instead of the code above:

case when 1=2 then TIMES.CALENDAR_MONTH_DESC
cast(EVALUATE('POWER(%1, %2)',@{pv_test}{2},@{pv_test}{3}) as char)

then you cannot use decimal numbers in edit-box of the first dashboard prompt to populate POWER function. So do not use cast as char for this solution, leave it in number format.


At initial start, the presentation variable pv_test is 0 and default values are used:

cast(EVALUATE('POWER(%1, %2)',@{pv_test}{2},@{pv_test}{3}) as char).

If we remove default values from code and leave just:

cast(EVALUATE('POWER(%1, %2)',@{pv_test},@{pv_test}) as char)

zero (0) is polulated from pv_test and the result of POWER(0, 0) is 1:

Decimal numbers test:

This works fine.

1 comment:

jwong@epcor.ca said...

This is a wonderful tutorial. I found your response in the oracle forum. I was wondering if you might be able to shed light on my problem. Below is my question in the forum.

"I'm a very novice user of BI, but in reading your response in this thread, I think your solution is similar to what I'm looking to achieve. I am also using 2 prompts on the page. Prompt 1 is a reference date (eg) 2009-01-01 set as a presentation variable "histdate". Prompt 2 takes the "histdate" and sets a relative startdate (eg) "startdate" = histdate - 10. I get that to work using the timestampadd function. Anyways...for prompt 2, since its already a filtered value, I was wondering how I can get it to default to that value, rather than having to select it from the drop down.

This is what I use in the column for prompt2 by the way:

case when 1=2 then "Position Date"."Position Date" else TIMESTAMPADD(SQL_TSI_DAY,-10,Date'@{histdate}') end

Thanks in advance for your help."