Using Global Collections in Expressions (Reporting Services)

Reporting Services provides the following global collections that you can reference from expressions: DataSources, DataSets, Fields, Globals, Parameters, ReportItems, and User. To refer to a collection from an expression, you can use standard Visual Basic syntax for an item in a collection. The following list provides examples of this syntax:

• Collection!ObjectName.Property or Collection!ObjectName("Property")
  =Fields!Sales.Value
=Fields!Sales("Value")
• Collection("ObjectName").Property
  =Fields("Sales").Value
• Collection("Member")
  =User("Language")
• Collection.Member
  =User.Language

Global Collections Summary

The following table describes each global collection and notes when you can reference the collection from an expression. Each global collection is discussed in a separate topic later in this section.

Members of the Globals and Users collections return variants. If you want to use a global variable in an expression that requires a specific data type, you must first cast the variable. For example, to convert the execution time variant into a Date format, use CDate(Globals!ExecutionTime). For more examples, see Expression Examples in Reporting Services.

Fields

Each dataset in a report contains one Fields collection. This is the collection of fields you see when you expand a dataset node in the Datasets window. The Fields collection is typically used to display data in text boxes in a report, but it can also be used in other report items, properties, and functions. Field names must be unique within a collection, but the same field name can exist in multiple collections.

Some data processing extensions automatically determine the field collection from the query you define for a dataset. When you click the Refresh Fields button on the toolbar, changes are saved to the report definition, and the Datasets window is updated to show those changes. To edit field definitions, in Report Designer in Data view, select the dataset to open the Dataset dialog box, and click the Fields tab. To add a calculated field, right-click the dataset in the Datasets window to open the Add New Field dialog box, type in a name. Then click Calculated field and enter the expression.

During report processing, as each report item is processed in turn, the Fields collection associated with the current report item is bound to the underlying source data. The data returned for a specific field can be a single value or a value for each row in the dataset as the report is processed. Whether you get a single value or multiple values is determined by the type of report item that contains the field reference. References from a data region (table, matrix, list, or chart) return a value for each row in the dataset. References that are not part of a data region return a single value.

When you use an aggregate report function to process the field collection in a dataset, you include a scope parameter that specifies the dataset. To verify a report item's association with a dataset, select the report item and view its properties. For more information about report functions and the scope parameter, see Using Report Functions in Expressions (Reporting Services).

By default, items in the Fields collection have two properties: Value and IsMissing. When you drag a field from the Datasets window onto a report item on the report layout, the contents of the report item is set to the Value property of the field by default. For a data region, the value for each row in the dataset is set to =Fields!FieldName.Value. The contents of a report item that is not a data region is set to a single value such as =First(Fields!FieldName.Value).

The IsMissing property indicates whether a field that is defined for a dataset is contained in the fields retrieved from the data source. This field can be tested in a field expression using Visual Basic.. The Value property for missing fields is null (Nothing in Visual Basic). The following example, when placed in the report item text box in which you want to display the field data, shows you how to test for a missing field:

=IIF(Fields!FieldName.IsMissing, "Field was not returned by query", Fields!FieldName.Value))

Because the Visual Basic function IIF evaluates all elements of a statement before returning the result, references to a missing field may cause processing errors. The following example shows how you can make Visual Basic function calls that refer to a field that may be missing:

=IIF(Sum(Fields!FieldName.Value) IS NOTHING, 0, Sum(Fields!FieldName.Value))

When you use the expression editor to help you build an expression that refers to a field, you can select the Fields collection in the first pane and see the list of available fields in the last pane. The following figure shows the field FirstName for a dataset named Employees.

Notice the list of properties available in the IntelliSense window. This list includes the default properties Value and IsMissing. The remaining properties are predefined extended field properties supported by other data processing extensions.

Extended Field Properties

Extended field properties are additional properties defined on a field by the data processing extension. A subset of extended field properties are predefined. The predefined properties are commonly used field properties. The Key field and the UniqueName field are typical examples. These predefined properties appear in the IntelliSense list and can be referenced using the collection syntax Fields!FieldName.Property. A data processing extension may choose to take advantage of predefined properties by optimizing queries that include this syntax.

You can also refer to other extended field properties supported by a specific data processing extension. These properties do not appear in the Expressions window, but can be determined through exploring a data source through a registered data processing extension and its associated query designer. Any extended field property can be referenced using the collection syntax Fields!FieldName("Property").

If a data provider does not support extended field properties, or if the field is not found when the query is executed, the value for the property is null (Nothing in Visual Basic) for properties of type String and Object, and zero (0) for properties of type Integer.

If a data extension provides custom field properties, those properties are visible in the Expression Editor drop-down list for all fields in the dataset, whether or not a field supports that property. If you add a reference to a property that is not supported for that field, the reference evaluates to null (Nothing in Visual Basic). For examples of providers that support extended field properties, see Defining Report Datasets for Analysis Services Multidimensional and Data Mining Prediction Data and Defining Report Datasets for Multidimensional Data from an SAP NetWeaver BI System.

Globals

The Globals collection contains the global variables for the report. The following table describes the members of the Globals collection.

Examples of global variables are as follows:

• This expression, placed in a text box in the footer of a report, provides the page number and total pages in the report:
  =Globals.PageNumber & " of " & Globals.TotalPages
• This expression provides the name of the report and the time it was run. The time is formatted with the Microsoft .NET Framework formatting string for short date:
  =Globals.ReportName & ", dated " & Format(Globals.ExecutionTime, "d")

Parameters

The Parameters collection contains the report parameter objects within the report. Parameters can be passed to queries, used in filters, or used in other functions that alter the report appearance and content based on the parameter. When you define query parameters, they are automatically added to the report parameter collection. The following table describes the properties defined on a Parameter: Count, IsMultiValue, Value and Label.

Parameters can be accessed through either the property syntax or the collection syntax. The following expression shows how to access every member of the Parameters collection and, when placed in a text box in a report item, displays the contents of the parameter ProductSubCategory.

="IsMultiValue: " & Parameters!ProductSubCategory.IsMultiValue.ToString() 
& " Count:" & Parameters!ProductSubCategory.Count & " Values: " 
& Join(Parameters!ProductSubCategory.Value,", ") & " Labels: " 
& Join(Parameters!ProductSubCategory.Label,", ")

For more information, see Working with Parameters in Reporting Services.

ReportItems

The ReportItems collection contains the text boxes within the report. Items within the ReportItems collection have only one property: Value. The value for a ReportItems item can be used to display or calculate data from another field in the report. To access the value of the current text box, you can use the Visual Basic built-in global Me.Value or simply Value. In report functions such as First and aggregate functions, however, use the fully qualified syntax.

For example:

• This expression, placed in a text box, displays the value of a ReportItem text box named Textbox1:
  =ReportItems("Textbox1").Value
• This expression, placed in a ReportItem text box Color property, displays the text in black when the value is > 0 and otherwise, in red:
  =iif(Me.Value > 0,"Black","Red")
• This expression, placed in a text box in the page header or page footer, displays the first value per page of the rendered report, for a text box named LastName:
  =First(ReportItems("LastName").Value)

User

The User collection contains data about the user who is running the report. You can use this collection to filter the data that appears in a report, for example, showing only the data of the current user, or to display the UserID, for example, in a report title. The following expression, placed in a text box, combines static text with variable user data:

=Globals!ReportName & " for " & User!UserID

The following table describes the members of the User collection.

In some cases, reports that include the User!UserID variable will fail to show report data that is specific to the current user who is viewing the report. The following conditions must be present in order for this failure to occur:

• The report must be a report execution snapshot or a report history snapshot.
• The report must be accessed programmatically through SOAP methods or through the report URL address.

When this failure occurs, user profile information in the report snapshot will be based on either of the following:

• The identity of the user who created the report snapshot.
• The account that the report server runs under if the snapshot is created by the report server as a scheduled operation.

User profile failures occur only for report snapshots that are opened through SOAP calls or through the URL address of the report snapshot. User!UserID is fully functional for reports that run on-demand and for all report snapshots that are opened from Report Manager. For more information about how report snapshots are called from Web service methods and URL access, see Using a URL to Access Report Server Items and Identifying Execution State.

DataSources

The DataSources collection represents the data sources referenced in a published report definition. You may get different values for these properties in preview mode than when you deploy and view a published report.

The following table describes the variables within the DataSources collection.

DataSets

The DataSets collection represents the datasets referenced in a report definition. This global collection is rarely used directly in reports but is available for report designers to access the query command used to retrieve data from a data source for each dataset.

The following table describes the members of the DataSets collection.

See Also

Tasks

How to: Add an Expression (Report Designer)

Concepts

Understanding Report Layout and Rendering
Report Design Basics

Help and Information

Getting SQL Server 2005 Assistance

Change History