The Exago Report Designer provides a Conditional Formatting tool to give cells in a report additional formatting that depends on the Contents of the cell. The Contents of a cell refers to the output data in a Data Row that is seen when a report is exported.
This can be useful for highlighting elements from a data set, outliers from a trend, or specific items of importance, just to name a few.
The Conditional Formatting tool makes use of the suite of powerful Functions that Exago provides for data analysis. This guide will walk through the Conditional tool of the Formatting Cells dialog and provide a basic example of how to apply conditional formatting to a cell in a report.
You can follow along using the free Microsoft Northwind sample database. If you have questions about how to do so, please contact your Administrator.
For this example, we'll take the following report which uses the Employees, Orders, and OrderDetails data categories.
Our goal is to apply conditional formatting to the report that will highlight in red the cells in which the Total Sales for an employee is below $100,000:
Format Cells Dialog
The Conditional Formatting tool is found within the Format Cells dialog box in the Report Designer. The dialog can be used to format as many cells as are selected at once. In addition to conditional formatting, the Format Cells dialog contains other useful tools which are detailed here.
Let's select the cell which we want to apply formatting, and click on the Format Cells button:
The following dialog box will open. Select the Conditional tab:
Each action in the Conditional tab represents a style you want to have applied if your specified condition is met. Press the Add button to add a new style. Select Foreground Color from the Action drop-down menu, and pick a nice red using the color picker or type a custom color value into the Attribute field.
NOTE: When using multiple actions, the lower one will take priority in the case of an overlap. You can re-order actions using the arrow buttons.
Now let's add the condition which, if true, will cause our color to be applied to the cell. Click the [fx] button to bring up the Formula Editor:
The left side contains our full list of data categories and data fields, and the right side contains all of Exago's built-in formulas. You can drag a data field or a formula to the Formula box, or type it in by hand. The editor also supports using parameters in the form: @[email protected]
In this case we only need to be concerned with the value of the selected cell. The formula that represents selected cell's value is CellValue().
Since this is a Conditional Formula, we need to ensure that the formula we write will resolve to either True or False. So the formula must contain at least one logical operator. The supported logical operators are as follows:
|Not Equal To||!=|
|Greater Than Or Equal To||>=|
|Less Than Or Equal To||<=|
You can also use parentheses to indicate the precedence of the parts of your statement.
NOTE: Do not use an IF statement inside a conditional formula. (Unless you know what you're doing). The conditional formula itself is the IF statement.
In our case, we'll enter:
This is the logical statement "Current Cell's Value is Less than 100000."
NOTE: Don't enter symbols or commas when writing a number. The Formula Editor may throw an error, or even interpret your number incorrectly!
Putting It All Together
Let's break down our progress so far. We've entered a conditional (If) formula on a cell, a logical statement (the current cell's value is less than 100000), and an action for the cell to take (change the text color to red).
Now press OK, save the report, and export it, and it should result in the following:
Every cell whose value is less than $100,000 has had its text color changed to red.
If you've made it this far, Congratulations! You've created your first Conditional Formula. Keep in mind that formulas can be far more extensive than what we've written. We can add multiple actions to the same cell, use data from other cells to format this one, and even add multiple conditions in the same formula box.
For a full list of supported features and further reference, see Formatting Cells.