Last N months(Rolling months) data By year-month prompt in OBIEE

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

To arrange the columns in alphabetical order automatically in presentation tables

1. Log into OBIEE Administration
2. Go to Presentation layer and right click on the table in which you want to arrange the columns alphabetically
3. Now click on the “Name” tab

4. Click Ok to get the columns in alphabetical order

5. And now observe that the columns in that table are sorted automatically

Changing the Precession of Percent of columns in Pivot table of OBIEE

We have an added feature “Percent of” to find the percentage for a measure w.r.t a dimension in pivot table. Here using this “Percent of” feature we will get the percentages up to single Decimal place only.
If the requirement comes like to increase the Decimal places for this percent of calculated columns. In OBIEE there is no direct option to achieve this requirement. For this we need to alter the Advanced XML to achieve this.
The detailed procedure is as follows. I had worked this task with Paint Repository (Which came by default with OBIEE)

1. Take Markets. Region and Sales Measures. Dollars from Criteria
2. Go for pivot view
3. And create one duplicate column for dollars and rename the duplicate dollars column as “Percentage of Region”
4. Now to get the “Percentage of Region” values as percentage of dollars w.r.t Region go for column properties tab and click the Show Data As->Percent of->Column

5. Now we will get the percentage of values for “Percentage of Region” Column

6. Here Observe the percentages values and their Decimal places, we have only one decimal place for the values

7. Now to increase these decimal places we need to alter the Advanced XML as there is no direct option to do this.
8. Click the Advanced Tab to alter the XML

9. To Increase the Decimal places for percent of column we need to change the “mindigits” and “maxdigits” values to require no. of Decimal places we want to show.so search for the “minDigits” and “maxDigits” tags

Percentage of Region

10. Now change this mindigits and masdigits values to ‘3 ‘ to get 3 decimal places percentage values

Percentage of Region

11. Now click the button and view the results in pivot table
12. And observe the results for percent of column with 3 decimal places as follows

13. By this way we can show the required no. of decimal places for pivot table’s percent of column

OBIEE Reporting Guidelines

• Refine OBIEE RPD (Repository) as per  requirements
• Change The Database and Connection Pools Settings in the Repository
• Import  the Cusotm Tables Required for the Project in the Repository
• Create the Joins for the Dimensions & facts using the Joins Manager.
• Create the Aliases to implement the joins as per the requirements
• Check the Physical model of the Repository
• Drag & Drop the Objects to the Logical Layer
• Rename and Remove the columns from the Logical Layer as per the Requirements.
• Remove the Hard Coded FK Key join in the Logical layer.
• Create the Complex Joins Between the Facts & Dimensions.
• Create the Dimensional hierarchy for the Logical Tables.
• Associate the hierarchy level in the Fact Sources
• Create the New Measures Required for the Project.
• Create and Realign the Objetcs in the Presentation Layer as per the Business Needs.
• Check  the Local and Global Consistency Check of the Repository
• Fix the Errors, Save the Repository and Deploy the Repository
• Make the Configuration Changes in the NQS config File.
• Restart the Oracle BI server and Presenation server services to Refelct the changes made in the Repository.
• Creation of New Reports in OBIEE Answers as per the Design Document.
• Creation of Pages as per the Design Document.
• Creation of Dashboard and Association of Pages with Dashboard
• Stop the OC4J/IIS and Oracle BI Server to get the Final Web catalog.

“All pages” In Pages Block of Pivot View

1.      Select Periods. Year, Sales Measures. Dollars  From Paint Subject Area

 

 

 

2.      Next Click On the Pivot view

 

 

 

3.      Now you will see the following screen

 

 

 

4.      Create one Duplicate column for Year as follows

 

 

5.      Now move the Duplicate column to “Pages” Block of the Pivot table

 

 

6.      Once you moved the Duplicate year column to Page block then you will get one drop down list to select the Year, as per the drop down list value the pivot view displays  the respected year data.

 

 

 

7.      As per the drop down list you can select only one option for year prompt. And there is no option to display all year data.

 

 

8.      To have all years data in addition to the existed values for prompt. Click the  option and select the “Before” value to get the “All Pages” option as first and default choice of your Prompt. If you selected “After” value then “All Pages” option will display as last choice in drop down list.

 

 

 

9.      Now you have the following options in your drop down list

 

 

 

10.  Finally the Report will be as

 

 

11.  Finally the report renders automatically and displays the correspondent data as per the Dropdown list choice