Customer Question/ Problem Description
Is it possible to define measures in report based on the values coming from prompt in OBIEE 11g?
Solution
One can define measure(s) in OBIEE report based on the prompt values by using Filter Expression. The steps involved are as follows
a. Define required presentation variable(s) within the dashboard prompt
b. Use Filter Function Area and filter_expressions by adding a single filer expression or building a complex filter statement using multiple expression by using presentation variables defined within the dashboard prompts
Filter Function Syntax
FILTER (Expr USING filter_expressions)
Where
- Expr is an expression that contains at least one measure column, for example, the expression "sales + 1" is allowed if "sales" is a measure column. The expression "productid" is not allowed if "productid" is a scalar attribute.
- Filter_expressions is a Boolean expression (evaluates to TRUE or FALSE) and does not contain any measure columns. The expression can not contain any nested queries
A sample example for complex filter expression is follows
FILTER ("Sales Measures".Dollars USING ((Periods."Year" = '1999') AND ((Markets.District = 'CINCINNATI DISTRICT") OR (Markets.District = 'DENVER DISTRICT'))))
Demo Example
I have defined a dashboard prompt with two column prompts ‘Brand’ and ‘Region’. I have also assigned presentation variables ‘brand’ and ‘office’ respectively to the column prompts ‘Brand’ and ‘Region’.
The report defined has following columns
The formulas for the 1-Revenue Brand and 1-Revenue Region by using Filter Function and Filter Expression is as follows, the presentation variables are used within the filter expression.
a. 1- Revenue Brand = FILTER ( "Base Facts"."1- Revenue" USING ("Products"."P4 Brand"= '@{brand}{BizTech}'))
b. 1- Revenue Region-FILTER( "Base Facts"."1- Revenue" USING ("Office Regions"."D50 Region"='@{office}{APAC}'))
While % Revenue by Brand and % Revenue by Office Region are calculated columns based on above definitions and 1-Revenue base fact column.
a- % Revenue by Brand - (FILTER( "Base Facts"."1- Revenue" USING ("Products"."P4 Brand"= '@{brand}{BizTech}'))/"Base Facts"."1- Revenue")*100.0
b- % Revenue by Office Region - (FILTER( "Base Facts"."1- Revenue" USING ("Office Regions"."D50 Region"='@{office}{APAC}'))/"Base Facts"."1- Revenue")*100.0
The dashboard prompt selection should drive the reporting measure, couple of selections for brand and office region on dashboard prompt and output reports are as follows
A. Prompt selection Brand =Biz Tech and Office Region = APAC
B. Prompt selection Brand =Biz Tech and Office Region = APAC
Limitation of Presentation Variable
Multiple values cannot be assigned to the presentation variable, in short while using presentation variables multiple selection for column prompt is not permissible.
