Often, it is useful to modify the format of data when a certain condition is true. This type of formatting is called conditional formatting. For example, you can display sales numbers in red if the value is below target, and in black if the value is above target.
You also can change the format of data in a column according to the values in another column. For example, in a report that shows customer names and the number of days each customer’s invoice is past due, you can highlight in blue any customer name that has an invoice past‑due value that is between 60 and 90 days. Then, you can highlight in red and bold any customer name that has an invoice past‑due value that is greater than 90 days.
Specifying a condition
To use conditional formatting, create a rule defining when and how to change the appearance of data. Only the data in columns supports conditional formatting. The condition part of a conditional formatting rule is an If expression that must evaluate to True. For example:
If the order total is less than 1000
If the customer credit limit is between 10000 and 20000
How to set conditional formats
1 Select the column containing the data you want to format.
2 Choose Format➛Conditional Formatting from the context menu.
3 In Conditional Formatting, create a rule to specify:
The format to apply. Choose Format to select formatting options.
The condition that must be true to apply the format, for example, Profit Greater than or Equal to 2000. For more information about specifying a condition, see the next section.
Figure 7‑5 shows an example of a rule specified for a selected column, Profit.
Figure 7‑5 Conditional Formatting displaying a rule
4 Choose OK. Figure 7‑6 shows the conditional formatting applied to the report. In the Profit column, numbers greater than 2000 are displayed in bold. If the column contains aggregate data, aggregate values do not reflect the applied conditional formatting properties.
Figure 7‑6 A BIRT design displaying conditional formatting
Comparing to a literal value
The conditional expression shown in Figure 7‑7 compares each value in the Profit column to literal values that you type. Alternatively, you can select from a list of values from the Profit column. Selecting from a list of values is useful if the comparison value is a product name, and you do not know the exact product names, or if the comparison value is a date, and you do not know the date format to type. If the comparison value is a date, Report Studio also provides a calendar tool, which you can use to select a date.
Figure 7‑7 Defining a condition that uses the Between operator
How to select a comparison value from a list of values
1 On Conditional Formatting, below the field that takes a comparison value, choose Change Value.
2 In Value, select Specify literal value, and choose Select Values. The values in the selected column appear, as shown in Figure 7‑8. This example shows values in a profit column.
Figure 7‑8 Selecting a comparison value
3 If there are too many values in the list, you can type a string in Filter Text to search for values that begin with that string. For example, you can type 18 to view all profit values that begin with 18. You cannot search for strings that appear in the middle of a name.
4 Select a value from the list, then choose OK. The value appears in the comparison value field on Conditional Formatting.
Comparing to a value in another column
In a conditional expression, you can compare the values of one column with the values of another column. Figure 7‑9 shows a condition comparing the sale price value with the MSRP value. If the sale price value is greater, the product name appears in bold and underlined.
Figure 7‑9 Creating a rule that compares values in two columns
How to compare to a value in another column
1 In Conditional Formatting, choose Change Value.
2 In Value, select Use value from data field. A list of columns used in the report appears.
3 Select a column from the list, then choose OK. Conditional Formatting displays the column name in the comparison value field.
Figure 7‑10 shows the BIRT design with conditional formatting applied.
Figure 7‑10 A BIRT design with conditional formatting
Specifying multiple conditional formatting rules
You can create up to three conditional formatting rules for a single column, evaluated in the order in which they appear in the list of rules. You can, for example, create three rules to set the values of a profit column to one of three colors, depending on the dollar amount. Figure 7‑11 shows an example.
When creating multiple rules for a column, be careful that the conditions do not cover overlapping values.
The format to apply. Choose Format to select formatting options.
