Knowledgebase: RadarCube ASP.NET MSAS
Creating custom Time Intelligence
Posted by - NA - on 16 December 2009 02:54 PM

RadarCube for MSAS supports the Time Intelligence functions available through the context menu of data cells, displaying the values of the time hierarchies' members.

Together with the standard Time Intelligence functions, implemented in RadarCube, a programmer can define those of his own.

To do that, you need to write an MDX-formula of calculating Time Intelligence and put it into the constructor of the TIntelligence class. Let's take a look at the example of how it should be done.

For instance, we want to create Time Intelligence that will display the value of the previous member (previous year, previous quarter, previous month etc.) on the base of the Adventure Works Cube.

The MDX-expression for calculating Time Intelligence should be put into this pattern:

WITH MEMBER [Measures].[X] AS <Time intelligence expression>
FROM <Cube name>
WHERE ([Measures].[X])

So, for the "Year" level of the "[Date].[Calendar]" hierarchy and the "Sales Amount" measure we'll have the following formula:

WITH MEMBER [Measures].[X] AS 
COALESCEEMPTY(SUM({PARALLELPERIOD([Date].[Calendar].[Calendar Year], 1,[Date].[Calendar].CURRENTMEMBER)}, [Measures].[Sales Amount]), 0) SELECT NON EMPTY {{[Date].[Calendar].[Calendar Year].ALLMEMBERS}} ON 0 FROM [Adventure Works] WHERE ([Measures].[X])

Now, let's take a close look at the parameters of the Time Intelligence object constructor:

public TIntelligence(THierarchy parent, string displayName, string expression)

The first two parameters - "parent" and "displayName" - are, respectively, the parent hierarchy and the name for the Time Intelligence item that will show up in the context menu. The third parameter - "expression" - is the generalized formula taken from the MDX-expression that we've created earlier. The following substitutions in the formula will take place:

Formula value "Expression" Parameter Value
<hierarchy member expression> {0}
<unique measure name>> {1}
<aggregating function> {2}

In our case, it will be:

<hierarchy member expression>: [Date].[Calendar].CURRENTMEMBER
< unique measure name >: [Measures].[Sales Amount]
< aggregating function >: SUM

So, in our case the generalized formula will look like this:

COALESCEEMPTY({2}({PARALLELPERIOD([Date].[Calendar].[Calendar Year], 1,{0})},{1}), 0)

Then this formula may be generalized for any level of the time-hierarchy:

COALESCEEMPTY({2}({PARALLELPERIOD(<Level Unique Name>, 1,{0})},{1}), 0)

Now, the only thing left to be done here is creating an instance of the TIntelligence object in the TOLAPGrid.OnInitHierarchy event handler:

private void tolapGrid1_OnInitHierarchy(object Sender, TEventInitHierarchyArgs EventArgs)
    if (EventArgs.Hierarchy.DisplayName == "Date.Calendar")
        if (EventArgs.Hierarchy.Intelligence.Count > 0)
            foreach (TLevel l in EventArgs.Hierarchy.Levels)
                TIntelligence ti = new TIntelligence(EventArgs.Hierarchy,
                "Previous " + l.DisplayName,
                "COALESCEEMPTY({2}({PARALLELPERIOD(" + 
                    l.UniqueName + ",1,{0})}, {1}), 0)");
ti.IntelligenceGroup ="Show previous period..."; EventArgs.Hierarchy.Intelligence.Add(ti); } } } }

In this example we've used the TIntelligence.IntelligenceGroup property for grouping Time Intelligence items in the context menu. Also you can format displayed values by handling the TOLAPGrid.OnDrawCell event.
All in all, we get the following result:

(330 vote(s))
This article was helpful
This article was not helpful

Help Desk Software by Kayako Resolve