KB10755: How to limit the number of rows returned in a Ranking Report when there is are two rows with the same value, or a "tie" in MicroStrategy

When a report is created with a filter that should return "Top X" records (such as "Top 10 Sales," "Top 10 Cities by Revenue," etc.), Strategy may return more records than the designated number if there is a "tie" - two rows with the same value. In many cases, this is preferable as the report should contain all of the records that meet the criteria. In some situations, such as in a Report Services document, where formatting is based on exactly X rows being returned, it may be preferable to create a report which will return exactly X rows even when there is a tie. In this case, if two records tie for the Xth position, only the first will be seen.
 
The following example report contains a filter that returns a Rank of the Top Seven Orders by Cost. However, in the following image, the report displays eight rows. This is due to a tie between two orders, both for $1511.
 
These results are sorted in descending order by Cost:

 
This procedure involves creating a running sum metric to be used in a Report Limit which will return only the first X records even if more than X records are returned by the report filter.

• Create a Running Sum Metric so that a Report limit can be set to return only the first seven rows even if there is a tie.
• Enter the Metric definition RunningSum(Max(1)).
• Click Validate:
  

• Highlight the term RunningSum, right-click and go to "Running Sum Parameters:"
  

• On the Sort By tab, add the metric on which the Top X filtering is based in the report and sort in the same order as the report is sorted (in this example, Descending Order by Cost). Click OK.
• Save and close the metric. Here, it is saved as RS(Max(1)):
  

• Add the new metric to the report objects window so that it will not be visible on the report, as shown below:
  

• Go to Report Data Options in the File Menu.
• In the Calculations section, click "Report Limit" and then "Modify," as shown below:
  

• Select the new metric and set the function to Metric Value, the Operator to Less than or equal to and the Value to the number of rows to be returned on the report. This number should be equal to the number selected in the report filter (i.e., if returning Top 10 cities by revenue, select ten in the report limit qualification):
  

Now, when the report is re-executed, only seven rows are returned: