MODLRDirect References
When to use: When making simple links/calculations between cells
Description: The equivalent of linking to different cells in standard spreadsheets. Since axis items are named elements we use a combination of one or more elements names (instead of row or column references such as A1) to refer to a cell that is against those elements relative to the cell being calculated.
Simple Syntax
Used when changing from the current cell to another by adjusting the reference to a specific element from one of the dimensions:
vb
["Element Name"]["Element Name"]or
vb
["Element Name A", "Element Name B"]["Element Name A", "Element Name B"]Assuming that "Element Name A" is from one dimension and "Element Name B" is from another dimension.
Full Syntax
vb
["Dimension Name:Hierarchy Name»Element Name"]["Dimension Name:Hierarchy Name»Element Name"]Dimension Name and Hierarchy Name are optional and can be omitted. When the specified element name exists in two or more dimensions within the cube the dimension prefix is required to prevent ambiguity.
The Hierarchy Name is used when referencing a specific parent element as parent elements in hierarchies do not need to be unique within a single dimension.
The full syntax example can be used to refer to a target cell which spans more than one dimensional change like the below example:
vb
["Dimension Name A:Hierarchy Name A»Element Name A", "Dimension Name B:Hierarchy Name B»Element Name B"]["Dimension Name A:Hierarchy Name A»Element Name A", "Dimension Name B:Hierarchy Name B»Element Name B"]Relative Expressions
Instead of specifying an element by name, we can instead use a relative expression. These expressions will be unlikely to work without the use of full syntax.
@Parent can be used to reference the parent of an element in the cell being calculated, within a specified dimension and hierarchy.
Simple Example
Restrict to Revenue
vb
["Units"] * ["Sell Price"]["Units"] * ["Sell Price"]Complex Example
In a business Product B sales is a fixed percentage of Product A sales. So we calculate Product B to be based on Product A and a percent which the user enters against Product B.
Restrict to "Units", Restrict to "Product B"
vb
["Units", "Product A"] * ["Upsell %"]["Units", "Product A"] * ["Upsell %"]Since this formula applies to only Product B, it will take the "Upsell %" entered against "Product B" and multiply it by the number of "units" sold for "Product A"