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. These options are found on the Subtotals/Aggregation tab of the Metric Editor. The default grand total is called the Total subtotal function. You can add functions to the list of available subtotals or remove functions. The functions listed here are available as subtotals in reports.

You can disable totaling on metrics that should not be subtotaled, such as inventory metrics or other non-aggregatable metrics. For steps to define the total subtotal function and available subtotals to disable subtotals, see Disabling 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 Subtotal Functions. For an explanation of when to use user-defined subtotals, a list of objects that can be used in them, and examples, see Creating Your Own Subtotals.

Standard Subtotal Functions

The predefined subtotal functions in the following table are automatically available for use with every metric and report. They are simple aggregate functions designed to satisfy many subtotaling requirements.

Subtotal function	Description
Count	Count[count] number of input values
Average	Avg[average] sum of input values divided by number of input values
Minimum	Min[minimum] smallest input value
Maximum	Max[maximum] largest input value
Product	Product[product] all input values multiplied together
Median	Median[median] middle value when all values are sorted
Mode	Mode[mode] most frequently found input value
Standard deviation	Stdev[standard deviation] distribution of input values
Variance	Var[variance] square of the distribution of input values
Geometric mean	Geomean[geometric mean] square root of the product of input values

These functions reflect only those most frequently used for evaluating metric subtotals. The metric subtotal calculations and consolidations needed to create a report are performed by the Analytical Engine, which is a component of MicroStrategy Intelligence Server. In addition to the above standard functions, the Analytical Engine also handles a large number of statistical, mathematical, financial, date-and-time, string, and OLAP functions, ranging from simple to highly complex.

See the Functions Reference for examples and details on every built-in function available in MicroStrategy.

Creating Your Own Subtotals

The standard predefined subtotal functions, which are automatically available for use with every metric and report, are simple aggregate functions that satisfy many subtotaling requirements. If they do not answer your specific needs, you can create your own user-defined subtotal using the Subtotal Editor. You can then use it in subtotal definitions just like any of the built-in subtotal functions such as Total and Count.

You can create your own subtotal using any combination of the following:

An aggregation function, such as avgdev, IRR, MIRR, and NPV, that is not one of the standard predefined subtotal functions
Multiple functions
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 included in the User-Defined Subtotal eExample (Weighted Subtotal).

After a user-defined subtotal is created, it is indistinguishable from the standard predefined subtotal functions such as total or count. For a particular metric, you can choose a user-defined subtotal as the total subtotal function or add it to the list of available subtotals. When you run a report containing this metric, the total subtotal function calculates the grand totals for the metric. Select from the available subtotals to display additional subtotals for the metric. For more information, see Displaying Subtotals on Reports.

Do not confuse user-defined subtotals with custom report subtotals, which are defined for a particular report for display purposes. User-defined subtotals are made available to metrics and can then be used on any report that uses the metric. For more information on custom report subtotals, see Custom Report Subtotals.

You can create a user-defined subtotal with a level, and select the subtotal as the dynamic aggregation function for a metric, to allow you to specify the level of dynamic aggregation for that metric. (Dynamic aggregation is the roll-up of metric values that occurs when an attribute is moved from the report grid to the Report Objects.) You must also change the Subtotal Dimensionality Use VLDB property because the default behavior is to use the level of the metric that is being aggregated, not that metric's dynamic aggregation function. For a more detailed description of this VLDB property, along with an example of its use, see the System Administration Guide.

User-Defined Subtotal Example (First Function)

You need to produce an inventory report showing the number of electronics products received in 2023 by month and by product. The report must also provide the first shipment quantity. To do this, create a user-defined subtotal using the First function. Create a metric with the Units Received fact and set the Total subtotal function, which calculates the metric's grand totals, to the new subtotal. Next, create the report. When you apply subtotals, the user-defined subtotal displays the number of items received in the first shipment, regardless of the month in which it arrived.

Since this example focuses on user-defined subtotals and using the Subtotal Editor, steps are provided to create the subtotal. For details to create metrics and reports, refer to the online help.

To Create and Use a User-Defined Subtotal (Using the First Function)

In MicroStrategy Developer, choose File > New > Subtotal.
Notice the Subtotal Editor's resemblance to the Metric Editor—their similarity in function and appearance helps to ease the process of creating subtotals.
In the Object Browser, navigate to the Basic Functions folder, located under Functions and Operators. Double-click the First function.
In the definition, right-click First, and select First parameters.
Click the Sort By tab. The First function must be sorted to achieve correct results.
Select Sort by objects. Click Add.
Since the report will be sorted by time, in the Object Browser select Attributes, then Time. Double-click the following, in the order they appear below, to add them to the sort:

Year
Quarter
Month
Day

Click OK, then OK again to return to the Subtotal Editor.
For more information on the First function, including details on sorting, see the Functions Reference.
Click Validate.
Click Save and Close. Name the new subtotal First (Date Sort).
Create a new metric:

In the Metric Editor, select the Units Received fact.
On the Subtotals/Aggregation tab, select First (Date Sort) as the Total subtotal function.
Save the metric as Units Received.

Create a new report:

In the Report Editor, place Item on the rows.
Place Month and the Units Received metric on the columns.
Filter on Year = 2023 and Category = Electronics.
Add Grand Totals.

Execute the report. The results are displayed in the following report sample:

This sample presents only a subset of the entire report, showing the first three months of the year and the total.

Notice that the Total does not add all units received throughout the year, but rather displays the amount of the first shipment, regardless of what month the shipment arrived. For example, you received 20 AM/FM Stereo Receivers each month; the total is 20, the amount received in January alone. No Digital Surround Sound Receivers were received in January, but 20 were received in February, which the subtotal reflects. Hitachi Hi8 Camcorders, in the last line of the sample, were not received until March, so the total is derived from the March shipment.

Remember to save the report if you want to keep it.

User-Defined Subtotal eExample (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}/Sum([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 Level Symbols: Defining Expression Syntax.

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}/Sum([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.

The focus of this example is user-defined subtotals and using the Subtotal Editor, so steps are provided to create the subtotal. For details to create metrics and reports, see the MicroStrategy Web Help.

To Create and Use a User-Defined Subtotal (Using a Weighted Subtotal)

In MicroStrategy Developer, choose File > New > Subtotal.
Enter the formula for the weighted regional average subtotal, as described above.
Click Validate.
In the Ambiguity: 'Units Sold' dialog box, select the metric and click OK.
In the Ambiguity: 'Region' dialog box, select the attribute and click OK.
You should see a "Valid expression" message in the status area. If you do not, review the formula and correct any errors.
Click Save and Close. Name the new subtotal Weighted Regional Average.
Open the Subtotal Editor again.
Enter the formula for the sum of the weighted regional average subtotal, as described above.
Click Validate.
In the Ambiguity: 'Units Sold' dialog box, select the metric and click OK.
In the Ambiguity: 'Region' dialog box, select the attribute and click OK.
You should see a "Valid expression" message in the status area. If you do not, review the formula and correct any errors.
Click Save and Close. Name the new subtotal Sum of WRA (which stands for Weighted Regional Average).
Open the Profit metric in the Metric Editor.
Click the Subtotals/Aggregation tab.
Select Weighted Regional Average and Sum of WRA in the Project subtotals list. Click > to add them to the metric.
Save the metric and close the Metric Editor.
Create the new report:

In the Report Editor, place Region and Call Center on the rows, and then the Units Sold and Profit metrics on the columns.
Choose Data > Subtotals.
Click Advanced.
Click New.
Type Average, Weighted Regional Average as the name of the custom subtotal.
For Units Sold, select Average from the pull-down list.
For Profit, select Weighted Regional Average.
Click OK.
Select Across level and then Call Center, so the subtotals are calculated for each region.
Click New to create another custom subtotal to display in the grand total position.
Type Overall Average, Sum of WRA as the name.
For Units Sold, select Average from the pull-down list.
For Profit, select Sum of WRA.
Click OK.
Select By position. For Rows, select Grand Total from the pull-down list. For both Columns and Pages, select None.
Click OK.
Notice that custom subtotals are distinguished by a different icon. An icon with an exclamation mark (!) means that the subtotals are not available for all metrics on the report. Recall that you added the user-defined subtotals to the Profit metric only, not the Units Sold metric.
Click OK.
Execute the report. The results are displayed in the following image: