MicroStrategy ONE

Rank

This function is used to display the ranking of values in a list relative to the other values. The calculation can restart based on attributes identified in the parameter settings. This is an OLAP function.

Unless the defaults are changed, the function ranks the values in ascending order by the value of the metric, and the rank is an integer.

Syntax

Rank <ASC, ByValue, BreakBy, NullInclude> (Argument)

Where:

  • Argument is a fact or metric representing a list numbers.
  • ASC is a TRUE/FALSE parameter that indicates the order of ranking (1 is the lowest or highest value).
  • ByValue is a TRUE/FALSE parameter that indicates whether the ranking is done by integer values (1, 2, 3, 4) or by percentage (10%, 50%, 75%, 100%).
  • BreakBy is the parameter that designates where the calculation should restart.

  • NullInclude is a parameter that determines how NULL values are included in the rank calculation.

    The NullInclude parameter only affects the rank of NULL values if the Rank function is performed by the MicroStrategy Analytical Engine. The Rank function is performed by the Analytical Engine for smart metrics, derived metrics, and other metric scenarios. To determine whether the Rank function for a metric is performed by the Analytical Engine, view the SQL statement for the report. If the metric is listed in the Analytical Engine calculation steps, this verifies that the Rank function is performed by the Analytical Engine.

    If the Rank function is performed on a database, the NullInclude parameter is ignored and NULL values are included in the rank calculation based on the database standards.

    For Rank functions that are performed by the Analytical Engine, you have the following options for this parameter:

    • 1: If you define NullInclude=1, NULL values are given a rank value equal to the number of other rank values, plus one. For example, the rank of the Profit metric in the report below ranks four separate profit values.

      There is one NULL value, which is given the rank of 4. The Rank (Profit) metric in this example uses an ascending rank. If you define the metric with a descending rank, the other rank values change but the rank value for any NULL values remains the same. This is shown in the report below.

    • -1: If you define NullInclude=-1, NULL values are given the rank value of one. For example, the reports shown below both define the Rank (Profit) metric with NullInclude=-1. The report on the left uses an ascending rank, while the report on the right uses a descending rank.

      As shown in the reports above, the NULL values for both reports are ranked with the value of one.

    • 0 (default): If you define NullInclude=0, NULL values are included in the rank calculation based on the NULL value handling defined using the Null checking for Analytical Engine VLDB property. For information on VLDB properties, including steps to access and modify them for various MicroStrategy objects, see the System Administration Guide :

      If you define the Null checking for Analytical Engine property as True, NULL values are treated as zero values in the rank calculation. For example, the report shown below ranks the NULL values with a rank of two, because zero is greater than -10 and less than 40.

      If you define the Null checking for Analytical Engine property as False, NULL values are treated as NULL values, which means NULL values are also displayed for the rank values. For example, the report shown below displays the NULL values as NULL in the rank.

Example

This example report displays customer states ranked by revenue within their regions. There are two metrics, one that ranks by value (default) and the other that ranks using a percentage. In order to have the state with the highest revenue in each region ranked as 1 in the Rank by Value metric, the Rank by Value ranking is descending. In order to have the largest number ranked at 100% in the Rank by Percent metric, the ranking is ascending (default).

The report includes the attributes Customer Region, Customer State, the metric Total Revenue (defined as Sum(Revenue)), and the two ranking metrics. The syntax for the metrics is as follows:

Rank by Value:

Rank<ASC=False,ByValue=True, BreakBy={[Customer Region]}>([Total Revenue])

Rank by Percent:

Rank<ASC=True,ByValue=False BreakBy={[Customer Region]}>([Total Revenue])

The resulting report is displayed as follows.