MicroStrategy ONE

BreakBy and SortBy parameters

In addition to the Parameters settings, many functions have BreakBy or SortBy parameters, each of which has its own individual settings:

  • BreakBy: Logical level where the calculation of values for an expression restarts. To break by an attribute or hierarchy means to restart counting values for expressions that use relative functions. Examples of relative functions are RunningStdevP, Rank, NTile, and expressions that calculate rank or percent values. The break by level must at the same level of aggregation or a higher level of aggregation used for the expression itself.

    For example, in the report shown below the Rank by Value metric ranks the revenue values. The Rank function for this metric uses a BreakBy of the Customer Region attribute, which means the rank calculation is restarted for each customer region. This ensures that the revenue is ranked by customer region, rather than ranking all of the revenues together across customer regions. While ranking all the revenues across customer regions can be useful, this report uses the BreakBy parameter to focus on revenue comparisons within each customer region.

  • SortBy: Order of the return values of an expression in relation to the order of the value or metadata object given. A sort by includes whether to sort in ascending or descending order, and which metadata object to sort by. Sort by may also be performed on the value of the subexpression, which is the input argument.

    For example, in the report shown below the FirstInRange metric returns the first profit value in a list of profit values. The FirstInRange function for this metric uses a SortBy of the Customer State attribute, which means the first value for each customer state is returned.

OLAP functions often include BreakBy and SortBy parameters. For example, Rank has a BreakBy parameter, and MovingAvg has a SortBy parameter.

A few group-value functions (First, Last, IRR, and NPV) are also defined by the SortBy parameter. The First and Last functions are used effectively to calculate subtotals.

For example, an inventory report lists the on-hand supply for each day. The report subtotals are the last day's inventory. Creating a user-defined subtotal that uses the Last function provides the last day inventory subtotal. If the SortBy parameters of the function are not set to sort by Day, the function may not provide the correct answer.