Manipulating Data with Expressions (Reporting Services)
Reporting Services supports expressions that are written in Microsoft Visual Basic. You can use these expressions to calculate the value of a report item, or to calculate values for style and formatting properties or other report item properties.
Field Expressions
The most basic type of expression is one that displays a field value in a text box. This is called a field expression. To link a database field to a report item, the expression must include the Fields collection, the name of the field, and the Value property. The value is created for you automatically when you drag the field to the report. The following example shows an expression that displays a product name in a text box:
=Fields!Product.Value
An expression can be a short expression that refers to a field object or a long expression that supports decision functions or formatting based on fields or other report items. Expressions in report items and properties must begin with an equal character (=). If you do not begin text with this character, the text will be evaluated as the actual value of the field.
Examples of field expressions are as follows:
This expression concatenates the FirstName field and the LastName field.
=Fields!FirstName.Value & " " & Fields!LastName.ValueThis expression performs a sum aggregation on the LineTotal field.
=Sum(Fields!LineTotal.Value)
For more information about the Fields collection, see Using Global Collections in Expressions (Reporting Services).
Conditional Formatting
You can use expressions to control the appearance of a report item. For example, you can write an expression for the Color property of a text box that displays the data in a different color depending on the data. An example of conditional formatting is as follows.
This expression, used in the Color property of a text box, displays the value in red if the value in the Cost field is greater than the value in the Revenue field. If the condition is not met, the text appears black.
=IIf(Fields!Cost.Value > Fields!Revenue.Value, "Red", "Black")
For more information about conditional formatting, see Adding Conditional Formatting.
Reporting Services Functions
Reporting Services provides a host of built-in functions that you can use in expressions. These include standard aggregate functions like Sum, Min, Max, and Count, in addition to functions like RowNumber and RunningValue. For a full list of functions with descriptions, see Using Functions in Reporting Services.
Class References and Custom Code
Expressions within a report can also contain references to the classes within the Microsoft.VisualBasic, System.Convert, and System.Math namespaces. If you use classes or functions from other system namespaces, you must use the full namespace, for example, System.Collections.ArrayList.
If you need additional functionality not provided by the Reporting Services functions or by the default classes, you can use custom code, another standard assembly, or a report code block. You do this by building a custom assembly and then by referencing that assembly from within an expression. For more information, see Writing Custom Code for Reporting Services.
Query Expressions
The query contained within a dataset can be an expression. You can use this feature to design reports in which the query changes based on input from the user, data in other datasets, or other variables. For more information about queries, see Querying a Data Source.
Using Locale Settings
You can use the locale settings on a client computer to determine how a report appears to the user. For example, you can create a report that uses a different query expression based on the locale information returned by the User.Language global variable. The query may change to retrieve localized information from a different column depending on the language returned. You can also use an expression in the language settings of the report or report items based on this variable.
While you can change the language settings of a report, you must be careful about any display issues this may cause. For example, changing the locale setting of the report can change the date format in the report, but it can also change the currency format. Unless there is a conversion process in place for the currency, this may cause the incorrect currency symbol to be displayed in the report. To avoid this, set the language information on the individual items that you want to change, or set the item with the currency data to a specific language.
See Also
Other Resources
Using Expressions in Reporting Services