Collections


SSRS provides five read-only global collections to draw information from as it processes reports . There are four valid ways to access collection items:

 =Collection!{ItemName}.Value =Collection.Item("{ItemName}") .Value =Collection.ObjectName =Collection("!{ItemName}") .Value 

Thus =Fields.("FirstName").Value is the same as =Fields!FirstName.Value and the same as =Fields.Item("FirstName").Value .

Note

All objects in the SSRS object model are read-only. Thus, it is not possible to assign a value to a parameter, for example, during report processing.


ReportItems

The ReportItems collection is simply a list of text boxes on the report. Only the Value property of a text box can be accessed. By accessing these values, developers can use them in calculating derived values.

Note

Keep in mind that text boxes can be hidden from the user . By using invisible text boxes, you can acquire data at runtime and use them to process values for other visible items.


For example, the value of textbox1 can be accessed with the following expression using the ReportItems collection:

 =ReportIems!Textbox1.Value 

The use of ReportItems enabled developers to create page summaries. For example, the following expression creates a summary of values displayed in textbox1 :

 =Sum(ReportItems!textbox1.Value) 

In addition, ReportItems can be used in complex expressions involving several items. ReportItems can also be passed to and used in a code, such as in the following example:

 Function Incr(ReportItems As Object) '***This function increments passed parameter   return ReportItems("textbox1").Value+1 End Function 

Although a ReportItems item can be used to access data from any text box in the report, to access the value of the current text box, use Me.Value or simply Value .

The need to use Value might arise, for example, for conditional formatting. The following expression could be used in the property BackgroundColor :

 =IIF(Value < 0, "Red", "White") 

Fields

The Fields collection contains references to fields within a data set. The most typical use of the Fields collection is to display data in text boxes; however, they can be used in calculating formulas for other properties and functions.

All fields have names. When a data set is created, Report Designer executes a query and retrieves column metadata, including names and a populated Fields collection for this data set with names retrieved. Fields can be either mapped to columns of a query or calculated by SSRS. Calculated fields are expressions (such as adding two database fields together) and are often used when it either adds elegance to a report or the query language is not powerful enough to retrieve calculated fields (infrequent occurrence with modern enterprise databases).

Report Designer allows one to subsequently add new fields (right-click on a data set where you want to add fields and select Add from the shortcut menu) or modify/delete existing fields (right-click on the field to modify and select Edit or Delete from the shortcut menu). The preceding steps assume that the Datasets window is visible; to open it from a main menu, navigate to View, Datasets.

Report Designer allows one to drag fields from the Datasets window to a report. If the report creator drops a field to a table or matrix, a corresponding cell is filled with an expression, based on the field. If a field is dropped outside of the matrix or table, a text box is created.

Recall that the RDL schema allows each data set to have its own set of fields. The result of this is that while authoring reports, you will have multiple sets of Fields collections. The important thing to remember is that each data region can only have a reference to one data set, and, hence, one set of fields that belongs to that data set. The data set name that the Fields collection refers to is actually a property of the data region, and not of the Fields collections.

SSRS must be able to match { FieldName } to an appropriate data set or the scope has to be explicitly defined. For example, if a report has a single data set, using an expression like =First(Fields!FirstName.Value) is acceptable in an independent text box. However, if the report has multiple data sets, an expression =First(Fields!FirstName.Value, "DataSet1") is required. Because data regions have an associated data set, this association provides scope resolution for fields within a data region. The downside of being tied to a data region is that the Fields collection is only accessible within data regions. This means that you cannot derive values from data sets for anything that does not or cannot contain a data region, such as page headers and footers.

Each field in the Fields collection has only two properties. The first one is Value . The value is actually what it says it isthe value of the field from the data set. The second property is IsMissing . This property tells you if the field actually exists in the data set. Although this might seem strange , this might prove to be very helpful if there is no way to be certain of the name of the fields being returned from a particular data set. IsMissing also helps if you have misspelled a field's name.

Parameters

The Parameters collection contains the list of parameters in the report. The parameters collection only has two properties: Value and Label . The Value is the value of the parameter. The Label is the friendly name of the parameter from the prompt property of the parameter.

The Parameters collection is similar to the Fields collection in that it can be used in any number of properties or functions. Unlike the Fields collection, the Parameters collection is global in scope. This means that the parameter can be accessed anywhere in the report, including page headers and footers.

Globals

The Globals collection is also fairly straightforward and has a predefined, fixed number of items. It is simply a list of global variables that are accessible while rendering the report.

Table 8.1 shows the properties available via the Globals collection.

Table 8.1. Globals Collection Items

Member

Type

Description

ExecutionTime

DateTime

The date and time that the rendering engine began processing the report.

PageNumber

Integer

The page number of the current page. This parameter can only be used in headers and footers.

ReportFolder

String

The virtual path to the folder containing the report. This does not include the ReportServerName.

ReportName

String

The name of the report from the Report Server's catalog.

ReportServerUrl

String

The URL of the Report Server from which the report is being processed .

TotalPages

Integer

The value for the total number of pages in a report. This parameter can only be used in headers and footers.


One of the most frequent uses of the Globals collection is to display a page number in the report's footer, such as in the following expression, which can be placed in a text box in the report's footer:

 =Globals.PageNumber & " of " & Globals.TotalPages. 

User

The User collection is the simplest of collections. It only contains two properties related to the user running the report, and both properties are strings. The first item is UserId , and the second item is Language . The UserId is the user identity of the person running the report. By default, SSRS uses Windows Authentication, which supplied the UserId in the form <DomainName>/<User Name> . The LanguageId is the language identifier (used in localization) for the user. In the case of US localization this value is " en-US ".

Note

When using Expression Editor, please , keep in mind that members of User collection ( UserID and Language ) are both located under Globals branch of the category member list. Please refer to Figure 8.2.


Report designers can use the User collection to provide user-dependent formatting customization of a report. To do that, a report developer can create a parameter with the following properties:

 Name: User Internal: Checked (this eliminates prompt to a report user) Available Values: Leave empty Default Values: Non Queried with expression =User!UserID 

Then, the report developer can use such parameters to conditionally format report items or to pass the parameter to a query. The benefit of using the parameter versus using =User!UserID directly (which is also an option) is an ability to modify the parameter's value. The parameter can be, for example, modified through users, while in turn =User!UserID is read-only.



Microsoft SQL Server 2005 Reporting Services
Microsoft SQL Server 2005 Reporting Services
ISBN: 0672327996
EAN: 2147483647
Year: 2004
Pages: 254

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net