Normally we will have the scenarios like to get data for last N no.of months from the selected date. Rather than this normal scenario if the user wants the same report but with the year and month prompts only instead of the calendar date.We developed this approach in the following procedure (I myself worked on sample sales rpd, which comes with OBIEE 10.1.3.4.0 by default). 1. Create a dashboard prompt with a). Year, b). Month and c). no. of rolling months. 2. Assign presentation variables var_year, var_month, and var_rollno to year with drop down list control, month with drop down list control and no.of rolling months with edit box control respectively. 3. Now create a request with 4 columns respectively “D0 Time. T05 Per Name Year”, “D0 Time.T32 Cal Month”, “D0 Time. T00 Calendar Date” and again another “D0 Time. T00 Calendar Date”. 4. Rename the second “D0 Time. T00 Calendar Date” Column As “Last n th Month Date” 5. Write the following formula for “D0 Time. T00 Calendar Date” Column MAX(“D0 Time”.”T00 Calendar Date”) 6. And Write the following formula for “Last n th Month Date” Column to get last nth month date for respective Calendar Date MIN(TIMESTAMPADD(SQL_TSI_MONTH,-CAST(@{var_rollno} AS INT),”D0 Time”.”T00 Calendar Date”)) 7. Create a filter on “D0 Time. T05 Per Name Year” by assigning this to presentation variable “var_year” and “D0 Time.T32 Cal Month” to “var_month” presentation variable. 8. Save this report with “Year Month Supporter”. 9. Now create the main report in which we are showing the Monthly wise Quantities. So take the columns “D0 Time. T05 Per Name Month”, “F2 Units”.”2-01 Billed Qty (Sum All)“ Columns and save the report 10. And create the filters based on the “Last n th Month Date” and “T00 Calendar Date” Columns from “Year Month Supporter” Report 11. Now create A dashboard page and add the Dashboard prompt and the main report 12. Select the Year ,Month from the drop down list and enter no. of Rolling months in edit box 13. You will get the last n months data for your given input values 14. For eg:I have taken year=2008,month=9 and no. of rolling months=11 so the report is

Normally we will have the scenarios like to get data for last N no.of months from the selected date. Rather than this normal scenario if the user wants the same report but with the year and month prompts only instead of the calendar date.
We developed this approach in the following procedure (I myself worked on sample sales rpd, which comes with OBIEE 10.1.3.4.0 by default).

1. Create a dashboard prompt with a). Year, b). Month and c). no. of rolling months.

2. Assign presentation variables var_year, var_month, and var_rollno to year with drop down list control, month with drop down list control and no.of rolling months with edit box control respectively.

  

3. Now create a request with 4 columns respectively “D0 Time. T05 Per Name Year”, “D0 Time.T32 Cal Month”, “D0 Time. T00 Calendar Date” and again another “D0 Time. T00 Calendar Date”.

4. Rename the second “D0 Time. T00 Calendar Date” Column As “Last n th Month Date”

5. Write the following formula for “D0 Time. T00 Calendar Date” Column

MAX(“D0 Time”.”T00 Calendar Date”)

6. And Write the following formula for “Last n th Month Date” Column to get last nth month date for respective Calendar Date

MIN(TIMESTAMPADD(SQL_TSI_MONTH,-CAST(@{var_rollno} AS INT),”D0 Time”.”T00 Calendar Date”))

7. Create a filter on “D0 Time. T05 Per Name Year” by assigning this to presentation variable “var_year” and “D0 Time.T32 Cal Month” to “var_month” presentation variable.

8. Save this report with “Year Month Supporter”.

 

9. Now create the main report in which we are showing the Monthly wise Quantities. So take the columns “D0 Time. T05 Per Name Month”, “F2 Units”.”2-01  Billed Qty  (Sum All)“ Columns and save the report

  

10. And create the filters based on  the  “Last n th Month Date” and “T00 Calendar Date” Columns from “Year Month Supporter” Report

    

11. Now create A dashboard page and add the Dashboard prompt and the main report

12. Select  the Year ,Month from the drop down list and enter no. of Rolling months in edit box

 

13. You will get the last n months data for your given input values

14. For eg:I have taken year=2008,month=9 and  no. of rolling months=11 so the report is