Version 2021

Creating user-defined subtotals

MicroStrategy offers subtotal functions that satisfy many subtotaling requirements. If they do not satisfy your requirements, you can create a user-defined subtotal to answer your particular needs. User-defined subtotals can also be used for dynamic aggregation, which is discussed in Specifying dynamic aggregation functions. For more information, see About user-defined subtotals.

To create a user-defined subtotal

  1. In MicroStrategy Developer, log in to a project.

  2. From the File menu, point to New, and select Subtotal. The Subtotal Editor opens.

  3. If the Object Browser is not displayed, from the View menu, select Object Browser.

  4. Define the formula of the subtotal, using the Object Browser to find functions, operators, metrics, and other objects. You can drag and drop them into the Definition box. You can also type the formula of the subtotal in the Definition box. You can use an "x" as the placeholder for the metric to be subtotaled.

    1. The placeholder is not mandatory in the formula.

    2. Any metrics included in the subtotal formula must be included in the report that uses the subtotal function.

  5. By default, the level (dimensionality) is the default dimensionality @, matching the behavior of a standard subtotal. To specify the level at which the subtotal is to be aggregated, complete the following steps:

    • Click Level (Dimensionality) on the component display window. The Level (Dimensionality) component window displays below the Subtotal Definition area.

      • To use the default level if it has been removed, click Add Default Level.

      • To create a new level, double-click an attribute in the Object Browser. The attribute is added as the Target.

      • To remove a level, select it and then click Remove.

      • To delete all levels and return to the default level, click Reset.

  6. When you have completed the definition, click Validate. The Subtotal Editor displays a status message, at the bottom of the Definition area, showing whether the new definition is correct (Valid expression).

  7. Select Save from the File menu. The Save As dialog box opens.

    You must name and save the subtotal before you can apply it to metrics, that is, make it available for use on reports.

  8. By default, all subtotals are saved in the directory Project Objects\Subtotals, although you can change the directory here.

  9. Enter the name of the new subtotal and click OK. You are returned to the Subtotal Editor.

    You cannot use the same name as a metric saved in this directory.

  10. Apply the subtotal to a metric. (How?)

  11. Click Save and Close on the toolbar. You are returned to MicroStrategy Developer.