Source: Migrated from Champions
I am trying to create a report/cubeview for our regions. But how do I get the dynamic calculation inserted into my CV? (Highlighted Yellow) Do I need to approach it differently?
Here is an example of what I am trying to create -
There isn’t much information here so answers will be a bit generic. There is likely a metadata way to get to your Profit Margin. Questions for that include things like what type of chart of accounts do you have (functional vs. natural) or is there a UD involved (department or line of business, etc.)? Alternatively both rows could have a GetDataCell expressions (see samples in the Member Filter Builder dialog). These work with any POV. Copied straight out of the samples: GetDataCell(S#Scenario1-S#Scenario2):Name(Difference) and GetDataCell(VariancePercent(S#Scenario1,S#Scenario2)):Name(Var %)
You can also use the Column/Row Expressions. Those are definitely more difficult if member expansions are in play.
Another way to do this would be to create a dynamic calculation in a member formula. Most people put those in UD8, but they can live in any dimension. It’s very similar to the GetDataCell method.
The way I did it-
Create another Account hierarchy for all the calculated accounts you want, in this case Rerent profit margin% etc
on each of these accounts, you can set the formula you want it to calculate. Use these in the cubeview member filters.
Ofcourse, like Andy mentioned, the formula would again call the GetDataCell function within it.
And if it is a cubeview and you don't want to create the hierarchy members, you can always use the Member filter and give your formula to getdatacell in the cubeview.