Conditional formatting in pivot view having analytic functions

Sravan Gereddy

Hey guys!

We have a requirement to perform arithmetic operations on values of different rows of a column and then display in a pivot view with some conditional formatting. Here is an illustration of the requirement.

There are 3 columns, Plant, Mineral and Value. Each plant has a set of minerals and each mineral has a value. Each plant can have any number of minerals, out of which M1 is mandatory. The requirement is to report a column(column 4 below), whose value is the value associated to the mineral -(minus) value of mineral M1 of that plant.

So, to fulfill this requirement, I'm using the MAX() analytic function. So, my column formula is as follows:

"Subject Area"."Value" - evaluate(        'MAX(%1) OVER (PARTITION BY %2 )',        CASE            WHEN upper("Subject Area"."Mineral" ) = 'M1' THEN "Subject Area"."Value"        END,        "Subject Area"."Mineral"     )

With this, I am able to achieve the first half of the requirement. The report's output displayed in a pivot view by pivoting the Mineral values. Illustrated as below( for plant A):

Now, we have a list of limit values provided for each mineral, and if in case the value of the mineral falls off the range, the cell has to  be highlighted to red. To achieve this, I am using a hidden column with a simple case statement.

Assume, for Mineral M6, the limit is between 4-6, the case statement goes as follows :

CASE        WHEN            "Subject Area"."Mineral" = 'M6'        AND            "Subject Area"."Value" - evaluate(                'MAX(%1) OVER (PARTITION BY %2 )',                CASE                    WHEN upper("Subject Area"."Mineral") = 'M1' THEN "Subject Area"."Value"                END,                "Subject Area"."Mineral"            ) BETWEEN 4 AND 5        THEN 0        ELSE            1    END

                                  

I'm using this case to generate a value 1, if it falls off range else 0. Using this value, I'm applying conditional formatting for the cells which return value 1.

In OBI, what I observed is that, conditional formatting is only applied when the hidden column is a part of the view columns(shouldn't be in excluded list). While using a pivot view with the conditional formatted column, data splits across rows like below:

The problem is, we need a single row for each plant, but due to the conditional formatted column, data splits across rows. So, I tried using MAX() in column formula, just to treat the column as a measure so that OBI displays a single record, but that didn't help as windowing functions cannot be used with any other group function( MAX(some_analytic_func)).

So, is there any way at OBI level to suppress the rows into 1? Any help is highly appreciated.

Thanks in advance.

Sravan

Gianni Ceresa

Hi,

I quickly build something similar on a 12c and it just works fine, no double rows etc.

First: get rid of your EVALUATE: you do not need it, pure OBIEE formula can perfectly make the same job.

Your "value" column is a measure? Or are you making an analysis only based on dimensional attributed?

Sravan Gereddy

Thanks for the response Gianni.

So, regarding get rid of your EVALUATE - how do I get rid of EVALUATE? Is there any way I can use an analytic function without EVALUATE or any OBI function which performs the same task that I don't know of? Please explain.

"value" column is a measure? - I'have both the columns in presentation layer, one as measure aggregated as SUM and the other as a dimensional attribute. I'm using dimensional attribute in this case.