In this tutorial, you’ll learn how to apply conditional formatting in your paginated reports. The primary features used are the Expression Builder and the Placeholder Properties.
Conditional formatting helps highlight specific data in your report. This is especially useful when you’re dealing with tons of data.
With Expression Builder, you can program your report to emphasize critical data points, such as turning values red or changing the font style if they meet or do not meet the requirements you set.
There are two ways to open Expression Builder in Report Builder. You can also use the Placeholder Properties to apply conditional formatting. These are discussed in detail below.
Using The Expression Builder
Right-click on any cell in your table and then click Expression.
This opens the Expression builder pop-up window.
The Expression builder allows you to format any value in your paginated report. There are also specific categories for formatting that you can choose from.
Built-in Fields are used to format common field items in your report such as page numbers. You can also opt to add in the name of the user who printed the report using the UserID item.
For the Parameters category, you can refer to your code. The Fields category shows the fields available for formatting depending on your data set. In this case, it shows the fields of the Sales table.
Similarly, the Datasets and Variables categories show the datasets and variables in your report. In this case, it contains the Sales dataset while it doesn’t have any variables.
Operators are actions you can perform on the data in your report. It is composed of five sub-categories: Arithmetic, Comparison, Concatenation, Logical, and Bit Shift.
This category offers you with the most flexibility when it comes to formatting data in paginated reports.
There are also Common Functions you can use.
Clicking a specific function gives you a description and an example on how to use it. So despite the several available functions, it’s still easy to understand how to use them.
Using The Placeholder Properties
In this demonstration, Profit Margin values above 40% need to be highlighted red. To do so, you need to use an IF statement.
Open the Placeholder Properties window. You can find this by right-clicking on the field you want to format. Then, go to the Font pane. Click the fx button under the Color option.
An if statement sample argument can be found when you click the Common Functions category and then Program flow.
Copy this argument and revise it to suit the conditional format you want the data to be in. In this case, if a Profit Value Margin field is greater than 0.4, it will be red. If not, it will be black.
Once you’re done, click Okay. When you run your report, you’ll see that the conditional formatting you created has been applied to the % Profit Margin column as intended.
Accessing The Expression Builder Using The Properties Pane
Another way to access the Expression builder is by double-clicking on the value you want to edit. In the Properties pane, click the drop-down menu of the property you want to format and then click Expression.
If you want two or more columns to have the same conditional formatting, copy and paste the expression on each value.
For example, if you want values in the City column to also turn red if its profit margin values are red, paste the conditional formatting of the latter value in the former’s color expression.
You can continue to do so for other values in your report.
If you press run, you’ll see that when a Profit Margin value is red. Its City value will also be red.
***** Related Links *****
Report Builder Tutorial: How To Add A Table
How To Format Data In Report Builder
Showcase Unique Insights Using Conditional Formatting In Power BI
Conclusion
The Report Builder gives you huge flexibility when it comes to formatting data. By using Expression Builder and Placeholder Properties, you can make specific data in your report stand out.
This tutorial covers a basic example of conditional formatting in Report Builder which uses a change in colors to add distinction between values.
There are other formats that you can use. The layout and process is closely similar to that of other Microsoft products so learning advanced conditional formatting techniques will be relatively easy.
Sue
