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
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.