About user-defined subtotals

MicroStrategy offers ten subtotal functions that are available for use with every metric and report. These are simple aggregate functions that satisfy many subtotaling requirements. If they do not answer your particular needs, you can create a customized, user-defined subtotal using the Subtotal Editor. For example, you can create nested subtotals.

User-defined subtotals allow you to employ the following in subtotals:

• A single aggregation function such as avgdev,

• Constants in conjunction with aggregation functions

• Nested functions

• Dimensional subtotals

• Other metrics in the subtotal formula

For example, you need a subtotal that always calculates at the regional level, regardless of the level of the report. You can create a user-defined subtotal, setting it to the Region level (or dimension). Another example is a weighted subtotal, where a subtotal is weighted with another metric, such as an average profit weighted by units sold. This example is more fully described in User-defined subtotal example (weighted subtotal) below.

After a user-defined subtotal is created and applied to a metric, it is indistinguishable from the standard predefined subtotals functions such as total and count. It can be used as the total subtotal function and the dynamic aggregation function for a metric. When a user runs a report containing this metric, the total subtotal function calculates the grand totals for the metric. The user can select the user-defined subtotal from the list of subtotal functions available for the metric.

For a description of metric subtotals and how they are affect a report, as well as a list of the standard predefined subtotal functions, see About metric subtotals. For a description of dynamic aggregation and instructions to specify the function used in dynamic aggregation, see Specifying dynamic aggregation functions.

Do not confuse user-defined subtotals with custom subtotals, which are defined for a particular report. User-defined subtotals are made available to metrics and can then be used on any report that uses the metric.

For instructions to create and use a user-defined subtotal, see the following topics:

• Creating user-defined subtotals

• Steps to apply a user-defined subtotal to metrics

• Subtotal Editor

User-defined subtotal example (weighted subtotal)

You need to create a report containing units sold and profit by call center. For each region, the report must calculate the average units sold and the average profit, weighted by the number of units sold in the region. Since these two averages must be displayed on the same line, a custom subtotal is needed. The formula for the weighted regional average profit is:

Sum(x*[Units Sold]){Region}/

where:

• x is the placeholder for the metric to be subtotaled. In this case, it is the Profit metric.

• [Units Sold] is a metric that sums the fact Units Sold.

• {Region} is the level of the subtotal calculation.

For information on symbols, see Metric Editor Formula tab: Metric level property symbols.

Finally, you need to sum the weighted regional average profit over all the regions. The formula for this subtotal is:

Sum(Sum(x*[Units Sold]){Region}/

which calculates the sum on an empty level, represented by the {}. An empty level calculates across the entire report. This subtotal will be used as a grand total on the report. To use these formulas in any of the Analytics Modules, you need to create user-defined subtotals because they are not standard subtotal functions in the Analytics Modules. The MicroStrategy Tutorial, however, offers them as standard subtotals.

For another example of a user-defined subtotal, see the Advanced Reporting Help.