Scenario: How to display last 3 months data on OBI EE report by using Month Prompt.

Here we have a scenario like to get data for last N no.of months by using Month prompt

1.     Create a Page level Dashboard prompt with Month.

2.  Assign a presentation variable  pv_month to “Month” with drop down list control.

3.  Now  create a request  with 3 columns  CALENDAR_MONTH_DESC, TIME_ID Rename it to MAX CURRENT DATE and again TIME_ID rename it to Nth Month roll.

4. Write the following formula for MAX Current date

            MAX(TIMES.TIME_ID)

5.   Write the following formula for Nth Month roll

      MIN(TIMESTAMPADD(SQL_TSI_MONTH,-3,TIMES.TIME_ID))

     To get last 3 months date for respective calendar date.

6.  Create a filter on calendar_month_desc column by assigning this to presentation variable var_month.
Save this report with the name “Monthdata”.

      7.Now create the “Main report” which we are showing the monthly Quantity sold and Amount sold .

      So create a report with the columns CALENDAR_MONTH_DESC, QUANTITY_SOLD and AMOUNT_SOLD.
     

8. Create a saved filter with the name month_sfilter. Apply saved filter to mainreport.

9.  Now create a dashboard page with Month_prompt and the Main report.

     Select Month from the drop down list and click GO. You will get the last 3 months data for given input value. For example I have taken month=1998-06 so the report is