Returning financial data with metrics

Metrics allow you to further integrate your data into MicroStrategy and display this data on reports. To support this financial reporting project, you need to create some specific metrics.

Retrieving financial data from the database

Since you modeled your data to store all financial data in a single fact column, you need to create a metric that returns all of the data that is stored in the database. The definition for this metric is as follows:

Sum(Fact) {~+, <[Level]+}

In the definition provided above:

Fact is the single fact you created for your financial data, as described in Creating schema objects for financial data. In the example provided, the fact is named Data.
Level is the hierarchy created for the attributes used to define the hierarchical organization of the financial line items, as described in Creating tables to provide hierarchical organization. In the example provided, the hierarchy is named Level.

The level of this metric is defined as {~+, <[Level]+}, which indicates that this metric is not aggregated over the Level attributes that define the hierarchical structure of the financial line items. This ensures that subtotals are calculated correctly.

For example, the definition for this metric can be as follows:

Sum(Data) {~+, <[Level]+}

You can use any name for this metric; for this example the metric is named StoredData.

Metrics for financial line items with percentage values

All financial line items that are percentages, margins, ratios, and so on are not stored in the database, as described in Storing financial data . These instead need to be created as metrics in MicroStrategy.

For example, in the report shown below, the financial line items that are not stored in the database include Gross Profit Margin, Operating Margin, and Interest Coverage Ratio.

Determine the general calculation for each of these metrics. In the example, these metrics would have the following general calculations:

Gross Profit Margin can be defined as Gross Profit / Revenue.
Operating Margin can be defined as Operating Income / Revenue.
Interest Coverage Ratio can be defined as Operating Income / Interest Expense.

With these general definitions, you need to define the metrics so that they represent these values. Since each of these metrics relies on other financial line items, you need to determine how to return the values for each financial line item. This can be done with a definition with the following syntax:

Sum(Fact) {~+, !Level+} <[FinancialLineItemFilter]; @2; ->

In the definition provided above:

Fact is the single fact you created for your financial data, as described in Creating schema objects for financial data. In the example provided, the fact is named Data.
Level is the hierarchy created for the attributes used to define the hierarchical organization of the financial line items, as described in Creating tables to provide hierarchical organization. In the example provided, the hierarchy is named Level.

The level of this metric is defined as {~+, !Level+}, which indicates that this metric does not include a group-by on the Level attributes that define the hierarchical structure of the financial line items. This ensures that the division calculation can be performed accurately.

FinancialLineItemFilter is the filter used to determine the data for the specific financial line item. Creating these filters is described in Determining financial line items using filters.
The metric definition parameters @2; and - define how the filter is applied to the metric.

In this example, this definition can be used to create the two values required for Gross Profit Margin:

Gross Profit = Sum(Data) {~+, !Level+} <[GrossProfit]; @2; ->
Revenue = Sum(Data) {~+, !Level+} <[Revenue]; @2; ->

To create the metric for Gross Profit Margin, you include these two definitions of financial line items in one metric definition for Gross Profit Margin:

Sum(Data) {~+, !Level+} <[GrossProfit]; @2; -> / Sum(Data) {~+, !Level+} <[Revenue]; @2; ->

All metrics for financial line items can be created with this same format; for this example, you would create the following metric definitions:

Operating Margin = Sum(Data) {~+, !Level+} <[OperatingIncome]; @2; -> / Sum(Data) {~+, !Level+} <[Revenue]; @2; ->
Interest Coverage Ratio = Sum(Data) {~+, !Level+} <[OperatingIncome]; @2; -> / Sum(Data) {~+, !Level+} <[InterestExpense]; @2; ->

Notice that the only difference between these metrics is the filters used to identify the required financial line items.

You must define each of these metrics as a smart metric. When creating the metric in the Metric Editor, on the Subtotals/Aggregation tab, select the Allow Smart Metric check box. This ensures that the division is calculated correctly. For best practices in using smart metrics, refer to the Advanced Reporting Guide.

Financial data metric

When displaying reports of your financial data, the values are all displayed using a single metric. This metric combines the values stored in your database with the values of the metrics that calculate the percentage financial line items into a single metric.

To create the financial data metric

1 To create the metric that combines all of the data for your financial line items into a single metric, you need to create the following metrics:
a A metric that retrieves all of the financial data that is stored in the database. Creating this metric is described in Retrieving financial data from the database.
b A metric that calculates the financial line items that are not stored in the database, and instead are created within MicroStrategy. Creating these metrics is described in Metrics for financial line items with percentage values.
c A metric that helps to determine whether the data for a financial line item is retrieved from the database, or from a metric in MicroStrategy. The syntax for this metric is:

Min([LevelN]@ID) {<Level+}

In the definition provided above:

LevelN is the lowest level attribute created to help define the hierarchical organization of the financial line items, as described in Creating tables to provide hierarchical organization. In the example provided, the attribute is named Level3.
Level is the hierarchy created for the attributes used to define the hierarchical organization of the financial line items, as described in Creating tables to provide hierarchical organization. In the example provided, the hierarchy is named Level.

Based on the example, the definition for this metric is as follows:

Min([Level3]@ID) {<Level+}

In addition to this metric definition, you must define the subtotal function for this metric to be Minimum. When creating the metric in the Metric Editor, on the Subtotals/Aggregation tab, in the Total subtotal function drop-down list, select Minimum.

You can use any name for this metric; for this example, the metric is named Levels.

2 With these metrics, you are ready to build the single metric that combines all of your financial line item data into one metric. The syntax for this metric is:

Case(([Levels] = Value1), [PercentageMetric1], ... , ([Levels] = ValueN), [PercentageMetricN], [StoredData])

In the definition provided above:

Levels is the metric you created above to determine whether the data for a financial line item is retrieved from the database, or from a metric in MicroStrategy.
([Levels] = Value1), [PercentageMetric1], ... , ([Levels] = ValueN), [PercentageMetricN] is the part of the metric definition that retrieves the values for all financial line items that are created using metrics in MicroStrategy, as described in Metrics for financial line items with percentage values.

Value1 through ValueN refer to the values that correspond to the financial line item. You can use the relationship table you created in Creating tables to provide hierarchical organization to determine the value for each financial line item.

PercentageMetric1 through PercentageMetricN refer to the metrics you created for the financial line items created as metrics to support percentages.

StoredData is the metric that retrieves all of the financial data that is stored in the database. Creating this metric is described in Retrieving financial data from the database.

In this example, the metric needs to determine whether to use one of four different metrics as the source of data for the financial line item. This includes the metrics for Gross Profit Margin, Operating Margin, Interest Coverage Ratio, and the StoredData metric that retrieves all the financial data that is stored in the database. The metric definition to select between these metrics is as follows:

Case(([Levels] = -11), [GrossProfitMargin], ([Levels] = -7), [OperatinMargin], ([Levels] = 2), [InterestCoverageRatio], [StoredData])

This metric selects data from the correct source for each financial line item. You can use any name for this metric; for this example, the metric is named Values. This Values metric is the metric that is included directly on reports for your financial reporting project.

3 To ensure that the Values metric is displayed correctly, you must also perform the following configurations for this metric.
a Open the metric in the Metric Editor and switch to the Subtotals/Aggregation tab.
b From the Total subtotal function drop-down list, select Default.
c Select the Allow Smart Metric check box near the bottom of the interface.
d From the Tools menu, select Metric Join Type. The Metric Join Type dialog box opens.
e Clear the Use default inherited value check box.
f Select the Outer option, and click OK to return to the Metric Editor.
g Click Save and Close to save your changes and close the Metric Editor.