Best way to make a cubeview that compares a static scenario to a selection of scenarios

[JacobH]

Hi all -

I am creating a cubeview that seems basic at first glance, but have ran into some walls as I am still new to using cube views on my own.

What I have is an entity, net sales, profit, profit margin, months, and a changing scenario.

I want to see net sales, profit, and profit margin for the current year. This part is simple enough and will not move.

However, I want to compare these same metrics to various other scenarios, one scenario at a time. I want to be able to select something like prior year actuals, budget, or any forecast and see the figures come in for it in the second/lower half of this cube so I can then make a comparison between current year and whatever selected scenario I have.

Is the best way to do this to create a parameter for the scenario and then write in that parameter name in the necessary rows for the scenario?

Additionally, if I wanted to create a better/worse row, would I be able to easily do that even though one of the two scenarios could change at any given moment? Something like "Current year net sales - net sales of selected scenario = xx"?

Just trying to figure out the best way to make part of this cube view variable and being able to run a comparison against it with current year data.

[Omkareshwar]

Hi Jacob,

The best way to go about this is by having a parameter where you can give selection for the scenario you want to compare with.

As for the better worse calculation use this 

by using these as your scenario is changing the values will get automatically updated based on the selected scenario.

Thanks, Omkareshwar

Archetype Consulting

[dimitriosc]

Hi JacobH, 

In addition to Omkareshwar's recommendation you could also leverage the UD8 Dimension to create dynamic retrieves of data based on your requirements for Reporting purposes. 

For example, having a UD8 member called "PY" with the below dynamic formula would retrieve your PY data. 

----------------

If (Not ViewMember.IsAnnotationTypeViewId(api.Pov.View.MemberId)) Then
Return api.Data.GetDataCell("U8#None:T#" & api.Time.GetNameFromId(api.Time.GetPriorYearPeriodId()).ToString )
End If

------------------

Similarly, a UD8 member called "VariancePY" with the below piece of code would generate your variance between current and last year periods. 

-------------------

If (Not ViewMember.IsAnnotationTypeViewId(api.Pov.View.MemberId)) Then

Return api.Data.GetDataCell("U8#None - T#POVPrior12:U8#None")

End If

Return Nothing

---------------

Experimenting with the use of UD8 members for reporting and relevant scripts can be fairly powerful and help you create dynamic reporting outputs.

I hope this helps!

Dimitrios

PRYZM