How to Use Expression in Report Designer

An expression is a short statement that represents a value. It is evaluated at runtime and dynamically sets the value of a property. An expression must begin with an equals (=) sign followed by a combination of constants, operators, data set fields, parameters, functions, or built-in fields. Expressions enable you to have more dynamic control over the content, design, and interactivity of your report.

The following table showcases the different built-in references available in Bold Reports. Using these references in an expression makes it less complex and reduces errors there by saving time.

Expression Builder

Bold Reports provides an Expression Builder that can be used to build expressions easily and quickly. The expression builder comprises a text editor at the top and built-in functions and data at the bottom. Under the Options drop-down, you can find the built-in fields, operators, and functions. On clicking any of these references, a short description with an example code snippet will be displayed on the bottom right of the Expression Builder. The Data drop-down consists of the Datasets and Parameters that are available in the report.

You can type the expression directly into the text editor or double-click on any built-in references to insert them into the text editor.

In this blog, we are going to design a conditional formatting report that dynamically formats the background color of table rows using an expression. At run time, the expression will be evaluated based on a parameter value with following conditions.

First, launch the Bold Reports On-Premise server demo site, refer to the “Steps to create a report” article, and create a new report.

Create data set

Create a data set to bind the required data to the table report item. Use the following sample query to recreate the following report-designing steps. This sample query runs against the Northwind sample database.

select CustomerID,CompanyName,Address,City,PostalCode,Country from Customers

Create parameter

Create a new parameter from the parameter panel. Set the parameter name to FormattingRow and the prompt to Highlight Country. Then click on Assign Values.

In the parameter assign dialog, select the Query Value option under the Available values category. Select the created data set from the drop-down. Choose the Country field in the Value and Label drop-downs.

Refer to the define default values documentation and set UK as the default value for the parameter. Then click OK and save the parameter.

Add table to report design

Drag a table report item from the item panel into the design surface.

Refer to the insert column and set header text documentation to add five columns to the table and set the header text as Customer ID, Company Name, City, Postal Code, and Country.

To customize header appearance, select the header row and open the properties panel. The common properties of table cells will be listed here. Customize them according to your requirement.

Assign data

Refer to the assign data documentation and assign data set fields CustomerID, CompanyName, City, PostalCode, and Country to the table cells.

Set expression

Now, let’s format the table data row by using a conditional formatting expression. Select the Details row group and open the properties panel.

Under the Appearance category, click on the small square icon on the right side of the background color property and select Expression from the menu.

Now, the Expression Builder for the background color property will open.

Enter the following expression in the text editor.

=IIf(Fields!Country.Value=Parameters!FormattingRow.Value,"#fee18a","white")

If the data set field Country matches with the FormattingRow parameter value, #fee18a will be applied as the background color, otherwise white is applied.

After adding the expression to the text editor, click OK. Once we set an expression, the expression setter button turns black and the color picker field is disabled. You can edit the property once you reset the expression.

Preview report

You can preview the report at design time using the built-in Bold Reports Viewer to ensure the report design is as expected. Switch to the preview mode. Based on the parameter’s default value, the table rows that match with the UK country name are highlighted.

You can also switch the parameter value in the parameter field drop-down to highlight the customer details of any other country. For example, select France country in the parameter drop-down and click View Report. The table rows that match with the country name will be highlighted.

You can download this report design here.

Conclusion

I hope this blog provided a clear overview on how to use expressions and customize a property’s value dynamically using expressions in Bold Reports. For additional information on the expression feature, you can go through the documentation.

If you have any questions, please post them in the comments section below. You can also contact us through our contact page, or if you already have an account, you can log in to submit your support question. Feel free to check out the Bold Reports On-Premise demos and our documentation to explore the tools and their various report customization features.

Bold Reports now comes with a 15-day free trial with no credit card information required. We welcome you to start a free trial and experience Bold Reports for yourself. Give it a try and let us know what you think!

Stay tuned to our official Twitter, Facebook, LinkedIn, Pinterest and Instagram pages for announcements about our releases.