KB4755: How to use a metric as a subtotal for another metric in MicroStrategy Intelliegence Server SDK 9.x

Strategy Desktop does not provide an interface to achieve this; therefore, custom code using the Strategy Intelliegence Server SDK needs to be developed. This document provides an example on how to do so in Visual Basic (VB).
 

 
Create the following metrics (the '<' and '>' symbols denote the dimensionality of a metric):

M1: SUM(F1)
M2: SUM(F2)
M3: SUM(F2) <A2>
M4: SUM(F2) <A1>
S1_A2: SUM(M1 * M2) / SUM(M3)
S2_A1: SUM(M1 * M2) / SUM(M4)

Note that S2_A2 is used as the subtotal for M2 at the A2 level and S2_A1 is used as the subtotal for M2 at the A1 level.
 
Sample of code in VB using the Strategy SDK is shown below. This procedure must have a Strategy 9.x project registered as a Project Source. In this example, the source name 'METRIC CHANGE PROJECT SOURCE' that holds the 'METRIC CHANGE' project, is being used.

Private Sub Change_Metric ()    
    Dim strErrMsg 
    Screen.MousePointer = vbHourglass 
    'Adjust the following to your environment: 
    Const DataSourceName As String = "METRIC CHANGE PROJECT SOURCE" 
    Const Login As String = "Administrator" 
    Const Password As String = "" 
    Const ProjectName As String = "METRIC CHANGE" 
    Const Metric_FullName As String = "S2_A1" 
    
    Dim DataSource As New DSSDataSource 
    Dim DataSourceList As DSSDataSourceEnumerator 
    
    Set DataSourceList = New DSSDataSourceEnumerator 
    DataSourceList.LoadFromRegistry 
    
    Set DataSource = DataSourceList.Item(DataSourceName) 
    DataSource.AuthMode = DssAuthStandard 
    DataSource.Login = Login 
    DataSource.Passwd = Password 
    DataSource.Init 
    
    Dim ProjectList As DSSDataSourceEnumerator 
    Dim Project As DSSDataSource 
    
    Set ProjectList = DataSource.Enumerator 
    'The following line will set the project to be METRIC CHANGE_1 
    Set Project = ProjectList.Item(ProjectName & "_1") 
    'MsgBox Project.Name 
    Project.Init 
    
    Dim Session As IDSSSession 
    Dim ObjectSource As IDSSSource 
    Set Session = Project.CreateSession 
    Set ObjectSource = Session.Component(DssRoleObjectSource) 
    
    Dim SearchFolder As IDSSFolder 
    Dim SearchObj As IDSSSearch 
    Set SearchObj = ObjectSource.NewObject(DssTypeSearch) 
    SearchObj.NamePattern = Metric_FullName 
    SearchObj.Types.Add (DssTypeMetric) 
    Set SearchFolder = SearchObj.Execute 
    
    Dim Metric As IDSSMetric 
    Set Metric = SearchFolder.Item(1) 
    
    'MsgBox Metric.Info.Name 
    
    'Change metric to subtotal: 
    Metric.MetricType = DssMetricSubtotal 
    'Get the folder of objects embedded in this metric: 
    Dim Metric_Embedded As IDSSEmbeddedFolder 
    'Objects that exist as embedded in this metric: 
    Set Metric_Embedded = Metric.Info.Embedded 
    'Metric_02_Embedded(IDSSFolder) contains 1 metric, 2 agg metrics
    
    
    'Get the expression tree of the first aggmetric 
    Dim AggMetric As IDSSObjectInfo 
    Dim AggMetric_Exp As IDSSExpression 
    
    Set AggMetric = Metric_Embedded.Item(3) 
    'Using VB object watch, we can check that the third element of 
    AggMetric.Special.Expression.Item contains a reference to the metric 
    that this metric will be subtotal of (M2):

    'Item(1) = Metric_01
    'Item(2) = AggMetric_01
    'Item(3) = AggMetric_02 <-- has the reference to M2
    
    'For Each OI in Metric_Embedded.Item
    '   do something
    'End For Each 
        
    Set AggMetric_Exp = AggMetric.Special.Expression
    Dim CurrentNode As IDSSNode 
    'Remove node with reference to M2 
    Set CurrentNode = AggMetric_Exp.Root.Child.Child.Child.Next 
    CurrentNode.Remove 

    'Add a DssNodeMe in its place 
    Set CurrentNode = AggMetric_Exp.Root.Child.Child 
    CurrentNode.Add (DssNodeMe) 

    'Save the metric 
    Metric.Info.Save 
    MsgBox "Done." 
    Screen.MousePointer = vbArrow 
    Exit Sub 
ErrCatcher:
    MsgBox "Error: " & strErrMsg 
    Screen.MousePointer = vbArrow