Sunday, 15 April 2012

OBIEE 11g - Measures Based on Selected Dashboard Prompts

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.

2 comments:

  1. Hi,

    i have tried it against ORACLE Database as a source and it worked as expected but it did not return any results when I source Essbase.

    Do you know if anything needs to be done for Essbase to make it work?

    ReplyDelete
  2. Hi,

    one question, how could it work if you require to put the following on specific value:

    Region = APAC and region like ('E%') - we want apac and all regions that begins with E, let suppose that we have EMEA1,EMEA2,EMEA3... to EMEA10000 (more than 10000 regions that begin with e), how do you write on specific value that ??? If you use sql results then we will get all this "emeas" but when you run a report you see a IN clause that is limited and cannot include all the records, is there any why to put a specific value plus a LIKE -pattern-???

    ReplyDelete