OBIEE – Dynamic Rolling Month Supporter

Scenario – Your dashboard has a requirement to show a Future Rolling months report and a Trend Analysis Rolling report where Number of rolling months and Rolling from months are dynamic (user prompted) in both the reports. Usually Rolling months are calculated using Sysdate or Current Date. But there can be a different scenario with dynamic N rolling months from Nth month with the number of rolling months in future (For Forecast Reports) as well as in past (For Trend Reports).

Solution – This requirement can be implemented easily by creating dashboard prompts, presentation variables for the prompts and a rolling month supporter report, in addition to your trend analysis report/ forecast report.

The following are the steps of implementing this requirement:

1) Create a dashboard prompt with i) Month and ii). No. of rolling months. I have taken Year column from Time Dimension to create an Edit Box for the Rolling Months, but you can use any of the columns. Since I will be providing an Edit Box for the user to add the number himself, I am taking any column here to use as Edit Box. You can also pre-populate the number in a drop down.

2)Create a presentation variable for the number of rolling months var_rollno.

3)You can give a default value for the Edit Box. I have given as 12.

4)Similarly, pull the month column from the time dimension and create a presentation variable for this as var_month.

5)Alternatively, you can set default selected value for this prompt but that is optional and not needed.

6)Now create a request with 4 columns respectively “Time Dim”. Month, “Time Dim”. Date, another “Time Dim”. Date and again another “Time Dim”. Date.

7)Rename one “Time Dim”. Date Column As “Last N th Month Dt”

8)Rename other “Time Dim”. Date Column As “Next N th Month”

9)Write the following formula for “Time Dim”. Date Column as- MAX("Time Dim".Date)

10)Write the following formula for “Last N th Month Dt” Column to get last nth month date for respective Date. This will give us the month in the past. MIN(TIMESTAMPADD(SQL_TSI_MONTH,-CAST(@{var_rollno} AS INT),”Time Dim”.Date))

11)Similarly, write the following formula for “Next N th Month” Column to get next nth month date for respective Date. This gives us the future month. MAX(TIMESTAMPADD(SQL_TSI_MONTH,CAST(@{var_rollno} AS INT),”Time Dim”.Date))

12)Create a filter on “Time Dim. Month” by assigning this to presentation variable “var_month” presentation variable.

13)Save this report with “Rolling Month Supporter”.

14) Now create the Trend report in which we are showing the Month wise Quantities.

15)In the criteria tab, select the columns “Time Dim”. Month, “Invoice. Quantity“ Columns.

16)Create the filters based on the “Last N th Month Dt” and “Date” Columns from “Rolling Month Supporter” Report.

17)Save the Trend report.

18)Now create a dashboard page and add the Dashboard prompt and the main Trend Report

19)Select the Month from the drop down list and Enter no. of Rolling months in edit box

20)You will get the last n months data for your given input values. Forecast report will also be developed similar to the trend report, but the filter for the same will be: