Subtotal Dimensionality Use

Subtotal Dimensionality Use is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

Subtotal Dimensionality Use determines how the level of calculation is defined for metrics that are included on reports that use dynamic aggregation, which is an OLAP Services feature. This VLDB property has the following options:

• Use only the grouping property of a level metric for dynamic aggregation (default): The dimensionality, or level, of the metric is used to define how the metric data is calculated on the report when dynamic aggregation is also used. When selecting this option, only the grouping option for a level metric is used to calculate metric data. For detailed examples and information on defining the dimensionality of a metric, refer to the documentation on level metrics provided in the Advanced Reporting Help.
• Use only the grouping property of a level subtotal for dynamic aggregation: The dimensionality, or level, of the metric's dynamic aggregation function is used to define how the metric data is calculated on the report when dynamic aggregation is also used. You can define the level of calculation for a metric's dynamic aggregation function by creating a subtotal, and then defining the level of calculation for that subtotal. When selecting this option, only the grouping option for a subtotal is used to calculate metric data. For detailed examples and information on creating subtotals, refer to the Advanced Reporting Help.
• Use both the grouping and filtering property of a level metric for dynamic aggregation: The dimensionality, or level, of the metric is used to define how the metric data is calculated on the report when dynamic aggregation is also used. When selecting this option, both the grouping and filtering options for a level metric are used to calculate metric data. For detailed examples and information on defining the dimensionality of a metric, refer to the documentation on level metrics provided in the Advanced Reporting Help.
• Use both the grouping and filtering property of a level subtotal for dynamic aggregation: The dimensionality, or level, of the metric's dynamic aggregation function is used to define how the metric data is calculated on the report when dynamic aggregation is also used. You can define the level of calculation for a metric's dynamic aggregation function by creating a subtotal, and then defining the level of calculation for that subtotal. When selecting this option, both the grouping and filtering options for a subtotal are used to calculate metric data. For detailed examples and information on creating subtotals, refer to the Advanced Reporting Help.

Example

Consider a metric that performs a simple sum of cost data by using the following metric definition:

Sum(Cost) {~+}

This metric is named Cost, and the syntax {~+} indicates that it calculates data at the level of the report it is included on. Another metric is created with the following metric definition:

Sum(Cost) {~+}

This metric also uses a subtotal for its dynamic aggregation function that uses the following definition:

Sum(x) {~+, !Year , !Category }

Notice that the function for this subtotal includes additional level information to perform the calculation based on the report level, Year, and Category. As shown in the image below, this subtotal function, named Sum(Year,Category) is applied as the metric's dynamic aggregation function.

This metric is named Cost (subtotal dimensionality). This metric along with the simple Cost metric is displayed on the report shown below, which also contains the attributes Year, Region, and Category.

Notice that the values for these two metrics are the same. This is because no dynamic aggregation is being performed, and the Subtotal Dimensionality Use VLDB property is also using the default option of Use dimensionality from metric for dynamic aggregation. With this default behavior still applied, the attribute Year can be removed from the grid of the report to trigger dynamic aggregation, as shown in the report below.

The metric values are still the same because both metrics are using the level of the metric. If the Subtotal Dimensionality Use VLDB property for the report is modified to use the option Use dimensionality from subtotal for dynamic aggregation, this affects the report results as shown in the report below.

The Cost (subtotal dimensionality) metric now applies the level defined in the subtotal function that is used as the metric's dynamic aggregation function. This displays the same Cost value for all categories in the Northeast region because the data is being returned as the total for all years and categories combined.

Levels at Which You Can Set This

Database instance, report, template, and metric