Scroll

Conditionally Format Cells in a Report

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.

image001.png

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:

image002.png

The following dialog box will open. Select the Conditional tab:

image003.png

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.

Formula Editor

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:

image004.png

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: @ParameterName@

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:

Operator Symbol
Greater Than >
Less Than <
Equal To =
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:

CellValue()<100000

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.


Hidden Article Information

Article Author
Alex Agoado
created 2016-01-08 20:30:09 UTC
updated 2017-05-25 16:17:57 UTC

Labels
formulas, Report Designer, parameters, formatting, conditional formatting,
Have more questions? Submit a request