Version 2021

# Using a hierarchy as the target of a metric level

## Before you begin

This topic assumes a basic understanding of level metrics, including level targets. See Level metrics: A practical overview and The context of a calculation: Level metric target for background information.

You can use a hierarchy as the target of a metric level to allow the level metric to dynamically adapt to the report. When placed on a report with attributes from that hierarchy, the metric calculates at the level of the lowest attribute of that hierarchy.

For example, a level metric with a target of Region calculates the regional revenue, regardless of what the report contains. If a level metric with the Geography hierarchy as the target is placed on a report with Region, the metric calculates the regional revenue. Placed on a report with Call Center, the metric calculates the revenue for Call Center. This example is explained in more detail in Hierarchy level metric examples below. The complete rules for replacing the target hierarchy are discussed in Resolving a hierarchy in a level metric below.

Attributes can be used as the target of a level metric to define the exact level of calculation for the metric. For information on using an attribute as the target of a level metric, see About level metrics.

## Resolving a hierarchy in a level metric

When a hierarchy is used as the target in a level metric, the hierarchy is automatically replaced with an attribute when the report is executed. The rules for replacing the hierarchy are listed below:

The examples described below use the Products hierarchy, which contains the following attributes, in order from highest to lowest: Category, Subcategory, and Item.

If the report grid contains attributes from the target hierarchy, the lowest attribute of that hierarchy found on the report is used as the target.

A level metric with a target of Products, placed on a report with Category and Subcategory, calculates at the subcategory level. If the level metric uses standard grouping, the metric calculates a value for each subcategory. If the level metric is not grouped, the same number, the total for all the subcategories displayed on the report, is calculated for each row (Subcategory) of the report.

If the report grid does not contain attributes from the target hierarchy, the report filter is checked. If the report filter contains attributes from the target hierarchy, the lowest attribute of that hierarchy found in the filter is used as the target.

A level metric with a target of Products, placed on a report with Region that is filtered for specific subcategories, calculates at the subcategory level. If the level metric uses standard filtering, the metric calculates only for the attributes in the filter. The same number, the total for all the subcategories on the report, is displayed for each row (Region) of the report. If the level metric uses absolute filtering, the target hierarchy is not included in the metric calculation, so the grand total across the project is displayed for each row.

If neither the report grid nor the report filter contains attributes from the target hierarchy, then the hierarchy is ignored.

A level metric with a target of Products, placed on a report with Region that is filtered for Customer, calculates a grand total across the project, ignoring the target hierarchy.

If the level metric ignores filtering, the target hierarchy is ignored in all instances. The level metric calculates a grand total across the project for all rows of the report.

## Hierarchy level metric examples

For example, the Geography Revenue metric is defined as:

• Formula: Sum(Revenue)

• Target: Geography hierarchy

• Filtering: Standard

• Grouping: Standard

• No report level

The Geography Revenue metric is placed on a report with attributes from the Geography hierarchy: Region and Call Center. Other metrics on the report include the Revenue metric and the Regional Revenue metric, which is a level metric with a target of Region. The report is filtered for Central and Mid-Atlantic. The report is shown below:

• Revenue calculates at the level of the report, which is Call Center.

• Regional Revenue calculates at the level of Region. The Regional Revenue values for both call centers in each region is the same.

• Geography Revenue calculates at the level of the lowest attribute in the Geography hierarchy on the report, which is Call Center.

Remove Call Center from the report; the results are shown below:

All three metrics return the same numbers because:

• Revenue calculates at the level of the report, which is now Region.

• Regional Revenue continues to calculate at the region level.

• Geography Revenue calculates at the level of the lowest attribute in the Geography hierarchy on the report, which is now Region.

Add the Category attribute, which is not in the Geography hierarchy, to the report. The results are shown below:

• Revenue calculates at the level of the report, which is now Region and Category.

• Regional Revenue continues to calculate at the region level.

• Geography Revenue calculates at the level of the lowest attribute in the Geography hierarchy on the report, which is still Region. The Geography Metric amount is the same for each region, regardless of category.

Replace Region with Call Center. The results are shown below:

Now all three metrics return different results:

• Revenue calculates at the level of the report, which is now Category and Call Center.

• Regional Revenue continues to calculate at the region level.

• Geography Revenue calculates at the level of the lowest attribute in the Geography hierarchy on the report, which is now Call Center. The Geography Metric amount is the same for each call center, regardless of category.

Remove Call Center, so that no attributes in the Geography hierarchy remain on the report. The results are displayed below:

Revenue calculates at the level of the report, which is now Category.

• Regional Revenue continues to calculate at the region level. Since no attributes from the Geography hierarchy are on the report, Regional Revenue calculates the revenue for all the regions on the report. Since the report is filtered for Mid-Atlantic and Central, only those regions are included in the calculation.

• Geography Revenue calculates at the level of the lowest attribute in the Geography hierarchy on the report. The report grid does not contain any Geography attributes, but the report filter does. Like Regional Revenue, Geography Revenue calculates the same number for each row, the sum of all regions in the report.

Remove the report filter, so that no Geography attributes remain in the report or report filter. The results are displayed below:

• Revenue calculates at the level of the report, which is now Category. The number is higher than in the previous sample, since all regions are included in the calculation.

• Regional Revenue continues to calculate at the region level. Since no attributes from the Geography hierarchy are on the report or in the report filter, Regional Revenue calculates the revenue for all the regions on the report. Without a report filter, all regions in the project are included, so this is a grand total of all revenue in the project.

• Geography Revenue calculates at the level of the lowest attribute in the Geography hierarchy on the report. Neither the report grid nor the report filter contains any Geography attributes. Like Regional Revenue, Geography Revenue calculates the same number for each row, the sum of all regions in the project.