Live Chat Icon For mobile
Live Chat Icon

How to Use Expression in Report Designer

How to Use Expression in Bold 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.

Types

Description

Documentation

Built-in -fields Built-in fields include globals and user collections. The global variables can be used to specify the report name, page number, and execution time. The user variables can be used to set the user identifier and language settings in a report. https://documentation.boldreports.com/on-premise/report-designer/compose-report/expressions/using-built-in-fields/
Operator A symbol that represents the action performed on one or more operand in an expression. https://documentation.boldreports.com/on-premise/report-designer/compose-report/expressions/using-operators/
Functions Perform some basic operations related to date, time, math, aggregate, text, and conversion functions. https://documentation.boldreports.com/on-premise/report-designer/compose-report/expressions/using-built-in-functions/
Parameters
This is a collection of parameters available in the report. Parameters are used in expressions to customize the report data and appearance based on the dynamic values at runtime.
https://documentation.boldreports.com/on-premise/report-designer/compose-report/expressions/using-report-parameters/
Dataset Fields
This is a collection of data set fields available in the report. The data set fields are used with expressions to display the actual data or manipulate the data at run time.
https://documentation.boldreports.com/on-premise/report-designer/compose-report/expressions/using-dataset-fields/

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.

Expression Builder
Expression Builder

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.

Condition Background color
Data matches with parameter value #fee18a
Data does not match with parameter value White

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

New report
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.

Add new parameter
Add new parameter

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.

Parameter dialog
Parameter dialog

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 default parameter
Add default parameter

Add table to report design

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

Add table to design surface
Add table to 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.

Add column headers
Add column headers

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.

Apply header properties
Apply header properties

Assign data

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

Assign data values
Assign data values

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.

Apply background color for details row
Apply background color for details row

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.

Expression setter
Expression setter

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

Expression builder for background color
Expression builder for background color

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.

Add expression to text editor
Add expression to text editor

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.

Expression setter button
Expression setter button

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.

Conditional formatting report preview
Conditional formatting report preview

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.

Report preview with France highlighted
Report preview with France 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.

2 comments

  1. Sharmitha
    April 22, 2020 at 12:54 pm

    Well explained and good work .

  2. Shalini
    April 22, 2020 at 1:13 pm

    Great explanation?

Leave a Reply