Dynamic Reports in OBIEE 11g

Dynamic Report in OBIEE11g

We had a requirement to generate* reports dynamically based on the values selected in the prompt.

Example:

Reports generated dynamically based on the values selected in the prompt: 

Reports : There are two reports Yearly Sales Report and Weekly Sales Report to be displayed in a single dashboard page.

Prompt :  The prompt has values like Year and Week.

By default dashboard page need to display only the yearly sales report and if User selects value ‘Week’ in the prompt then it should display only weekly sales report.

This dynamic report generation can be implemented in two ways.

Method 1:

A  variable prompt is created with two user-defined values : Week and Year as shown below:

Presentation variable Pres_Var is created which holds the value selected in the prompt. It can hold more than one values. And report is filtered based on the values in Pres_Var.

/

Select prompt is defined with two custom values Week and Year. 

Weekly and Yearly sales report  is created.

Reports and  prompt is placed in the dashboard as shown below:-

Apart from this, an intermediate report(Yearly_Inter_Report) is created for Yearly Sales report. 

Column Formula for Year column is changed to ‘Year’ through edit formula.

A filter is applied to year column with Presentation Variable (Pres_Var}

The Pres_Var  will hold the value that is selected  by the user in the prompt. It can be either (Week/Year). If it is Year then a filter will be applied in the inter report where ‘Year’ = Year which will return a record with Value Year.

Similar inter-report is created for week as well.

Filter on ‘Week’ should be applied only after changing the edit formula. It should have ‘Week’. Only then this filter evaluates to true.

In section of Yearly sales report a condition will be applied on the yearly inter report

Condition is applied on the intermediate report (Yearly Inter report)

if the analysis returns atleast one row or more than one row, then the condition evaluates to true.

Here if user selects Year, then @{Pres_Var}  holds the value (Year) which would return 1 row. Then as per the condition, the Yearly report will be displayed.

Similar to this, Weekly inter report is a created and condition is applied on  the weekly sales report section with this weekly inter report.

In dashboard, by default  Yearly Sales report will be displayed since the prompt is defaulted to Year.

When user selects week, then weekly sales report will be displayed.

Method 2 : (Without multiple inter report)

A prompt is created with two pre-defined values : Week and Year.

Two reports Weekly and Yearly sales report is created .

Prompts and reports placed in dashboard.

One Yearly Inter report can be created, whose formula can be modified as given below:

Column Formula can be given as Case when ‘@{Pres_Var} = ‘Year’  Then  TableName.ColumnName  ELSE ‘None’ END

Therefore When Pres_Var = ‘Year’ Then it might return multiple records else it will return one record with Value ‘None’

Therefore Conditions can be applied in sections of weekly and yearly sales report with one single Intermediate report instead of creating multiple inter-reports for generating dynamic reports.

Condition in Section of Yearly Sales Report

Condition in Section of Weekly Sales Report

Therefore  If the Yearly Inter-report results in one row then weekly sales report is generated. If it retrieves more than one row then yearly sales report is displayed.