Formatting conditional data in documents

Certain data on your document can assume special significance (such as Sales > 1000, or Date of Sale prior to December 1, 2023). Conditional formatting allows you to draw immediate attention to such data based on the result of an expression evaluation. If an expression evaluates to TRUE, the control can be:

• Hidden.

• Formatted in a particular way, for example, bold red font for Sales below $1000.

• Replaced by text, such as East for Regions in the eastern US. This replacement text can be formatted in a particular way.

• Replaced by a symbol, such as a diamond for Sales above $100,000. These replacement symbols can also be formatted.

Conditional formatting allows you to format specified data in your document depending on predefined criteria. The criteria are defined in attribute or metric qualifications (also known as conditions), such as Customer Order Amount >100 or City = Miami. Conditional formatting allows the data in controls and document sections to be formatted by data-driven conditions. Like thresholds in reports, conditional formatting allows you to alert document recipients to data that is likely to be important for making business decisions. Conditional formatting provides the flexibility to personalize documents based on the data returned by the dataset reports.

For example, a document contains cost, profit, and revenue by region and employee, and is grouped by region. The document needs to provide a quick summary of the profit information, so the profit data must be replaced with a green diamond if the profit is $100,000 or greater, and a red stop sign (that is, a hexagon) if it is less. The final document looks like the following:

Conditional formatting for control types

A conditional format is the combination of a control, a condition, and formatting properties which are applied to the control's data that meets the condition. Objects of all control types except Grid/Graph can use conditional formatting. The following table lists the control types and the types of conditional formatting available for each.

Control Type	Conditional Formatting
Image	Hide object Formatting: Borders
Line	Hide object Formatting: Line style, color, and weight
Rectangle	Hide object Formatting: Background color; Line style, color, and weight
Text field	Hide object Replacement text and symbols Formatting: All formatting properties
Section	Hide object Formatting: Background color
Grid/Graph	Replacement text, symbols, and images Formatting: All formatting properties You can apply conditional formatting (or thresholds) to an object in a Grid/Graph, but the method is slightly different. For instructions, see Applying thresholds.

Derived metrics and summary metrics can be used to create conditional formatting; calculated expressions cannot. For more details, see Derived metrics, summary metrics, and attributes in conditional formatting.

Conditional formatting on selector totals

Selectors provide interactivity to Report Services (RS) dashboards, allowing each user to change how he sees the data. A selector can change panels, the focus of a Grid/Graph, or dynamic text fields (a text field that is a reference to an object on a report) in a panel stack. Selectors that contain attribute, custom group, or consolidation elements as selector items can also include an option to display totals. The total is calculated for all the selector items. A user can choose whether to display specific elements, all of the elements at the same time, or the totals. For instructions to add selector totals, see Showing totals in selectors.

In MicroStrategy Developer and in MicroStrategy Web, if the target is a Grid/Graph, you can select whether to apply conditional formatting to metrics only, to metrics and their corresponding selector totals, or to the subtotals only. In MicroStrategy Web, if the target is a text field, you can select whether to apply conditional formatting to metrics only, or to metrics and their corresponding selector totals. (See the Document Creation Help for instructions.) When you create a conditional format in MicroStrategy Developer, if the target is a text field, the conditional format is not applied to the total, but rather to the metric values only.

For example, a document contains the Region attribute and the Revenue metric on a panel stack. The panel stack is targeted by a selector, which allows a user to choose the region to display in the panel stack. The selector includes the option to display the total, which is calculated for all the regions. The conditional formatting on the Revenue metric displays low revenue in red and high revenue in green. The conditional formatting can be applied to the regional revenue values only (that is, the metric only), or to both the regional revenue values and the total value.

Showing and hiding conditional formatting

You can choose whether to show or hide each of the conditional formats that you create. You can also determine whether to allow user to switch conditional formatting on and off for the entire document. For details and procedures, see Showing and hiding conditional formatting.

To apply conditional formatting

1. Open the document in the Document Editor. How?

2. From the Format menu, choose Conditional Formatting. The Conditional Formatting dialog box opens.

   Conditional formatting on an object in a Grid/Graph is slightly different. You must first edit the Grid/Graph before right-clicking the metric for which to add a threshold, and choosing Thresholds. The Thresholds dialog box opens. For further instructions, see Applying thresholds.

3. In the drop-down list at the far left of the toolbar, select the control or section to apply conditional formatting to.

4. In the toolbar, click Add a new threshold to add a new conditional format definition. The new conditional format appears below the toolbar, labeled New Threshold.

5. You can rename the new conditional format by selecting New Threshold and typing the new name.

6. In the conditional format box that you just created, click the text Click here to start a new qualification. A qualification is the actual condition that must be met for the conditional formatting to be applied. Examples are "Region In list {Northeast, Mid-Atlantic, Southeast}" or "Profit Greater than 10000."

7. Click Field, then select the attribute or metric to qualify on. The list includes all the attributes and metrics in all of the datasets on the document.

   The field named Selected Text Field is the current text field object to which the condition is being applied.

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

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

   • A box to enter numbers or text, such as 1000 or Northeast (you must enter two values if Between or Not Between is selected as the Operator)

   • A list of attribute elements

   • If Where is chosen as the Operator, you must specify a Field (such as ID and DESC), an Operator (as described above), and a Value

     If NULL or Not NULL is chosen, Value is not available.

10. To specify a second qualification for the same conditional format, click the text Click here to start a new qualification in the same conditional format box. Follow these steps again, beginning at the Specify a qualification heading. By default, the operator joining the qualifications is AND. To change it, click AND, then select the new operator from the drop-down list.

11. By default, the control or section is formatted. You can change the behavior by doing one of the following:

    • To hide the control or section when the qualification evaluates to true, click Format in the middle of the conditional format box and select Hide object from the list.

      You cannot select other formatting options; skip to step 13.

    • To replace the text on the document with new text:

    • Click Format in the middle of the conditional format box and select Replace Text.

    • Enter the new text in the box next to the drop-down list.

    • To replace the text on the document with a symbol:

    • Click Format in the middle of the conditional format box and select Quick Symbol.

    • Choose the replacement symbol from the box next to the drop-down list.

12. To format the control, replacement text, or replacement symbol (optional unless Format is selected):

    • On the toolbar, click Edit the threshold formatting . The Format Cells dialog box opens, allowing you to apply number, alignment, font, border, and background formatting, depending on the control type being formatted.

    • Click OK to return to the Conditional Formatting dialog box.

    • Save the conditional formatting

13. Click OK to save the conditional formatting and return to the document.

Related Topics

• Derived metrics, summary metrics, and attributes in conditional formatting

• Showing and hiding conditional formatting

For examples of conditional formatting, see the Document Creation Help.