Rules (If conditions)

You can apply conditional formatting using font color, style, icons, or background based on one or more IF conditions.

Refer to the Create Rule section to get started. Once the rule is created and you can see the Conditional Formatting side panel, follow the steps below.

STEP 1: Choose 'Rules (If conditions)' in the Format by dropdown. You will see two sections - Style and Conditions.

Choose Rules(If Conditions) in the Format By dropdown

STEP 2: Using the Style section, you can format text based on color/style, apply a background color, or select icons.

STEP 3: In the Conditions section, you can define conditions using options such as Number, Data selection, Values, Formula, and User Selection.

If conditions

STEP 4: When you have inline charts in your reports, conditional formatting rules can be applied to the chart labels or the chart itself. Select the desired option(s) from the Impact on list. Notice how the pin charts and labels are highlighted in blue when 'SalesEnhc' > 4m.

Impact on charts

Let's look at examples for each of these options.

Style

• Font style: bold, italic, underline

Set the font style based on conditional formatting rules. Notice how we've applied Bold, Italic, and Underlined the values that satisfy the condition. Click on the icon to apply a color to the underline.

Font style

• Cell background

Highlight the cells that match a conditional formatting rule by applying a background color.

Cell background

• Font color

Apply a custom font color when a conditional formatting rule is met.

Font color

• Cell borders

Highlight cells that satisfy the conditional formatting rule by setting a custom border. You can choose the border color by clicking the icon.

Cell borders

• Adding icons or text

Display icons to apply conditional formatting to your data. You can also use custom icons by clicking the Upload Icon link.

Adding icons

You can position the text/icons with respect to the cell values. You can also choose to display only the icons or text and hide the cell values.

Using icons and text

When you use icons, you have an additional option to align your icons in the grid. By default, the icons are left-aligned within the cell.

Left align icon

Left align option is disabled

• Hiding values

Business reporting may require withholding certain information to protect strategic interests. You can use conditional formatting rules to mask values based on specific criteria. All the cells with Sales > 35k have been hidden.

Hiding cell values

Conditions

1. Number

In this example, we are highlighting the sales values where sales is greater than 35k. The numbers field supports scaled entries such as 10m.

2. Data selection

You can also use a cell value in the condition.

2.1. Select 'Data selection' from the dropdown, click on the 'Select value from' textbox, and select a cell in the report. The value gets populated automatically.

Click the textbox and select the cell

Note:

• In this example, we have used 'Values and Totals' as the Row hierarchy levels. You can see a new field where you can choose whether conditional formatting needs to be applied to the row grand total or not.

The formatting is applied to all the cells where sales are greater than the selected value.

Conditional formatting based on data selection

3. Value

You can apply conditional formatting based on another measure in the visual. Let's highlight the cells where 2024 Actuals are greater than the 2023 Actuals.

Measures that were added from the data source, auto-calculated variances, columns/measures created using calculations, data input, and simulations can all be used in the IF condition.

Select 2023 Actuals from the highlighted dropdown.

Note that conditional formatting is applied only to the totals - Regions, Subregions, and Categories since we have set Row hierarchy levels to 'Totals only' and included the row grand total.

Conditional formatting based on measure comparison

4. Formula

You can create simple formulas using either numeric values or measures. Let's highlight the records where 2024 Actuals are greater than the 2024 Plan by at least 1m. Configure the condition as shown.

Cells highlighted based on formula condition

The Add condition option lets you create nested AND/OR conditions.

Let's consider another example where the conditions are January 2024 Actuals and February 2024 Actuals greater than 5m, and the Sub-regions are Pacific and East.

STEP 1: Configure the settings as shown for the first condition and click the Add condition link to enable the radio buttons for AND/OR.

Another condition with default selections is enabled, and an option to select AND/OR. Let's select 'AND' and 'Month' from the dropdown.

Adding multiple conditions

STEP 2: All the months are listed in the Choose members list. Let's select Jan and Feb.

Select the months

STEP 3: Repeat the steps to select the 'East' and 'Pacific' regions.

Select regions

Notice how the cells in the Pacific and East regions for January and February are highlighted.

Conditional formatting based on multiple conditions

5. User selection

You can apply conditional formatting based on a selection during runtime, i.e., in the reading view. Let's consider a case where we want conditional formatting to be applied to 2024 Actuals based on a Variance value selected during runtime.

STEP 1: Configure the rule as shown.

Conditional formatting based on user selection

STEP 2: Click on a value in the PL Variance measure. You can see flag icons in the 2024 Actuals column where the PY Variance is greater than the selected PL Variance.

Conditional formatting applied on user selection

6. Dates

With Inforiver Reporting Matrix, you can compare date dimensions in rows and columns and automatically format cells based on the comparison. Let's add date dimensions in the row and column parameters. To compare two date dimensions, select the Compare Date option. In the example below, we used conditional formatting to highlight the cells where the order date exceeds the ship date.

Comparing dates

In the example above, we compared each ship date against each order date and highlighted the cells with the order date greater than the ship date. Instead of comparing each cell, you can also compare a date dimension with the minimum or maximum value of another date dimension. To demonstrate this, let's highlight the cells with the order date greater than the minimum ship date.

Highlight cells where order date is greater than minimum of ship date

You can compare date dimensions against static dates using the Selected Date option and specify the date to be compared against.

Compare dates against static dates

7. Conditional formatting for data input fields

Highlight your numeric data input fields with conditional formatting. When users enter values, you can specify rules to automatically apply formatting when the rules are satisfied, e.g., spotlight the cells when the budget entered exceeds a certain limit.

Highlight cells when users enter budget values that are greater than sales

8. Rules for non-numeric measures

You can apply conditional formatting to non-numeric measures using conditions like is blank/is not blank/contains/does not contain, etc. In this example, we've set a hatched background for the cells with blank customer names.

Applying rules to and creating conditions with non numeric measures

Even direct comparison of the texts in the Data input measures is possible using Conditional Formatting.

Comparison of 2 Data input measure columns are shown.