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).
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
Posts
