To Roll up last N months data in OBIEE 11g (Based on Month Prompt MMM-YYYY)

I came across a requirement, where user has to select a value from the month prompt(Ex:- DEC-1981) and report should display the data for the past 4 months (DEC-1981, NOV-1981,OCT-1981 and SEP-1981) .
Here month prompt is derived from the date column.

Results should look like this:-

Steps followed:-
--> Create a dashboard prompt and assign a presentation variable.
--> Create a helper report to restrict the dates
--> Create the base report to see data and filter dates based on helper report

Create a dashboard prompt and assign a presentation variable. 

EVALUATE('TO_CHAR(%1,%2)' AS CHAR,"Scott_emp"."HIREDATE",'MON-YYYY')

Create a helper report to restrict the dates

Helper report is used to list the dates that fall in the last 4 months.This is filtered with the selected month value and gives you the starting and ending date for the four months.

For First Day:-
TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( TIMESTAMPADD(SQL_TSI_MONTH,-4,"Scott_emp"."HIREDATE")) * -(1) + 1, TIMESTAMPADD(SQL_TSI_MONTH,-4,"Scott_emp"."HIREDATE")))

For Last day:-
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( "Scott_emp"."HIREDATE") * -(1) + 1, "Scott_emp"."HIREDATE")))

Create the base report to see data and filter dates based on helper report

Create report with the required columns and the filters to restrict the report with the dates.

Add a filter on the date column as below

Add a filter on the date column as below

Save all the reports and add them to the dashboard page and output looks like this

[nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE

Evaluate function enables users to use database functions in OBIEE Answers/Rpd

Below is the error message when using EVALUATE in OBIEE answers.
Error Message:-
[nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE

 To fix the issue, change the value EVALUATE_SUPPORT_LEVEL to 2 and restart the opmn services.

OBIEE 11g direct database requests(DDR) prompt

In OBIEE 11g direct database requests has a limitation that, it cannot be used for the prompts. To achieve that we can create an analysis with the DDR, create a pivot view and drag the required columns to the prompts area which will act as prompt for the report

Create direct database request

Enter connection pool and query 

Click on Results. Result looks like below, we need a prompt for this report.

Edit the report and add Pivot Table view

Click on the column properties which you want to add as prompt and select Duplicate Layer

Example:- JOB

Move JOB column to the Pivot Table Prompts Section

Click results now

OBIEE 11g Catalog Documentation

Reports can be built using catalog manager to list the objects and their details in the catalog.

Below are the steps

Open the catalog manager

Click on Tools -> Create Report

“Select type to report on” based on your requirement. If you want a report on dashboards the Select dashboard and click OK

Check Excel Format, if you want report to be saved in Excel and select the location to Save.

Click OK

 Excel is saved in your location. Open excel to see the report output.

OBIEE 11g Rpd Documentation

Repository Documentation utility  in Admin tool helps us to give the detail level of information about the rpd objects(Physical table, Physical Column, Logical Table….. )

Click on Tools -> Utilities and select Repository Documentation and click Execute

Save the csv file in your preferred location 

Open the saved csv file