How can we update 2 calculated columns in different data formats in one report? Columns: * Actual Revenue (Number Format) * Plan Revenue (Number Format) * Calculated column using Selection Steps: Variance (Number Format) = Actual Revenue – Plan Revenue * Calculated column using Selection Steps: Variance % (% format) = ((Actual Revenue – Plan Revenue)/ Plan Revenue)*100 I can change it to one data format (either number or %) , but cannot keep both formats at the same time. I have attached an image to show the existing dataset in the tables, and pivot table requirements. Thanks in advance,

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Custom data formats for 2 calculated columns in OBIEE Reports

User_YU3AO

How can we update 2 calculated columns in different data formats in one report?

Columns:

Actual Revenue (Number Format)
Plan Revenue (Number Format)
Calculated column using Selection Steps: Variance (Number Format) = Actual Revenue – Plan Revenue
Calculated column using Selection Steps: Variance % (% format) = ((Actual Revenue – Plan Revenue)/ Plan Revenue)*100

I can change it to one data format (either number or %) , but cannot keep both formats at the same time.

I have attached an image to show the existing dataset in the tables, and pivot table requirements.

Thanks in advance,

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

What does your analysis looks like?

Because if, as you say, you have 4 columns you can set the format on every single individual column.

An Excel screenshot isn't helpful as it doesn't say at all how your analysis is...

User_YU3AO

Hi, I have attached the existing table available in database (just Actual and Plan data), and calculated columns using selection steps (highlighted in yellow).

Calculated Columns using Selection Steps in OBIEE.JPG

User_YU3AO

I would like to keep Variance in number format, and Variance % in percentage format.

Gianni Ceresa

Ah, that's what I suspected

You don't really have 4 columns, you just have 2 columns: one is the measure (ok, you have 2, Sales & COGS, but doesn't change anything) and an attribute (dimension).

And you then select few values in the dimension (Actual, Plan, Variance, Variance %).

In this case as the measure is a single column it explain why the formal is shared between all the values (actual, plan, variance and variance %).

User_YU3AO

Thanks for the quick response. Is there any other way, I can calculate Variance and Variance % to get the desired data formats?

Gianni Ceresa

Well ... Not really ...

Because it is a single unique measure column the format is unique and shared for all the values of the column. Even playing with some weird formulas you can only detect when the attribute is Actual or Plan but never the Variance (so no way to make a difference between Variance and Variance %) because the CASE WHEN would be interpreted by the physical source (I would say Essbase in your case) while the selection steps calculations are done during the rendering by the presentation server.

By CSS I had some hope, but because you can't apply conditional formatting to calculated items (they just doesn't match any conditional formatting rule) you will not be able to make the difference between "Variance" and "Variance %". So it doesn't help you at all.

All in all ... I don't guess you can, because you are kind of asking to against the basic logic of the system (a given column has a single data type and format). And calculated elements added by selection steps can't be matched by conditional formatting.

Thomas Dodds

The ONLY way to 'mix' $, # % data formats in OBI is to reduce them all to text pre-formatted - and you can then throw away the math abilities. You also now have to build new structures to store the data in it's formatted state and model it through the RPD. OBIEE is not a pixel perfect reporting system.

Use BI Publisher for these scenarios OR chuck out requirements where mixing of the formats is asked for.