Working with Expressions in Reporting Services

New: 5 December 2005

In Reporting Services, expressions are used to provide dynamic flexibility for controlling the content and appearance of a report. Some expressions are created for you automatically. For example, when you create a new report and drag fields from datasets onto report items, the values of text boxes are automatically set to expressions that refer to the dataset fields by name. During report processing, each expression evaluates to a single value that replaces the expression when a report is rendered. Expressions are also used throughout the report definition to specify or calculate values for report item properties, text box properties, parameters, queries, bookmarks, document maps, filters, and group and sort definitions.

You create expressions on report items by entering text in text boxes, in properties, or through a dialog box. An expression is written in Microsoft Visual Basic. An expression begins with an equal sign (=) and consists of references to constants, operators, functions, and built-in report global collections including fields and parameters. You can also create references to .NET Framework classes, custom code, and assemblies external to report processing. For more information about creating expressions, see Creating Expressions in Reporting Services.

This topic provides an overview of where you can use expressions in report definitions. The examples in this topic are based on the AdventureWorks data set created in the tutorial, Creating a Basic Report. To get started with examples, see Expression Examples in Reporting Services.

Using Field Expressions

You can use expressions to refer to a field from a dataset and display its value in a text box. To link the retrieved data with a report item, the field expression must use the Fields global collection, the name of the field, and the Value property.

• When you drag a field from a dataset window onto a report item, a field expression is automatically added to the text box.
  Example:
  =Fields!LastName.Value
  Result:
  Reiter
Reiter
Saraiva
• You can combine data from multiple fields or constants.
  Example:
  =Fields!FirstName.Value & " " & Fields!LastName.Value
  Result:
  Tsvi Reiter
Tsvi Reiter
Jose Saraiva

For the list of all types of references that you can use in an expression, see Creating Expressions in Reporting Services.

Using Reporting Services Built-in Functions

You can use expressions to refer to a host of built-in report functions from an expression. These range from standard aggregate functions like Sum, Min, Max, and Count that are used to calculate values for a group of rows, to functions like RowNumber and RunningValue that you can use in detail rows to provide values for every row.

• Count the number of last names in the LastName field of a dataset named SalesOrder using the aggregate function Count. Although you can place the expression in the detail row of the table, you would see the same value entered in every row of the 3906 rows. Aggregate functions are designed to summarize detail rows, and are typically used in text boxes in the header or footer rows of a data region or a data region grouping.
  Example:
  ="Number of Names: " & Count(Fields!LastName.Value)
  Result:
  Number of Names: 3906
• Sum the TotalDue field using the aggregate function Sum. Place this in the footer row of the table. Note the value is unformatted.
  Example:
  ="Total Sold: " & Sum(Fields!TotalDue.Value)
  Result:
  Total Sold: 108266245.7018
• Format a numeric value using the Format function and a Microsoft .NET Framework formatting string, C.
  Example:
  ="Total Sold: " & Format(Sum(Fields!TotalDue.Value),"C")
  Result:
  Total Sold: $108,266,245.70
• Number rows using the RowNumber function.
  Example:
  =RowNumber(Nothing)
  Result:
  1
2
3
• Use RunningValue to aggregate a running total for the given scope.
  Example:
  =Format(RunningValue(Fields!TotalDue.Value,Sum,Nothing),"C")
  Result:
  $27,231.55
$28,947.73

For a full list of reporting built-in functions that can be referenced from an expression, see Using Report Functions in Expressions (Reporting Services).

Scope

Because you may have multiple groups defined, you can specify the scope an aggregate function is to use. Scopes are names that you can pass to aggregate functions to indicate how you want to calculate the aggregates. For example, for tables that have more than one group defined, you may want to refer to an outergroup from an innergroup header row. The Nothing scope tells a function to use the outermost scope. For a simple table data region with no grouping, there is only one scope.

To identify scopes that are valid for a report item grouping, select the item, open the Properties dialog box, click the Groups tab, and view the list of group labels that can be used to identify scopes.

Using .NET Framework and System Namespaces

You can use expressions to refer to the built-in namespaces for the Microsoft.VisualBasic run-time library, and .NET Framework namespaces System.Convert and System.Math. To refer to other .NET Framework CLR (common language runtime) namespaces, you must use the fully qualified namespace, for example System.Text.StringBuilder. For references to external assemblies, you need to identify the assembly through the report properties and make sure it is available on the report server. For more information, see How to: Add an Assembly Reference to a Report (Report Designer) and Referencing Assemblies in an RDL File.

Using Custom Code and Class References

You can use expressions to reference your own custom and class instances. You can do this by adding code directly to the report through the report properties or by building a custom assembly and then by referencing that assembly from within an expression. For more information, see Using Custom Code References in Expressions (Reporting Services).

Using 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. The following example displays the value in red if the value in the TotalDue field is less 10000. If the condition is not met, the text appears black.

=Iif(Fields!TotalDue.Value < 10000,"Red","Black")

To enter formatting expressions for a report item, open the Properties window and type the expression in the value field for the property you want to format. For more information about conditional formatting, see Adding Conditional Formatting.

Using Query Expressions

You can use expressions to define the query contained within a dataset. 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 Defining Report Datasets.

Using Filtering, Grouping and Sorting Expressions

You can use expressions to define filters that limit the amount of data and the type of data shown in a report. Filters are applied to data after it is retrieved from the data source. You can define filters on datasets, data regions, and data region groupings. For more information on filtering, see How to: Add a Filter (Report Designer) and Filtering Data in a Report.

You can use expressions to define groups for data regions. You frequently use expressions to calculate aggregates on grouped data. Groups are defined by creating group headers on data regions. For more information about grouping data, see Grouping Data in a Report and How to: Add a Group to a Table (Report Designer).

You can use expressions to define sorting for data regions. You can sort datasets, detail rows on a data region, and groups on a data region. For more information about sorting data, see Sorting Data in a Report and the How to: Sort Data in a Table (Report Designer).

Using Built-in Report Collections

You can use expressions to refer to built-in report collection classes. In addition to the Fields collection discussed earlier, you can create expressions that refer to items in the following collections: Globals, User, Parameters, ReportItems, DataSets, and DataSources. When used in an expression, these collections provide access to report properties and data determined dynamically during report processing. For more information, see Using Global Collections in Expressions (Reporting Services).

Using Locale Settings

You can use expressions to refer to the locale settings on a client computer through the User.Language value 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 value. 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.

See Also

Tasks

How to: Add an Expression (Report Designer)

Reference

Expression Examples in Reporting Services

Concepts

Report Layout How-to Topics

Other Resources

Edit Expression (Report Designer)

Help and Information

Getting SQL Server 2005 Assistance