In this article, you will learn about Conditional Formatting. You will need to use the same report you used in the last lesson.
What is Conditional Formatting?
Conditional formatting 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.
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 yes or no answer will determine if the formatting is applied or not.
For example, we might ask the question “is the invoice past the due date?” If the answer is "yes", then we could highlight the invoice with a different color. If the answer is "no", then we do nothing. We can change the way something looks in real-time with conditional formatting. For example:
Example #1: Color Formatting
Use the "Customer's Invoices" report you used in the last lesson. In order to see this work, you will need to make sure there is an invoice with a due date set and it's overdue.
On the left, make room for another date type field. I also copied a new header label and called it "DUE".
On the right, go to the field list and expand the Invoice.Customer table. Again, make sure you expand the correct table. Do not expand the Invoice table.
From Invoice.Customer table, search for and drag in the DueDate field into the DetailReport1 band, right beside the TxnDate field.
With the DueDate field selected, underneath Appearance, find and expand its Formatting Rules.
Click the plus ( + ) beside Formatting Rules. This will add an unchecked formattingRule1 which you should expand.
Name this formatting rule "
Set up the Data Source and Data Member next. This makes creating the Condition easier:
Data Source = Your Method Account
Data Member = Expand the Customer table and select Invoice.Customer.
Beside Condition, select the ellipsis ( ... ) to open the Expression Editor.
Because you set up the Data Source and the Data Member, it will be easier to select the fields you want. Scroll through and double-click the DueDate field. Then type in the less than sign "
Note the red x? That's because the expression is incomplete. What do we wish to check? We want to see if the DueDate is less than the current date. How do we get the current date? There's a function for that. At the bottom, expand the Functions line and select DateTime. If you scroll through the list, you will see a number of functions related to date manipulation.
Find LocalDateTimeNow() and double-click it. This will complete the expression in the editor.
Click OK to save your condition. You have now set up a rule to apply only to entries when its due date is less than your current date and time. Now to set up how those entries will be styled. Expand the Formatting section.
Under Formatting, change the Foreground color to red.
Your formatting rule is done! However! You need to turn it on. At the moment, it's disabled. In order to enable, you need to check the checkbox by its name.
Save and Preview your report.
If all goes well, then the date of the past due invoice should be in red!
Example #2: Reusing a Formatting Rule
You may want to highlight the whole line, instead of the date. Doing so is surprisingly easy. You can apply a formatting rule to other controls in your report.
First, let's remove the formatting rule from the date.
Select the DueDate field if not already selected. Find the PastDue formatting rule and uncheck it.
Select the Detail2 band, the row that holds all the fields from the Customer.Invoice table. We will apply the formatting rule to this entire row.
With the row selected, find the PastDue formatting rule under Formatting Rules, and check it.
Save and Preview your report.
The formatting rule has now been applied to the entire row!
As you can imagine, formatting can cover far more than just coloring. You can show and hide fields and sections, you can add borders, you can change the font type, size, and style. There are a number of things you can do, and we've only touched the surface.
In the next lesson, we will create a new report and cover Charts.