Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Custom data formats for 2 calculated columns in OBIEE Reports

Received Response
21
Views
7
Comments
User_YU3AO
User_YU3AO Rank 1 - Community Starter
edited Aug 13, 2024 5:55PM in Oracle Analytics Cloud and Server

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,

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    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
    User_YU3AO Rank 1 - Community Starter

    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
    User_YU3AO Rank 1 - Community Starter

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

  • Gianni Ceresa
    Gianni Ceresa Mod

    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
    User_YU3AO Rank 1 - Community Starter

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

  • Gianni Ceresa
    Gianni Ceresa Mod

    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
    Thomas Dodds Rank 8 - Analytics Strategist

    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.