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: [Measures].[SalesAmount]/[Measures].[SalesCount] 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: ![]() or ![]() 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: ![]() Understanding CalculationsTo 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:
The formula: The formula: The formula: For example, for the "January 2008" cell it will display the difference between "Sales" measure values of January 2008 and January 2007. The formula: The formula:
A lot of different examples of using MDX functions in expressions can be found in the "MDX Essentials Series" A manual for MDX functions can be found in the MSDN library. Programmer's GuideTo 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 runtimeTMeasure m = TOLAPGrid1.Measures.AddCalculatedMeasure("Calculated"); m.Expression = "[Measures].[Sales Amount] / [Measures].[Order Count]"; Creating a calculated member in runtimeTHierarchy h = TOLAPGrid1.Dimensions.FindHierarchyByDisplayName("Sales Channel"); TCalculatedMember M = h.CreateCalculatedMember("Reseller - Internet", "", h.Levels[0], null, TCustomMemberPosition.cmpLast); M.Expression = "[Sales Channel].[Reseller] - [Sales Channel].[Internet]"; | |
|