Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Rolling Year,Month and Date filters – Moving Window Filters

Posted by Venkatakrishnan J on March 11, 2008

I have received this question a couple of times before and this also came up in the forums today. Lets look at the question first (modified version of the original question to make it more clearer). “I have a dashboard prompt wherein the user can choose any year (using a dropdown). I also have a report which shows sales across all the years (containing a lot of historical data). Now, based on the year that was chosen in the dashboard prompt i would like to restrict the report to only the previous 4 years of the selected date“. Though this question is pretty straight forward, i thought i would blog about it to serve as a point of reference for future questions like this. Just to make it a bit more complex lets also make the number 4 above dynamic. Lets start first with creating a dashboard prompt on 2 columns. One prompt would extract the year from a date field and the other would just let the user enter some numeric data. Make both the columns to set presentation variables promptdate and promptnumber. The first column would have a formula to extract the year from the date.

YEAR(TIMES.CALENDAR_MONTH_START_DATE)

And the second one would just let the user to enter any value. So, in order to achieve that enter the below formula

CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Test’ END

      

Once this is done, lets create a simple report containing 2 columns. CALENDAR_MONTH_START_DATE and its corresponding SALES.

      

Now, create a filter on any of the columns and convert that filter to SQL.

      

Now, in the SQL box enter the below filter formula.

TIMES.CALENDAR_MONTH_START_DATE >= TIMESTAMPADD(SQL_TSI_YEAR, – @{promptnumber}{2}, TIMESTAMP ‘@{promptdate}{2003}-01-01 00:00:00′)

Similarly, create another filter and enter the below formula after converting it to SQL.

TIMES.CALENDAR_MONTH_START_DATE <= TIMESTAMP ‘@{promptdate}{2003}-12-31 00:00:00′

So, what the above filter does is it dynamically varies the window of the filter that is being applied. Notice that even this SQL box can accept presentation variables.The other important thing to note in the formula is the use of negative year values in TIMESTAMPADD function. This will move the year backwards.

      

Now, if you go to the dashboard prompt and enter 1 and 2006 in the prompts we would get a report containing the dates from 2005. Similarly, if you enter 2 and 2005, it would get a report containing dates from 2003 till 2005.

      

      

Again, very simple but can be useful in a lot of situations.

About these ads

5 Responses to “Oracle BI EE 10.1.3.3/2 – Rolling Year,Month and Date filters – Moving Window Filters”

  1. Ashok said

    Nice example. Wonder how will you display both previous years and forecasts for the reports. So I need to report both -4 years and +4 years in my report. How do I do that?

  2. Puneet Arora said

    I agree with the solution that has been provided here for the rolling months or years.
    But, I would better opt for aggregating the data at different levels and then go for data modelling, thus making the model looks more simpler (as the complexity has been taken care by any ETL Tool(for eg – Informatica) by creating aggregate tables) and also improving the performance.

    I am sure, if I go by this approach, and would select aggregation at quarterly and annually, the report would definitely going to take huge amount of time.

    Also, I have seen people using presentation variable even though when it is really not required.It is a good functionality, no doubt about that, but simple things always are good to see and good to experience.The more complexity you will produce at web level, the more the performance would be hampered.

    And, OBI EE is just not all about fetching the data but also with a good performance. I think users would definitely agree with me on this.

  3. Jeremy said

    Hey Venkatakrishnan,

    Thanks for the helpful post. I was wondering, are you able to get your screenshots to reload for this article? I can view your screenshots fine for your other articles, but I am having troubling viewing some of these.

    Many thanks,

    jeremy

  4. [...] Oracle BI EE 10.1.3.3/2 – Rolling Year,Month and Date filters – Moving Window Filters [...]

  5. Sajid said

    Hi:
    I tested your solution, and it does not seem to work correctly. Once you enter a month, next time you can not leave it blank. Month has to be entered as 0(Zero).
    If you leave month blank, query does not populate the default value initially specified.

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

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: