Specifying maximum and minimum values: Report limits

After a report's results are displayed, you may need to further restrict the data displayed without changing how the calculations were performed. You can limit the data displayed in a report by specifying maximum and minimum values for a given metric. These maximums and minimums determine which rows of a result set are displayed in the report, and are called report limits.

For example, the image below shows you a report that ranks all employee sales.

You want to see only the results of the top ten employees. If you apply a report limit to restrict the data displayed to the top ten employees, the data used to calculate the sales rank is not affected. Only the employees displayed changes, as shown in the image below.

A report limit is assigned to metrics that appear on the report. Report limits are defined using operators such as Between and Less Than. By default, report limits are joined by the AND operator. To change the operator, double-click the operator and select a new operator.

For more information on additional options and settings within the Report Limit Editor, click Help.

For more information on advanced operators to apply to a report limit, see Appendix B: Logical and Mathematical Operators for Filtering in the Advanced Reporting Help.

If you have MicroStrategy OLAP Services, you can apply report limits to any data on a report, not just metrics. For information on OLAP Services, see OLAP Services.

To limit the data on a report using maximums and minimums

1. Open a grid report.

   In the example below, the report shows revenue earned by each employee. You want to add a report limit to restrict data to show employees who earned revenue greater than 1,000,000.

   

2. From the Data menu, select Report Data Options. Under the Calculations category, the Report Limit subcategory displays any report limits that may already be applied to the report.
3. To apply new limits to the report data, click Modify.
4. Double-click in the Limit Definition area.

5. Click Browse and navigate to the metric you want to apply the limit to. Then click OK.

   Derived metrics cannot be used in a report limit. A report limit is a SQL engine function and therefore can only use a metric that exists in the project. A derived metric, which is created within the report, exists only in the report. To use the derived metric, re-create it as a regular metric, using the Metric Editor. For background information on derived metrics, see Derived metrics. For steps to create a metric, see Calculating data on a report: Metrics.

6. From the Operator drop-down list, select the operator you want. Examples include Between, Greater than, Less than, Exactly, and so on.

7. Enter the Value that you want the operator to apply to. In the example below, the Operator is set to Greater than, and Value is set to 1,000,000 to see only data over 1 million.

   

8. Click OK. Then click Save and Close to save the report limit.
9. Click OK to return to the report. In the image of the report below, you can see that only those employee names that earned revenue greater than 1,000,000 are displayed.

Report limits and filters: If the report has a filter, the filter is applied to the report data first, then the report limit is applied to further restrict the data returned in the report.