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:
- 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
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);
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