Dynamic Column heading in an OBIEE report with Essbase as datasource

Question

Hi ,

I have an analysis developed in  OBIEE 11g , and the subject area is essbase.

My report  has members of the Scenario dimension on the column Actual ,Budget, Forecast1 , Budget vs Actual,%change, Forecast vs Actual , %change

In order to Achieve this , i have from Selection Steps selected the Scenarios :Actual, Budget and Forecast1

for calculating the variance, i created a calculated column when i edit the Pivot.

Now the ask is that ,  the Forecast in the column must be user selected (Forecas1 or Forecast2...Forecast12).

Based on the Forecast Selected by the User the variance and Change% needs to be updated.

I have tried to do this using Presentation variable , Union Report  but i'm unable to preserve the  Above Column Format

Can someone please help .Can this be achieved without modifying the RPD ?

Thanks

Sam

3266016 · Answer

Note:In the calculated item for Forecast Vs Actual i used   @{Scenario} - Actual  , then the value is not calculating for default value Forecast 1

When i hardcode the Forecast 1 in the calculated Item  the result is right

3266016 · Answer

Hello Gianni,

Thanks for responding , Based on your response i went ahead and did the following

Created a presentation variable using the Dashboard prompt "Scenario"( value in Drop down Forecast 1  thru Forecast 12)   with default value Forecast 1

In the Criteria I created nested filters

Scenario  equal to @{Scenario}{Forecast 1}

OR Scenario equal to Actual

OR Scneario equal to Budget

Doing this i am able to preserve the format and display   "Actual"  " Budget"  "Forecast 1" in the column followed  by Calculated items  "Budget vs Actual','%change', 'Forecast vs Actual' , 'Fcst %change'

Now when i run my analysis , My results are as expected for the Default Forecast ="Forecast 1"

when i change My prompt Forecast 1 to Forecast 5   , The format is retained

Now displays   "Actual"  " Budget"  "Forecast 5"   in the column followed  by Calculated items "Budget vs Actual','%change', 'Forecast vs Actual' , 'Fcst %change'

But the resulting value  for the calculated items Forecast vs Actual , Fcst %change   does not recognise the data in Forecast 5 and result is just data in -(Actual).

In the calculated item for Forecast Vs Actual i used   @{Scenario} - Actual

Can you please let me know if im on the right track or should i be approching teh calculated item any differently.

Thanks again for your time

Regds

Sam

Gianni Ceresa · Answer

Hi,

In theory you can do it with a presentation variable defining the forecast. If you change the formula of the forecast column to use the variable to select the right forecast column everything else must work without changing anything else.

Not sure why you are trying to do a UNION report here ....