MicroStrategy ONE
Shortcut Metrics
Shortcut metrics are based on metrics already included in a report and provide a quick way to add new metrics to the report. Refer to the examples section for usage scenarios with code samples.
Shortcut metrics belong to one of the following categories:
All shortcut metrics are derived metrics except for the transformation metrics, which must be calculated in SQL.
Derived metrics are metrics that are developed in the context of a report, allowing you to create calculations based on the data already available on the report. For example, if a report contains Revenue and Cost metrics, you can calculate Profit on the fly with the following definition:
Profit = ([Revenue Metric] - [Cost Metric])
Percent-to-total metrics
Percent-to-total metrics display the percent in relation to a selected total of each item affected by the metric. The total can be by column, by row, by page, for each value of the attribute, or the grand total.
A percent-to-total metric is defined by the following formula: Original Metric / (Sum(Original Metric){…dimensionality…})
The dimensionality is determined by a set of template units specified by the caller through this object’s attributes: axesBitMap, axesUnits, and units. It gives the level at which the subtotal is calculated. Note that this is the denominator’s dimensionality.
Determining the subtotal level is explained using the bitmap model. This model shows what happens when more than one specification type (argument) for the axes/units is used.
-
For each axis on the template, a bitmap is created, with one bit per template unit.
-
Walk the caller's Axes specification (axesBitMap) and set the bits in the axes bitmaps accordingly. For example, if an axis is specified, set all the bits of the corresponding bitmap to 1.
-
Walk the Units on axes specification (axesUnits) and, for every unit identified, mark the corresponding bit and all the following bits in its axis bitmap.
-
Walk the standalone Units specification (units) and, for every unit identified, mark (only) the corresponding bit on its axis bitmap.
-
If nothing is specified for Pages and Chapters, flip (complement bits of) all bitmaps. If something is specified for Pages or Chapters, do not do anything.
-
Build the dimensionality for the denominator metric by walking the bitmaps and adding dimensionality units (of type Attribute) for all bits that are set in the bitmaps that correspond to a template unit of type attribute.
Consider the following report.
|
Customer Count |
||||
Gender |
Age Range |
> 100K |
50K-100K |
<50K |
TOTAL |
Female |
>50 |
200 |
400 |
80 |
680 |
Female |
40-50 |
50 |
300 |
160 |
510 |
Female |
30-40 |
40 |
180 |
240 |
460 |
Female |
<30 |
10 |
120 |
320 |
450 |
|
Total |
300 |
1000 |
800 |
2100 |
If the user right-clicks on a specific metric he or she is given the following choices.
Percent to total — > |
Add new metric — > |
Percent to Row Total |
|
|
Percent to Column Total |
Percent to Page Total (if applicable) |
||
Percent to Grand Total |
Selecting Percent to Column Total displays the following report.
|
Customer Count |
||||
Gender |
Age Range |
> 100K |
50K-100K |
<50K |
TOTAL |
Female |
>50 |
67% |
40% |
10% |
32% |
Female |
40-50 |
17% |
30% |
20% |
24% |
Female |
30-40 |
13% |
18% |
30% |
22% |
Female |
<30 |
3% |
12% |
40% |
21% |
|
Total |
100% |
100% |
100% |
100% |
Row totals and column totals refer to the left-most or top-most attribute on the report. That dimensionality is fixed so that the same total is still applied even after the report has been pivoted. An alternative implementation can use location-based dimensionality in the metric that changes when the report is pivoted.
Rank metrics
Rank metrics apply a ranking number to the metric values for a given attribute. When selected, this shortcut metric provides break-by options for each attribute on the report.
A rank metric is defined by the following formula: Rank(OriginalMetric).
The sort direction (Ascending) is set as a property on the rank operator node. The BreakByObject determines the (unit of the) special dimensionality (of type BreakBy) attached to the metric.
Transformation metrics
Transformation metrics apply offset values, such as “four months ago” or “last year” to the selected attribute.
Once an Identity metric is created, the transformation metric is equivalent to the original metric with additional transformation information attached such as “four months ago” or “last year.” If required, another metric is created based on the formula type, the original, and the Identity metrics. The equivalent formulae or results, based on the formula type, are as follows:
-
DssXmlMetricFormulaIdentity— IdentityMetric
-
DssXmlMetricFormulaDifference— IdentityMetric – OriginalMetric
-
DssXmlMetricFormulaVarianceRatio— (IdentityMetric – OriginalMetric) / OriginalMetric
These metric formula types are enumerated under com.microstrategy.webapi.EnumDSSXMLMetricFormulaType.