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. SELECT NON EMPTY {{<Level Unique Name>.ALLMEMBERS}} ON 0 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 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:
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)"); 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. ![]() | |||||||||
|