OBIEE - Pivot Measures (The Filter Function)

Saw Object

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.

Obiee Pivot Measures

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

Syntax

FILTER ( Measure USING Base Line Col = Value )

The FILTER function requires:

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'))

Obiee Measure Pivot With Filter Function

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')) 

Obiee Logical Column Filter

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

Obiee Filter Final Result

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:
Obiee Bad Result Case When

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





Discover More
Bi Server Architecture With Client
OBIEE - Filter

get the Analysis XML, indent it and apply it again of you can try to suppress the
Obiee Ceim
OBIEE - Functions

The home page of OBIEE functions of the logical sql and from BI Server. See Logical SQL Reference
Obiee Filter On Internet
OBIEE - How to perform a pivot in a table view

This article explains how to obtain a pivot view in a table view through the creation of pivot measures. A pivot table is just the same table where : the pivot column has been suppressed for...
Obiee Pivot Ora 00937
OBIEE - ORA-00937 - Not a single-group group function

You may receive this error and below are some causes. On one percentage measure was the aggregation rule of answer set to default. By setting it on Average the problem was resolved. To get...



Share this page:
Follow us:
Task Runner