Cube Views

Cube View Calculations

Cube Views support three calculation methods for adding computed rows and columns — GetDataCell expressions, column/row math (CVC/CVR), and dynamic member formulas. Each method has different capabilities and performance characteristics. This guide covers all three, plus Custom Member Lists for dynamic member generation from Business Rules.

Choosing a Calculation Method

MethodComplexityPerformanceUse Case
GetDataCellSimple expressionsGood — evaluates per cellVariance, percentage, cross-dimension lookups
CVC/CVR mathReferences other columns/rowsGood — evaluates after data retrievalTotals, ratios between existing columns/rows
Dynamic Member FormulasFull formula engineCan be slower with sparse suppressionComplex runtime calculations not stored in the cube
Start with the simplest method that meets your requirement.

GetDataCell Expressions

GetDataCell is a function available in the Member Filter Builder that retrieves a value from a specific cube intersection. Use it to create calculated rows or columns that pull data from different dimension members than the row/column would normally show.

Basic Syntax

plaintext
1GetDataCell("A#Variance"):Name("Act vs Bud Variance")
2GetDataCell("A#Revenue:S#Actual") - GetDataCell("A#Revenue:S#Budget"):Name("Revenue Var")
The :Name("label") suffix sets the display label for the calculated row or column.

Common GetDataCell Functions

The Member Filter Builder's Samples tab provides pre-built GetDataCell patterns:
FunctionDescription
VarianceActual minus Budget (or comparable minus reference)
VariancePercentVariance as a percentage of the reference value
BetterOrWorseVariance with sign adjusted based on account type (revenue positive = better, expense negative = better)

GetDataCell with Business Rule Call

For complex calculations that require logic beyond simple arithmetic, call a Finance Business Rule from a GetDataCell expression:
plaintext
1GetDataCell("BR#[BRName=MyCalcRule,FunctionName=GetCustomMetric,Field1=Param1]"):Name("Custom Metric")
The Business Rule receives the current cell's POV context and the specified parameters, executes the calculation, and returns a value.
ℹ️Info
GetDataCell Business Rule calls execute once per cell. For a Cube View with 100 rows and 5 columns, a GetDataCell BR call in one column executes 100 times. Keep the Business Rule logic lightweight to avoid performance issues.

Column and Row Math (CVC/CVR)

Column math (CVC) and row math (CVR) reference other columns or rows by name and perform arithmetic on their values. This is the simplest way to add totals, variances, or ratios that combine existing columns or rows.

CVC Syntax

plaintext
1GetDataCell(CVC(Actual) - CVC(Budget)):Name(Variance)
2GetDataCell(Divide(CVC(Variance), CVC(Budget))):Name(Variance%)
3GetDataCell(CVC(Q1) + CVC(Q2) + CVC(Q3) + CVC(Q4)):Name(FullYear)
The strings Actual, Budget, Q1, etc. inside CVC() are the names of other columns in the same Cube View. This is why naming your columns descriptively matters — CVC(Actual) is self-documenting.

Hiding Source Columns

Often you want to show only the calculated result, not the source columns. Set the source column's isColumnVisible property to CVMathOnly:
  • The column is hidden from the user
  • The column's data is still retrieved and available for CVC expressions
  • The calculated column that references it displays normally
💡Tip
The CVMathOnly visibility setting is the key to clean calculated reports. Retrieve source data in hidden columns, then reference them in visible calculated columns. Users see only the final result.

CVR (Row Math)

Row math works identically to column math but references row names instead:
plaintext
1GetDataCell(CVR(Revenue) - CVR(COGS)):Name(GrossProfit)
2GetDataCell(Divide(CVR(GrossProfit), CVR(Revenue))):Name(GrossMargin%)

Dynamic Member Formulas

Dynamic member formulas are calculated at runtime by the cube engine — the values are not stored in the cube. When a Cube View includes a member with a dynamic formula, the engine evaluates the formula on each retrieval.
Key characteristics:
  • Calculated at runtime, not stored
  • Defined on the member in Dimension Maintenance, not in the Cube View
  • Available wherever the member is used — Cube Views, Quick Views, Business Rules
  • Performance consideration: dynamic formulas combined with sparse row suppression can cause slower evaluation because the engine must calculate values before determining whether to suppress the row
⚠️Warning
Dynamic member formulas with sparse row suppression can create a performance conflict. Sparse suppression skips rows with no data during retrieval, but dynamic formulas need to be evaluated to determine if the row has data. If performance is a concern, test with and without sparse suppression enabled.

Custom Member Lists

Custom Member Lists allow a Finance Business Rule to dynamically generate the list of members that appear in a Cube View row or column. Instead of hard-coding members in the member filter, the Cube View calls a Business Rule that returns the list at runtime.

Syntax

plaintext
1E#Root.CustomMemberList(BRName=MyListRule, MemberListName=ActiveEntities)
The Business Rule named MyListRule must implement a function that returns a member list named ActiveEntities. The engine calls this function at runtime, and the returned members appear in the Cube View.

Condensed Syntax

For shorter expressions:
plaintext
1E#Root.CML(BRName=MyListRule, MLN=ActiveEntities)

CustomMemberListWithParameters

Pass additional context to the Business Rule using parameters:
plaintext
1E#Root.CustomMemberListWithParameters(BRName=MyListRule, MemberListName=FilteredEntities, Param1=Value1, Param2=Value2)
The Business Rule receives these parameters and can use them to filter or modify the returned member list.

Use Cases

  • Dynamic entity lists — Return only entities that have data in the current period
  • User-specific views — Return members based on the current user's security profile
  • Conditional members — Include or exclude members based on scenario, time period, or other POV context
  • Cross-dimensional filtering — Return accounts that are relevant to the selected entity's industry
ℹ️Info
Custom Member Lists execute once per Cube View retrieval, not once per cell. This makes them more efficient than GetDataCell Business Rule calls for generating dynamic row or column structures.

When to Use Each Method

ScenarioRecommended Method
Variance between two scenarios in adjacent columnsCVC math
Percentage calculation from existing rowsCVR math
Cross-dimension lookup (different member than row/column default)GetDataCell expression
Complex calculation with conditional logicGetDataCell with Business Rule
Dynamic row/column list based on data or securityCustom Member List
Calculation that should be available everywhere (not just this Cube View)Dynamic Member Formula

Related Content