Expressions

You can use an expression to set the value of a control in the report or set conditions under which certain styles apply. You can set expressions through the Expression Editor dialog while setting values in the Properties pane. All expressions begin with an equal sign (=).

Even the expression for a field value for a TextBox is set as follows:

=Fields!LastName.Value

Expression Editor Dialog

This Expression Editor dialog allows you to select from a number of fields available to the report as well as to a particular property. You can access the Expression Editor by selecting nearly any property of a control and selecting <Expression...> from the drop-down list.

Follow the steps given below to create an expression in the Expression Editor.

1. From the Fields pane, select a field you want to use in your expression. Following are the types of fields available in the Expression Editor:

   • Constants - Available for properties which have enumerated values such as TextDecoration or BorderStyle.

   • Common Values - Run time values available to all properties in every report. You can directly select these common values like Page Number, Report Name, and others from the Explorer onto the design surface or add and modify the values from the Expression Editor. The description of fields under Common Values is available in the Expression Editor.

   • Parameters - Fields available in reports which contain report parameters. If available, you can choose a parameter from this field to retrieve the current value of the parameter.

   • Data Sets - All fields in each dataset are associated with the report. However, the report retrieves only the sum or the first value of any field that is not within the current dataset scope.

   • Operations - Arithmetic, comparison, concatenation, logical/bitwise, bit shift operators for creating custom expressions.

   • Common Functions - Functions to perform actions on data in data regions. Expand the Common Functions node to view the available functions with their Syntax, Description, and Example.

   Note that all functions have a Scope parameter which determines the grouping, data region, or dataset to be considered when calculating the aggregate or other function. Within a data region, the Scope parameter's default value is the innermost grouping to which the report control belongs. Alternately, you can specify the name of another grouping, dataset, or data region, or specify Nothing, which sets it to the outermost data region to which the report control belongs. The Scope parameter must be a data region, grouping, or dataset that directly or indirectly contains the report control using the function in its expression.

   • Document Map - The DocumentMap.Path expression defines labels for the report's TableOfContents members. An example of such expression is "=DocumentMap.Path & " General Information". If this expression is defined in the Label property of the report's control associated with the report's TableOfContents, General Information will be displayed as the label of the corresponding report's TableOfContents member.

   • Theme - Available themes can be viewed and set.

   • Report Items – All the TextBox controls from report items (Table or Tablix data regions) or on the design area.

2. Double click to add the selected field to the Expression pane. The expression pane shows the fields in a valid expression format. You can remove the expressions in the editor.

3. Click Save to save the expression or cancel to close without saving. The expression appears as the property value in the properties grid.

Using Expressions in Reports

In the raw form, your data may not be ideally suited for display in a report. You can customize it and bring it into shape using expressions. The following are some examples of how expressions are set in different scenarios.

Concatenating Fields and Strings

You can concatenate fields with strings and with other fields. For example, use the following expression to get a result like Customer Name: Bossert, Lewis.

="Customer Name: " & Fields!LastName.Value & "," & Fields!FirstName.Value

Conditional Formatting

Use expressions in properties like Color, Font, Border, etc. on specific field values based on a condition, to highlight a part of data. The formula for conditional formatting is:

=iif( Fields!YourFieldName.Value operator "Value to compare", "If the condition is met, use this value.", "If not, use this one."

For example, if you enter the following expression in the Font > FontWeight property of a text box that displays the names of people, you get the name "Denise" in bold.

=iif(Fields!FirstName.Value = "Denise", "Bold", "Normal")

Functions

A number of aggregate and other functions are available for use in your expressions. A range of functions including running value, population standard variance, standard deviation, count, minimum, and maximum are available. For example, use the following expression to get a count of employees.

=Count(Fields!EmployeeID.Value, Nothing)