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.

Global Collection	Example	Description
Fields	=Fields!Sales.Value	Represents the collection of fields of the dataset that are available to the report. Available after data is retrieved from a data source into a dataset.
ReportItems	=ReportItems("Textbox1").Value	Represents the collection of text boxes for the report item, such as the text boxes contained within a table data region, page header, or page footer. Available during report processing.
Globals	=Globals.ReportName	Represents global variables useful for reports, such as the report name or page number. Always available.
Parameters	=Parameters("ReportMonth").Value - or - =Parameters!ReportYear.Value	Represents the collection of report parameters, each of which can be single-valued or multivalued. Not available until processing initialization is complete. When you create query parameters, they are automatically added to the report parameter collection.
User	=User.UserID - or - User.Language	Represents a collection of data about the user running the report, such as the language setting or the user ID. Always available. User!UserID is frequently used to filter results in reports.
DataSources	=DataSources("AdventureWorks").Type	Represents the collection of data sources referenced from within the body of a report. Does not include data sources used only in page headers or page footers. Not available in local preview.
DataSets	=DataSets("TopEmployees").CommandText	Represents the collection of datasets referenced from the body of a report definition. Does not include data sources used only in page headers or page footers. Not available in local preview.

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.

Member	Type	Description
ExecutionTime	DateTime	The date and time that the report began to run.
PageNumber	Integer	The current page number. Can be used only in page header and footer.
ReportFolder	String	The full path to the folder containing the report. This does not include the report server URL.
ReportName	String	The name of the report as it is stored in the report server database.
ReportServerUrl	String	The URL of the report server on which the report is being run.
TotalPages	Integer	The total number of pages in the report. Can be used only in page header and footer.

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.

Property	Description
Count	An integer that indicates the number of parameters in the collection.
IsMultiValue	A Boolean that identifies this parameter as having a set of values.
Value	The value for the parameter. For a multivalued parameter, Value returns an array of objects.
Label	A user-friendly label for the parameter. If no label is specified, the value of the Label property equals the Value property. If more than one Label is associated with the same Value, the first matching Label is used. For a multivalued parameter, Label returns an array of objects.

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.

Member	Type	Description
Language	String	The language of the user running the report. For example, en-US.
UserID	String	The ID of the user running the report. If you are using Windows authentication, this value is the domain account of the current user. The value is determined by the Reporting Services security extension, which can use Windows authentication or custom authentication.

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.

Important

Failure to update a report snapshot based on user profile information causes incorrect data and introduces a security risk if sensitive or confidential data is included in the snapshot.

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.

Variable	Type	Description
DataSourceReference	String	The full path of the data source on the report server. For example, /DataSources/AdventureWorks.
Type	String	The type of data provider for the data source. For example, SQL.

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.

Member	Type	Description
CommandText	String	For database data sources, this is the query used to retrieve data from the data source. If the query is an expression, this is the evaluated expression.
RewrittenCommandText	String	The data provider's expanded CommandText value. This is typically used for reports with query parameters that are mapped to report parameters. The data provider sets this property when expanding the command text parameter references into the constant values selected for the mapped report parameters.

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

Release	History
14 April 2006	Changed content: Expanded Fields content.
5 December 2005	New content: Added Datasets and DataSources collections. Changed content: Modified global syntax examples.