Calculated measures and members in RadarCube
Posted by - NA - on 21 February 2011 12:41 PM
RadarCube supports creating calculated measures and members by end-users with the in-built formula editor. Both MSAS and Direct versions of RadarCube use the standard MDX syntax and the standard set of MDX functions for creating calculated expressions. For example, a "SalesAverage" calculated measure may be created with the following expression:
Calling the Formula Editor
Formula Editor window
You can create a calculated measure either through the context menu of the "Measures" node in the CubeStructureTree or the context menu of the "Measures" cell in the current OLAP slice:
To create a calculated member, use the context menu of the cell with the header of the level, where you mean to place the new member:
You can edit and delete a calculated measure or member through the context menu of the cell with its name:
To write correct formulae for calculating measures and members, you need to have an idea of the way RadarCube calculates cell content.
The context of the selected cell: "Quantity" measure, as well as the members of the "Categories" and "Year" levels named "Dairy Products" and "1996", respectively.
MDX-formulas for calculating measures and members are applied to this context, modifying it if need be.
Let's analyze a formula for the "SalesAverage" measure:
For example, for the "January 2008" cell it will display the difference between "Sales" measure values of January 2008 and January 2007.
To create or remove calculated measures, use the TOLAPGrid.Measures.AddCalculatedMeasure and TOLAPGrid.Measures.DeleteCalculatedMeasure methods accordingly. The MDX-formula for calculating the measure must be assigned to its Expression property right after its creation. For example:
Creating a calculated measure in runtime
TMeasure m = TOLAPGrid1.Measures.AddCalculatedMeasure("Calculated"); m.Expression = "[Measures].[Sales Amount] / [Measures].[Order Count]";
Creating a calculated member in runtime
THierarchy h = TOLAPGrid1.Dimensions.FindHierarchyByDisplayName("Sales Channel"); TCalculatedMember M = h.CreateCalculatedMember("Reseller - Internet", "", h.Levels, null, TCustomMemberPosition.cmpLast); M.Expression = "[Sales Channel].[Reseller] - [Sales Channel].[Internet]";