Conditional formatting in Report Designer allows you to create a set of rules that dictate how your report appears. As the name suggests, based on these rules, the formatting of that information will change when certain conditions are met. The applications of this tool, which can be found in every control and band, are very far-reaching, so for the purposes of this documentation we will provide you with a few basic examples of conditional formatting, as well as a Screen overview that will identify the unique elements of each conditional formatting screen.
A note on conditions
In order to understand how conditional formatting works, it’s important to understand what we mean by “condition”. In programming terms, a condition is a question with a yes-or-no answer. The answer to the question will determine what formatting is applied. For example, we might ask the question “is the balance of this invoice equal to zero?” Using conditional formatting we can determine what actions will be taken when the question is answered “yes” or “no”. To illustrate this, let’s take a look at a few basic examples.
Note: Conditional formatting cannot be applied to summary fields.
Example #1: Do not display
Let’s assume you’ve created a simple list of customers and their associated balances. Now let’s imagine you want Report Designer to exclude any customer with a balance of zero from that list. Here’s how to do that using conditional formatting.
In the Detail band, click the arrow button to bring up the additional properties for the band. Then click on the ellipses button [...] next to Formatting Rules to bring up the Formatting Rules editor. From here you can create a new rule to apply to this report.
Click on Edit rule sheet to bring up the Formatting Rule Sheet editor, then click the (+) icon to create a new rule. This will open several options on the right-hand side of the editor, where you can alter the parameters of the rule.
Start by scrolling down to Design and typing in a new name for the rule (e.g. HideNoBalance).
If you simply want Report Designer to hide any balance of zero, scroll to Formatting, click next to Visible, and select No.
Scroll up to Condition and click the ellipses button [...] to bring up the Condition editor. This editor is similar to the Calculated Fields editor elsewhere in the designer, and more information about the specifics of this screen can be found in the Sceen overview section.
Here is where we construct our “question” or condition to be met. On the left, click on Fields. Scroll down in the middle menu and double-click Balance since this is the field in question. This is the first part of the question, and it will appear in the editor section above. Then click on the (=)icon, and right next to it in the editor section, type in 0. Your condition will now read [Balance]==0. Click Okay to continue.
Click close to return to the Formatting Rules editor. The HideNoBalance rule is now visible in the Rules available section to the left. To apply it to the Detail band of your report, click the (>) icon to move it to the Rules applied to a control section to the right. Then click Okay. This will return you to the designer screen.
The above steps have now set up the following condition (if the balance is equal to zero, it will not be visible).
When you click on Preview, you will see that any customer with a balance of zero has now vanished from the list, along with the associated zero balance. This is a great way to clean up reports, and an even better way to illustrate how conditional formatting works!
Now, let’s take a look at another way to conditionally format a report that’s a bit more complicated.
Example #2: Color formatting
Let’s assume we are using the same list of customers and balances as before. Now let’s imagine you want to organize your report so you can clearly see any customer with a negative balance. A great way to do this would be to print those balances in a visible color. Here’s how to do that using conditional formatting.
Follow step 1 and 2 from the example above. Rename your new rule (e.g. NegativeRed), then scroll up to Formatting and change the foreground color to Red.
Scroll up to Condition and click the ellipses button [...] to bring up the Condition Editor. Because you are once again asking a question related to the displayed balances, click Fields > Balance. Then click the (<) icon and type in 0. Your new condition now reads: [Balance]<0. Click Okay to continue.
Follow step 7 from above to apply your new rule to the Detail band.
The above steps have now set up the following condition. (If the balance is less than zero, the balance will be displayed in red).
As before, the Preview view will show you your changes, this time displaying all customers with associated negative balances in this color.
Note: We mentioned before that conditional formatting can be found in every control and band in Report Designer. This means you can get even more specific in applying your rules. For example: if you wanted to display only the balance amount in red and not the associated customer, you would follow the same steps but begin by clicking the arrow button in the balance field instead of the detail band. This means the changes made by the conditional formatting will only apply to the information pulled into the report by that field, whereas applying changes to the detail band will affect every field in that band.
And that’s it! You now know how to make basic use of the versatile conditional formatting tool!
Screen overview
In this section, we’ll examine the different editors that affect conditional formatting and take a closer look at the unique elements of each.
Formatting rules editor
This editor provides you a visual list of all the rules you have created for this report [Rules available in a report] and which rules you have actively applied [Rules applied to a control]. Several buttons help you navigate this section.
> Select an available rule and use this button to apply it to the control (this physically moves the rule from the left to the right).
< Select an active rule and use this button to remove it from the control (this physically moves the rule from right to the left).
>> Use this button to apply all rules to the control.
<< Use this button to remove all rules from a control.
Use the up and down arrows to scroll through available and active rules.
Edit Rule Sheet opens the Formatting Rule Sheet Editor. Ok applies the changes and closes the editor, while Cancel closes the editor without applying any changes.
Formatting rule sheet editor
This editor is where you set up what formatting is implemented if your condition is met by creating rules.
+: Create a new rule. This opens the options to the right.
x: Delete selected rule. This can’t be undone.
Clear formatting rules: Clears all changes and reverts formatting to defaults.
Categorized: This button organizes your formatting options into three categories: Behavior, Data, and Design (see below).
Alphabetized: This button organizes your formatting options alphabetically.
Formatting options are divided by default into three categories.
Behavior: This category gives you the option to open the Condition Editor, and to change Formatting settings. These settings are what will be visually applied in the report when your conditions are met. For more information on the specifics of each setting, see the Styling documentation.
Data: This category allows you to associate Data Members and Data Sources to controls.
Design: This category allows you to rename the formatting rule.
In all three categories, you can use the arrow button to collapse or expand the sections for ease of use. The Close button will close the editor.
Condition editor
This editor is accessed through the Behavior category in the Formatting Rule Sheet Editor. Click the ellipses button to open this editor.
The toolbar directly below the View Condition window are quick buttons that coincide with the Operator options below.
The left-hand window displays the elements you will use to create the condition: Functions, Operators, Fields, Constants, Variables, and Parameters.
The middle window expands on the options available within each element, and in the case of Functions, provides a filter by view drop down menu that lets you search specifically for Date/Time, Logical, Math, or String functions.
The right-hand window will display an explanation of any option selected from each of the elements (for example, clicking on the + icon under Operators will display text explaining its functionality).
In order to add an option to the View Condition window, it must be double-clicked.
OK saves the condition, which will now appear in the Condition field of the Formatting Rule Sheet Editor, and closes the Condition Editor. Cancel closes the Condition Editor without saving.