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 Calculations

To write correct formulae for calculating measures and members, you need to have an idea of the way RadarCube calculates cell content.
The calculations are based on formulae applied to the cell context. The cell context is its coordinates in a multi-dimensional cube:


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:

[Measures].[Sales]/[Measures].[Quantity]
This formula is calculated for every cell, according to its context.

The formula:
[Time].[Year].[1998]-[Time].[Year].[1997]
will calculate the difference between the values of the current measure for 1998 and 1997. This formula will make sense only for a calculated member in the "Year" context.

The formula:
([Measures].[Quantity], [Time].[Date].CurrentMember) -
([Measures].[Quantity], [Time].[Date].CurrentMember.PrevMember)

will calculate the difference in value between the current and the previous "Date" hierarchy members for the "Quantity" measure, displaying perse the increase of the sold products amount compared to the previous period.

The formula:
[Measures].[Sales] - (ParallelPeriod([Time].[Date].[Year], 1), [Measures].[Sales])
will calculate the difference between the current "Sales" measure value and its value for the same period of the previous year, i.e. the increase of the sold products cost compared to the same period of the previous year.

For example, for the "January 2008" cell it will display the difference between "Sales" measure values of January 2008 and January 2007.

The formula:
Sum(PeriodsToDate([Time].[Date].[Year]), [Measures].[Sales])
will sum up the progressive total of "Sales" measure values from the beginning of the year. That is, for a "September 2007" cell it will display the progressive total from January to September 2007.

The formula:
Avg(LastPeriods(3,[Time].[Date].CurrentMember), [Measures].[Sales])
displays the moving average of the "Sales" measure for the last three periods (i.e. three years for the "Years" level, three months for the "Months" level, etc.)

 

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 Guide

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[0], null, TCustomMemberPosition.cmpLast);
M.Expression = "[Sales Channel].[Reseller] - [Sales Channel].[Internet]";
(273 vote(s))
This article was helpful
This article was not helpful

Help Desk Software by Kayako Resolve