OBIEE When the Top N Filter fails… (repost)

If you’ve read my previous post about Using the Top N Filter, this will be a follow up post that covers a trick I discovered when the Top N filter didn’t do what I wanted it to do.
My requirement is to only show data for the past 6 weeks.  I have a base table that has massive amounts of weekly data, designated by a column week_ending.  At first, I thought I could simply add a Top N filter for "Week Ending" in Top 6.  This didn’t work however, since I have multiple rows containing the same date - it only returned data for the latest date in the table (which makes sense, because that date would be the same for each of the Top 6 since it occurs many times).  I also tried using the DISTINCT keyword in various places in the filter formula, as well as in the column definition - still no luck.
What I ended up doing was the following:

1. I decided to turn to SQL to give me the results I need.  I started by building a query that gave me the Top 6 dates in my base table:
   

   SELECT * FROM (SELECT DISTINCT week_ending
   FROM base_table
   ORDER BY week_ending DESC)
   WHERE rownum <= 6;
   
   WEEK_ENDING
   ------------------------- 
   13-JUL-08
   06-JUL-08
   29-JUN-08
   22-JUN-08
   15-JUN-08
   08-JUN-08                 
   
   6 rows selected

   
   
2. 
   
   
   
   Next, I used the query above in the WHERE clause for a view:
   
   

   CREATE OR REPLACE VIEW top_six_weeks_vw AS
   SELECT *
   FROM base_table bt
   WHERE bt.week_ending IN (SELECT * FROM (SELECT DISTINCT week_ending                                          FROM base_table
   ORDER BY week_ending DESC)
   WHERE rownum <= 6);
   
   

   
   
3. 
   
   
   
   Now, simply model this view in the OBIEE Repository, and your Answers reports will only display the latest 6 weeks worth of data.
   
   

This article was original posted on the Kevin C. oraclebi blog. See: http://obiee101.blogspot.com/2009/09/obiee-blog-lost.html









Till Next Time