How to apply case statements in where clause in OBIEE?

This could be a common reporting scenario and I just wanted to write this post, on how to create a case statement inside of a filter condition. This also works if dashboard has two or more prompts, and they should filter data using a combination of values. For example, consider the following requirement:

You have data for year 2009 and 2010. Per the requirement, you want to select quarter 1 if year is 2009, while you want to select quarter 2 if its year 2010. So, its two different sets of criteria for which data should be selected.

The case statement may be more complex than this example, the solution below will still work.

This is done per the steps below:

1. Add a filter on Year to report.

2. Edit filter > Advanced > convert this filter to SQL

3. In Advanced SQL Filter, type the filter per the expression below:


1 = case
when Date."Report Year" = 2009 and Date."Report Qtr" = 1 then 1
when Date."Report Year" = 2010 and Date."Report Qtr" = 2 then 1
else 0 end

Click results, and verify ..

Viola, it works. We are successfully able to apply a case statement in a filter criteria. This can be used in any further combinations per requirements.

Explanation:

What we are doing in the case statement is that we are bucketing records we want to select as 1, and everything else as 0. Next we have "1 = ". So for every records we bucket as 1, the condition becomes 1 = 1 which is true, while for rest it becomes 1 = 0, which is false, simple?

To demonstrate this, lets take the expression from filter, and add it as a column (remove "1 =" and add rest of the expression) as below:

Hit results:

Clearly you can see, our records are marked as 1, while things to be filtered out are 0.

Here is how BI fires the query for this:

select distinct T1161285.RPT_YEAR as c1,
     T1161285.RPT_MONTH as c2,
     T1161285.RPT_QTR as c3
from
     INVRDM.WC_CALENDAR_D T1161285 /* DIM_WC_CALENDAR_D */
where  ( case  when T1161285.RPT_QTR = 1 and T1161285.RPT_YEAR = 2009 then 1 when T1161285.RPT_QTR = 2 and T1161285.RPT_YEAR = 2010 then 1 else 0 end  = 1 )
order by c1, c3


This is a perfect way to add case statements inside the where clause.

To take is a step forward, now imagine user need to select month and quarter in the prompts. For example user wants to select year 2009 and quarters 1, 2 and 3.

In this case, we will add the year and month as filter and set them to is prompted. This is in addition to the case statement above as shown below: