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
Posts
