Best Practices

Follow the guidelines below when creating derived metrics:

• In reports, you can define derived metrics with objects in the Report Objects pane. The Report Objects are the components included in the report definition, even if they are not displayed on the report grid.

• A derived metric can be defined with the metrics in the report definition. The Input Metric Formula dialog box where you create derived metrics allows you to choose only from objects included in the report definition, as shown below.

  

• Attributes included in the report definition are also available to use in the definition of a derived metric. If you use an attribute as part of the metric definition, the metric calculation requires new report SQL to be executed against the data warehouse. This re-execution is not required for derived metrics that only use metrics in their definitions.
• You can use one or more functions or operators in the formula of the derived metric. Click the fx button to access available functions and operators.

• You can use numeric prompts in the formula of the derived metric, which allows users to determine part of the value of the metric. For example, if the value of your metric depends on the current tax rate, you can prompt users to type the current tax rate.

  For steps to create prompts, see the Basic Reporting Help.

• You can change the level at which a derived metric is calculated. For example, the derived metric sum(M1) {Attribute1} is calculated at the Attribute1 level. For information on metric levels, see the Advanced Reporting Help.
• Any user can modify a derived metric after report execution, since its formula is visible to all users. If a derived metric should not be modified by end users, create the metric in the Metric Editor and add it to the report as a normal metric.
• Transformation objects cannot be used with derived metrics because they require SQL to be re-executed against the data warehouse.
• View filters can filter the results of a derived metric. A view filter is an additional filter applied in memory to the report results to restrict the amount of data displayed on the report. For more information on view filters, see View Filters.

• A derived metric is dependent on any report objects that are included in the derived metric's definition. Because of this dependency, you cannot remove an object from the report that is used in a derived metric definition.

  If you try to remove an object from the report, a message is displayed that indicates you cannot remove the object because it is being used by the derived metric. You can however move an object off the report grid so that it only appears in the Report Objects pane. This allows you to hide the object from the report grid and still support any derived metrics that are dependent on it.

• In Report Services documents that use multiple datasets, you can create derived metrics that use metrics from different datasets. If you do so, note the following:

  • The metrics you use may be calculated at different levels, depending on the definition of the datasets. For example, one dataset contains the Subcategory attribute and the Revenue metric, and another dataset contains the Category attribute and the Profit metric. If you create a derived metric based on Revenue and Profit, note that Revenue is calculated at the Subcategory level, and Profit is calculated at the Category level. This can be useful to create percent-to-total metrics.
  • If the derived metric is to be used in a data field, it is recommended that you use a calculated expression instead.

  For information and examples of using derived metrics in documents with multiple datasets, refer to the Document Creation Help.

You can create derived metrics with the following methods described in this section:

• Quickly creating a derived metric in Web: You can create a derived metric based on often-used functions, such as Average, by using the Insert Metric feature in Web.
• Creating a derived metric using the Input Metric Formula dialog box: You can create any type of derived metric by defining derived metric expressions using the Input Metric Formula dialog box.
• Using rank and percent-to-total metric analysis: You can quickly create derived metrics that display the percent in relation to a selected total of each item affected by the metric or display a ranking number to the metric values for a given attribute. These can be quickly created using shortcut metrics.