Hi All,
I was working on this report and had faced this issue and took some time to work around it so thought would be helpful to post it.
Scenario-
I have a dashboard which has reports from different Subject Areas and I’m using a Presentation Variable in the Prompts to filter out the report data. This is quite simple and can be done easily but when choosing ‘All Choices’ option I’m getting an error
Solution
Here I have a Quarter column and the users want to choose All choices to get YTD.
The first thing we have to understand is when All choices are chosen then the OBIEE is actually passing a NULL value. This is dynamiclly taken when its prompted. When passed through a presentation variable then we have to add this filter condition.
> In the report click on the column filter condition and Advances SQL Result. In my case it is for the Quarter column. I used this condition
Time.”Calendar Quarter” = case when ‘@{QUARTER}’ IS NULL THEN CAST(Time.”Calendar Quarter” AS CHAR) ELSE ‘@{QUARTER}’ END
Save the report and is sure to work.
Till next time 🙂
I found something very helpful (at least for me) on this topic. When you filter on a presentation variable if you put a wildcard in the (default) value it will simulate “All Choices”. OBIEE's wildcard is %.
So, if you want to see All Choices, just set the prompt option to blank 🙂
LikeLike
Just to add: I see you're using 10g not 11g and you have your filter set to use OBIEE SQL. So in that situation it would be '@{QUARTER}{%}'
LikeLike
Thanks. I was desperately looking for a solution to this issue.
LikeLike
i am using 11g. for the same issue mentioned above, how can I frame the query?
LikeLike