In a recent article: http://obiee101.blogspot.com/2009/02/obiee-switching-between-filters.html I showed you how to create dynamic filters. This time I want to show you on of the ways to create dynamic prompt content. First create a small dropdown prompt which contains a FilterName LOV:
SELECT case when 1=0 then "D2 Market"."M01 Market" else 'Market' end FROM "Sample Sales" Union all SELECT case when 1=0 then "D2 Market"."M01 Market" else 'Area' end FROM "Sample Sales" Union all SELECT case when 1=0 then "D2 Market"."M01 Market" else 'District' end FROM "Sample Sales"
Put the result in a presentation called "filtername"
Create a new promt for the dynamic content in the column put:
case when '@{filtername}{Market1}'= 'Market' then "D2 Market"."M01 Market" else case when '@{filtername}{Market1}' = 'Area' then "D2 Market"."M02 Area" else case when '@{filtername}{Market1}'= 'District' then "D2 Market"."M03 District" else "D2 Market"."M03 District" end end end
WOW: It's very sensitive on the place of the single quotes!
Add a dynymic filter to your report:
Till Next Time
7 comments:
Hi John.
Are these two fields on two separeted dashboard prompts, each with its own 'GO' button?
So you need to activate (to fill presentation variable) from the first 'GO' and then use this filled variable on a second dashboard prompt and then after second 'GO' report is filtered.
If yes, is there some way to have only one dashboard prompt with these two fields so that second field see a presentation variable from the first field when we change it from a drop-down list.
This would be great to have in the future.
Regards,
Goran
http://108obiee.blogspot.com/
I know that we can not populate presentation variable just with changing value from a list, for now. The only way is with 'GO' button. To bad.
Regards,
http://108obiee.blogspot.com/
Hello John:
This article is very helpful on what I am trying to do.
I now have a dynamic filter which is constructed based on the prompt value derived using the presentation variable.
I have the following question?
1/ Is it possible to make this dynamic filter prompted to one presentation variable from the prompt?
Thanks
Sankar
@sankar,
I'm not sure what you are trying to achieve, can you eloborate?
regards
John
Hi John,
thanx for this good post, i have a requirement where i have a report on the dashboard where i created this dynamic prompts and the report has a column from time hierarchy using this prompt and used as column selector in same report to flip from year thru week.
Requirement is to default the report with different values for different levels in hierarchy i.e. year = rolling 4 yrs, quarter =rolling 6 qrtrs etc; so if i select the LOV in 1st prompt ex:year then i can see values of column-year in 2nd prompt.
Now i'm not able to pass this value to report(Pivot view) which has to change dynamically my column selector.
Is there any possibility to achieve this requirement.
Also at creating the filters in report in your example i see '@{FilterName}' is equal to/is is Market, i'm confused a little over here, is it a presentation column in the report or a new pres.variable ?? because i'm not seeing this variable refered in other place on dashboard.
please help me over here with your reply.
thanks in advance.
Hi ,
Do you have any recommendation to achieve this in multi select prompt ? I have multiple flags in my fact table. I want them to be listed in multi select prompt and when user selects one or more flags it should dynamically add filter flag(selected from multi select prompt) = 1.
Thanks !!!
Post a Comment