MicroStrategy ONE
About metric subtotals
Subtotals allow you to display totals on a report by selecting an aggregation function. When you create a metric, you determine which subtotals are available for that metric. A user who runs a report with that metric can then choose which of those subtotals to display on the report, as described in Applying subtotals to a report.
In the context of metrics, subtotals permit computation and display of quantified data gathered by MicroStrategy, along attribute groupings that you can specify for a report. You can define subtotals using any of the standard predefined subtotal functions and user-defined subtotals; each is designed to yield a specific result. The behavior of subtotal aggregations is based on the types of data included in the metric to which the subtotal is applied.
When you create a metric, you determine which subtotals are available for that metric. The user who runs a report with that metric can then choose which of those subtotals to display on the report.
Defining subtotal defaults
For each metric, you can change the default grand total function and add functions to the list of available subtotals. This allows you to use different grand total functions for different metrics on the same report, for instance.
You can use the Metric Editor to determine:
-
Which function calculates the metric's grand totals when the metric is used on a report (the total subtotal function). This function is applied when a user selects Grand Totals from the Data menu.
-
Which subtotal functions are available for the metric when it is used on a report. When a report containing the metric is run, a user can choose which of these subtotals to apply. These functions are displayed when a user selects Subtotals from the Data menu, as described in Applying subtotals to a report.
You can disable totaling on metrics that should not be subtotaled, such as inventory metrics or other non-aggregatable metrics. For instructions on how to define the total subtotal function and available subtotals to disable subtotals, see Disabling metric subtotals.
Your choices for both the total subtotal and available functions are standard predefined subtotal functions, which are simple aggregate functions such as Total and Count that satisfy many subtotaling requirements. If they do not answer your specific needs, you can create a user-defined subtotal and use it just like any of the built-in subtotal functions. For a list of the predefined subtotal functions, see Standard predefined subtotal functions below. For an explanation of when to use user-defined subtotals, a list of objects that can be used in them, and examples, see About user-defined subtotals.
Standard predefined subtotal functions
The following aggregation functions are the standard predefined subtotal functions. You can choose any of them or any user-defined subtotals as subtotals.
-
Avg [average]: sum of input values divided by number of input values
-
Count [count]: number of input values
-
GeoMean [geometric mean]: square root of the product of input values
-
Max [maximum]: largest input value
-
Median [median]: middle value when all values are sorted
-
Min [minimum]: smallest input value
-
Mode [mode]: most frequently found input value
-
Product [product]: all input values multiplied together
-
Stdev [standard deviation]: distribution of input values
-
Sum [total]: sum of input values
-
Var [variance]: square of the distribution of input values
These functions reflect only those most frequently used for evaluating metric subtotals. The Analytical Engine can also handle a large number of statistical, mathematical, financial, date-and-time, string, and OLAP functions, from simple to highly complex.
See the Functions Reference for examples and details on every built-in function available in MicroStrategy.
Displaying subtotals on reports
In addition to the functions provided by the Metric Editor, you can apply subtotals dynamically, that is, directly from a displayed report or during report/template creation. The Subtotals dialog box allows you to:
-
Select the functions to calculate the subtotals
-
Specify the format and location for subtotal display
For instructions, see Applying subtotals to a report.