Applying thresholds

Steps to apply thresholds to a metric on a report

Thresholds allow you to alert report analysts to data that is likely to be important for making business decisions. Certain data cells on your report could assume special significance (such as Sales > 1000, or Date of Sale prior to December 1, 2027). Thresholds allow you to draw immediate attention to such data based on the result of expression evaluations.

Thresholds display conditional formatting in a report to highlight certain data cells depending on predefined criteria. The criteria are attribute or metric qualifications. If a qualification's expression evaluates to TRUE, the report displays the threshold.

You can choose different types of thresholds to highlight or replace the important data on the report:

• Format: This allows you to apply special formatting to certain displayed data; for example, in a report showing sales conversions (from leads to sales), you might display all data in green for those months when sales conversions are greater than 50%. You could also display data that falls below a certain percentage in red.

• Replace Text: This allows you to replace data with special text. For example, a report shows the financial values of various sales opportunities. For those sales opportunities that have been lost you might display the word LOST in red, rather than displaying the financial value. A common use of this option is to display the word EMPTY when a data value is null.

• Image: This allows you to replace data with a graphic image you have on your machine or network. For example, a report shows the values of open and closed sales for the past month. To focus attention on still-open sales data, you can replace the sales value data for all closed sales with the customer's company logo. A common use of this option is to display green, yellow, and red "traffic lights" (circles) to allow very quick, high-level analysis of data and immediately pinpoint specific groups of data that needs further analysis. For more information about specifying the source of the image, see Thresholds dialog box: Image options.

• Quick Symbol: This allows you to replace the normally displayed data with a common symbol; for example, in a report showing the financial contribution of various sales groups to overall sales office activity, for the monthly trend column you could show either a green plus + or a red minus – symbol to represent positive or negative contribution trends.

You can select whether the threshold formatting is applied to the selected metric, its subtotals, or to both the selected metrics and its subtotals.

You can also apply threshold conditions to a graph report; see Applying thresholds to a graph report for instructions.

After you create a threshold, it is automatically enabled so that it is displayed on the report. You can disable the threshold so that it is not displayed, but is available for future use. For more information, and instructions, see Disabling and hiding thresholds.

The image below shows the Tutorial report named Product Sales Results By Region. Thresholds are displayed for the % Change in Revenue metric, and for the Revenue metric.

For more details, examples, and images, see the Basic Reporting Help.

For information on conditional formatting in Report Services documents, which is similar to report thresholds, see Formatting conditional values in documents.

Steps to apply thresholds to a metric on a report

1. In MicroStrategy Developer, open a report. (How?)

2. Right-click the metric for which to add a threshold and choose Thresholds. The Thresholds dialog box opens, with a blank threshold named "New Threshold" displayed.

3. You can rename the new threshold by selecting New Threshold and entering the new name.

   If you selected the wrong metric, you can select a different one from the list in the toolbar, then click Add a new threshold in the toolbar.

4. Click the text Click here to start a new qualification. (A qualification can be "Region In list {Northeast, Mid-Atlantic, Southeast}" or "Profit Greater than 10000", for example.) Field, Operator, and Value replace the text.

5. Click Field to select the attribute or metric to qualify on. The list includes all the attributes and metrics on the report. You can also select Rank or Percent, and then select a metric.

6. Click Operator to select an operator, such as Exactly, Greater than, or In List. The operators available for selection depend on whether you selected an attribute or a metric as the Field.

7. Click Value to select a value. Depending on the Field and Operator chosen, the available options can be any of the following:

   • Type a value. Enter a number to compare the Fieldto.

   • Select Elements. Select the attribute elements to compare the Field to.

   • A list of the attributes on the report. Select the attribute form to compare the Field to.

   • A list of the metrics on the report. Select the metric to compare the Field to.

   • Field, Operator, and Value, if you selected Where as the Operator.

   • If you selected Is Null or Is Not Null as the Operator, Value is not available.

   • If you selected Between or Not Between for the Operator, you must select two values.

8. You can specify a second qualification for the same threshold, by repeating the steps beginning at To specify qualifications above. By default, the logical operator joining the qualifications is AND. To change it, click AND then select the new operator from the drop-down list.

9. Next, specify the type of formatting to apply to the selected metric. Click Formatand select one of the following from the drop-down list:

   • To change the formatting only, select Format. You can apply colors, patterns, or gradients to the threshold values or make them transparent.

     For example, in a report showing sales conversions (from leads to sales), you can display all data in green for those months when sales conversions are greater than 50%. You could also display data that falls below a certain percentage in red. You can also apply patterns and gradients to the values.

   • To replace the selected metric with text, select Replace Text. Type the new text in the (text) box to the right.

     For example, in a report showing the financial values of various sales opportunities, for those sales that have been lost you can display the word LOST in red, rather than displaying the financial value. A common use of this option is to display the word EMPTY when a data value is null.

   • To replace the selected metric with a symbol, click Quick Symbol. Choose the symbol from the selection in the box to the right.

     For example, in a report showing the financial contribution of various sales groups to overall sales office activity, for the monthly trend column you could show a green plus + and a red minus – symbol to represent positive or negative contribution trends. Choose the replacement symbol from the box next to the drop-down list.

   • To replace the selected metric with an image, click Image.

     1. Specify how the image is stored by clicking Absolute path and selecting one of the following from the drop-down list:

        • Absolute path (the default), for example, c:\images\img.jpg

        • Relative to HTML Document directory, that is, a relative path from the document directory where the image is stored, for example, images\img.jpg

        • On the network, that is, a path on the local area network, which is in a UNC (Universal Naming Convention) format, for example, \\machine_name\shared_folder\img.jpg

        • On the Web, that is, a URL to an image file, for example: http://www.microstrategy.com/images/img.jpg

     2. In the box below the drop-down list, type the path and name of the file or click ... (the browse button) to browse for the image file. For more information on specifying the image path, see Thresholds dialog box: Image options.

        For example, a report shows the values of open and closed sales for the past month. To focus attention on still-open sales data, you can replace the sales value data for all closed sales with the customer's company logo. A common use of this option is to display green, yellow, and red "traffic lights" (circles) to allow very quick, high-level analysis of data and immediately pinpoint specific groups of data that needs further analysis.

10. If you selected any option except Image, click Edit the threshold formatting on the toolbar to apply formatting to the metric values, replacement text, or symbol. The Format Cells dialog box opens. Select from the following procedures depending on what to format:

    • Format a number

    • Format the alignment

    • Format the font

    • Format the border

    • Format the background

11. After you specify the formatting options, click OK to return to the Thresholds dialog box. A preview of the selected formatting is displayed.

12. Select whether to apply the threshold formatting to subtotals. Click one of the following icons on the toolbar:

    • Metric Only (the default) applies the formatting to the metric selected in the report in step 2.

    • Subtotals Only applies the formatting to the selected metric's subtotals only, not the metric itself.

    • Metric And Subtotals applies the formatting to the selected metric and its corresponding subtotals.

      If a metric that is in the Report Objects pane but not on the report grid is used in a custom subtotal, the threshold formatting does not apply to the custom subtotal, even if Subtotals Only or Metric And Subtotals is selected. This occurs because the custom subtotal is defined only for the metrics on the report grid. For more information, see Defining a custom subtotal.

13. You can add another threshold to the selected metric.

    • Click the Add a new threshold icon in the toolbar. You can instead add one of the following predefined thresholds from the drop-down list (click the arrow next to the Add a new thresholdicon to access the list):

      • Highest Value: Adds a new threshold with the qualification predefined as "Rank of selected metric Top 1". Change the value (from 1).

      • Lowest Value: Adds a new threshold with the qualification predefined as "Rank of selected metric Bottom 1". Change the value (from 1).

      • Highest Percent Value: Adds a new threshold with the qualification predefined as "Percentage of selected metric Top 1". You can then change the value (from 1).

      • Lowest Percent Value: Adds a new threshold with the qualification predefined as "Percentage of selected metric Bottom 1". You can then change the value (from 1).

      2. You can rename the new threshold by selecting New Threshold and entering the new name.

      3. Finish defining the new threshold, beginning with To specify qualifications above.

14. You can add a threshold to another metric:

    1. Select the metric from the metric list in the toolbar.

    2. Click the Add a new threshold icon in the toolbar. You can instead add one of the predefined thresholds, as described in the previous step.

    3. You can rename the new threshold by selecting New Threshold and entering the new name.

    4. Finish defining the new threshold, beginning with To specify qualifications above.

15. By default, the Allow user to display and hide thresholds check box at the bottom of the dialog box is selected, which means that users can switch thresholds on and off when they view the report. Clear the check box to remove the Show/Hide Thresholds option from the Data menu and to disable the F12 button that is used to switch thresholds on and off. The Thresholds icon on the toolbar, which allows users to show or hide thresholds, in MicroStrategy Web is also disabled.

    For more information about disabling, enabling, hiding, and showing thresholds, see Disabling and hiding thresholds.

16. After you have created all the needed thresholds, click OK to save the threshold definition and close the Thresholds dialog box.

If you want to copy/paste your new threshold to additional metrics on the report, see Copying a threshold across multiple metrics.

Related Topics

• Steps to apply thresholds to a graph report

• Disabling and hiding thresholds