OBIEE - Pivot Measures (The Filter Function)

About

Indicator, description of Measures are generally the sign that you need to pivot your measure (i.e. to create a Pivot Measure)

The revenue is booked. Revenue is the measure and booked is a description and then a dimension attribute where a filter must apply.

To create a pivot measure, you use the FILTER function of OBIEE (and not the case function).

Articles Related

Syntax

FILTER ( Measure USING Base Line Col = Value )

The FILTER function requires:

at least one measure attribute in its first argument
and at least one (baseline|dimension) attribute in the predicate

Pivot View 10G Limitations

You can also pivot your measure by an attribute dimension with the pivot view but you have limitations

in 10g

. The following actions are only available in a table view :

A cross column conditional formatting. It's not possible to set a red background of a column based on the value of an other column in a pivot view.
A write back because you must use the table view to perform this action.
A sort on a measure column
A download of the data in a pivot format.
An header navigation

How to use the filter function

In an Analyse

In an Analyse of with a logical sql:

FILTER("Base Facts"."1- Revenue" USING ("Orders"."R1  Order Status" = '1-Booked'))

For a complete example, you can check this article: OBIEE - How to perform a pivot in a table view

In a repository measure

As as measure in the repository with the following statement

FILTER( "1 - Sample App"."F0 Revenue Base Measures"."1- Revenue"  
USING ("1 - Sample App"."D7 Orders (Facts Attributes)"."R1  Order Status"  = '1-Booked'))

Result

Physical SQL Generated

physical sql

WITH SAWITH0 AS
  (SELECT SUM(T42433.Revenue) AS c1,
    T42406.PER_NAME_YEAR      AS c2
  FROM SAMP_TIME_QTR_D T42406
    /* D03 Time Quarter Grain */
    ,
    SAMP_REVENUE_F T42433
    /* F10 Billed Rev. */
  LEFT OUTER JOIN SAMP_ORDER_COMMENTS T55131
    /* D71 Order Comments */
  ON T42433.Order_Number  = T55131.Order_Number
  WHERE ( T42406.QTR_KEY  = T42433.Bill_Qtr_Key
  AND T42433.Order_Status = '1-Booked' )  --- The predicate
  GROUP BY T42406.PER_NAME_YEAR
  )
SELECT DISTINCT 0 AS c1, D1.c2 AS c2, D1.c1 AS c3 FROM SAWITH0 D1 ORDER BY c2

Example of Report

Support

With a case when

Does not work. You will get bad result if you don't have the column of the predicate in your logical sql (or report).

CASE
WHEN "Orders"."R1  Order Status" = '1-Booked' THEN
  "Base Facts"."1- Revenue"
END

One of the behaviour of the case statement is that it adds to the query grain the column of the predicate and the ELSE part of the case statement is not filtered or suppressed (then you will get NULL values).

The SQL generated:

WITH SAWITH0 AS
  (SELECT SUM(T42433.Revenue) AS c1,
    T42406.PER_NAME_YEAR      AS c3,
    T42433.Order_Status       AS c4  
  FROM SAMP_TIME_QTR_D T42406
    /* D03 Time Quarter Grain */
    ,
    SAMP_REVENUE_F T42433
    /* F10 Billed Rev. */
  LEFT OUTER JOIN SAMP_ORDER_COMMENTS T55131
    /* D71 Order Comments */
  ON T42433.Order_Number = T55131.Order_Number
  WHERE ( T42406.QTR_KEY = T42433.Bill_Qtr_Key )
  GROUP BY T42406.PER_NAME_YEAR,
    T42433.Order_Status
  )
-- No Sum to suppress the Order Status grain but a distinct
SELECT DISTINCT 0 AS c1,
  D1.c3           AS c2,
  CASE
    WHEN D1.c4 = '1-Booked'
    THEN D1.c1
  END AS c3
FROM SAWITH0 D1
ORDER BY c2

The data set result:

0	2008	1065974,6
0	2008	
0	2009	988843,33
0	2009	
0	2010	1314330,08
0	2010

The report result:

Function FILTER requires at least one measure attribute

[nQSError: 10058] A general error has occurred. 
[nQSError: 22038] Function FILTER requires at least one measure attribute in its first argument.

Function FILTER requires at least one measure attribute in its first argument.

Documentation / Reference

Made with the OBIEE sample app without the ROWNUM feature enabled.