Cube views accept calculated columns or rows using the GetDataCell() function and references to cube view column or row names. This is also known as cube view column/row math.
These are based on the names given to a cube view column, for example "Col1" or "TimePeriods" or cube view row, for example “Row2” or “Accounts” by the creator of the cube view.
NOTE: Using the column or row names may be more useful than using member filters.
-
Cube view columns: Uses the GetDataCell function to perform math on columns.
-
Cube view rows: Uses the GetDataCell function to perform math on rows.
There are several variations on this method, depending on whether the expression refers to columns, rows, or a combination.
When naming a new calculation, do not include spaces or special characters that could make the column/row calculations read incorrectly.
Create a CVC/CVR Calculation
-
Add a row or column to insert the calculation.
-
Rename the row relevant to your calculation. In this case, the is CurrAssets.
-
Open the Member Filter Builder for the row or column where you want to insert a CVC/CVR calculation.
-
Delete what is in the Member Filter pane.
-
Navigate to the Samples tab.
-
Expand Column/Row Expressions.
-
Double-click Sum of Rows (CVR). This will populate the GetDataCell expression that you need.
-
Update the row names to add the rows together. Rename Row1 to Cash and Row2 to AR because Cash and AR are row header names used in the cube view.
-
Replace the name at the end of the expression to display Current Assets.
-
Click OK.
-
Click Save.
-
Run the cube view.
CVC: Cube View Column Calculations
The example below shows a Column Math example of the difference between columns (CVC).
The syntax is GetDataCell(CVC(SomeColumnName) - CVC(SomeOtherColumnName)):Name(Header Name)
The example of simple member math includes the :Name() function typically applied to a column in a cube view:
GetDataCell(CVC(Col1) - CVC(Col2)):Name(Variance)
GetDataCell(CVC(Col1) + 1):Name(Column Plus One)
GetDataCell(CVC(Col1) * (-1)):Name(Column with Sign Flipped)
CVR: Cube View Row Calculations
The example below shows a Row Math example of Sum of Rows (CVR).
The syntax is similar, but instead of CVC, a calculated row uses CVR for retrieving the value of a row in a formula.
An example of the syntax: GetDataCell(CVR(SomeRowName) + CVR(SomeOtherRowName)):Name(HeaderName)
NOTE: If a column name is numeric (for example, 500), single quotes are required when specifying the row name. Square brackets are allowed, but not required. For example: GetDataCell(CVR('123') - CVR(['4,567'])):Name(Difference)
When you need a column and row index: Col1 has a member filter of S#Actual, S#Budget, meaning it will return two columns. In this case, a variance between Actual and Budget scenarios can be shown like this: GetDataCell(CVC(Col1, 1) - CVC(Col1, 2)):Name(Variance).
Alternatives: GetDataCell(CVC(Col1, First) - CVC(Col1, 2)):Name(Variance) GetDataCell(CVC(Col1) - CVC(Col1, 2)):Name(Variance)
Column Math Example with Division and Other Advanced Functions
Member Expansions may require some more intricate formulas. This may require using indexes or a CVRC expression. Use functions like Divide to avoid divide by zero situations.
GetDataCell(Variance(CVR(Col1,2), CVR(Col1,1)):Name(Variance)GetDataCell(VariancePercent(CVR(Col1,2), CVR(Col1,1)):Name(Variance %) GetDataCell(Divide(CVC(Col3), CVC(Col2))):Name(Ratio) GetDataCell(BWDiff(CVC(Col1), CVC(Col2))):Name(BetterWorse Difference)GetDataCell(BWPercent(CVC(Col1), CVC(Col2))):Name(BetterWorse %)
