Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Conditional Filters – Using Case statements in Filters

Posted by Venkatakrishnan J on February 6, 2008

I had this question come via email twice last week and hence i thought i would blog about it today. Though this is very simple, i thought i would blog about this to show that one can use advances sql clauses in filter expressions. In order to understand, lets first start with a simple report shown below.

      

Now, our aim is to apply filter only for the REGION_NAME = ‘Asia’. So, basically we want a report wherein only for Asia we do not want to display the sales for the year 2003. But for other regions we need the year 2003. In order to do this, all we need to do is to create a filter on REGION_NAME and convert it into SQL.

      

Once this is done, just enter the below SQL

CASE WHEN GEOGRAPHY.REGION_NAME = ‘Asia’ THEN TIMES.CALENDAR_YEAR_NAME ELSE ‘Dummy’ END != ‘2003’

      

What this basically does is, it evaluates the CASE statement first. If REGION_NAME = ‘Asia’ then the filter CALENDAR_YEAR_NAME != ‘2003’ is applied else the filter ‘Dummy != ‘2003’ will be applied (in place of Dummy you can have anything that is not equal to 2003). One you have this filter now you would have a report with year=2003 filter applied only for asia Region.

      

As i said earlier, this is pretty simple and can be considered trivial even. But again, this is just to show you the fact that one can use advanced filter clauses

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: