Problem
What is SQL Server Reporting Services 2012 (SSRS) ReportItems collection and how do I use it? Are there any restrictions on its use? Check out this tip to learn more.
Solution
Often when designing SSRS reports, report creators are quite adept at adding / dragging and dropping data items onto a tablix. Going even a step further, many users can readily add grouping and aggregations to “rollup” values into summary rows and columns. However, many users have never used the ReportItems collection to reference other objects on a report. ReportItems is actually a collection which allows a report designer to reference a textbox within the current scope and can be placed on the report header, footer or report body. The scope is determined at report runtime during processing.
Using the ReportItems Collection
The basic syntax for the ReportItems reference is as follows:
ReportItems!TextboxName.Value.
The only property of the ReportItems collection is “Value”, and you simply replace the TextboxName with the name of the textbox you would like to reference. The process seems deceptively simple, and it is simple if the textbox you are referencing is within the same scope and is not part of a larger list of values within a tablix, matrix, table or list. The best way to explain the ReportItems collection is through examples; we will use sample reports from the AdventureWorks data warehouse database to illustrate the use of the ReportItems collection. In the first example displayed below, we will simply use the ReportItems collection to reference another textbox on the report; in this example the textbox being referenced is the tbBillTo textbox, and it is being referenced from the ShipTo textbox to its right.
As noted in the previewed report below, the ShipTo box now displays the same information that is in the tbBillTo textbox.
This same referencing method works from either the page header or footer, as long as the reference is to a single textbox on the page and which is not part of a tablix, matrix, table, or group. Thus, in the following illustration, the ReportItems reference from the tbBillTo textbox is completed within a textbox in the page header.
The result is two fold. First you will see in the below screen print that the textbox in the page header appropriately displays the information from the bill to textbox. Furthermore, in the second screen print below, you will see that on page 3 of the report, the Bill To changed to a new customer, and the textbox using the ReportItems collection changed with it. The process noted the scope of the tbBillto textbox and changed accordingly. Note, if the referenced textbox does not exist on the page when the report is rendered, the ReportItems collection textbox will error and not display any data for that page.
ReportItems Referencing textboxes within a Tablix Group
Up to this point, the ReportItems references have been to simple, independent textboxes. What happens if the textbox is part of a tablix, matrix, table, or group? Using the same report, we will now change the ReportItems textbox, which is not part of the table, to reference the line total textbox in the tablix.
Attempting to preview the report in BIDS produces the following error.
The error is caused by the textbox containing the ReportItems reference rendering outside the tablix group scope. This error make sense, because the ReportItems references a textbox that actually produces many lines of data, one for invoice line. The ReportItems reference does not know what line to use. The natural thought is to use one of the Aggregate functions such as First or Last as illustrated below.
Unfortunately, using the Last functions also produces an error, as seen below; however the error actually displays a valid suggestion of where the ReportItems textbox needs to be.
So moving the ReportItems reference to a textbox in the page header now results in displaying the last line value as illustrated by the next two screen prints.
Thus moving the Aggregated ReportItems reference to a textbox in the page header now results in displaying the “Last” line value in the page header textbox (placing it in the page footer will produce similar results). Combining the move to the page header along with using the Last aggregate function results in displaying the appropriate value and no error. Of course, the value is referencing only to the last value on the current page; in the example report, if the invoice lines is more than one page, the value will only be the last value on each page. Furthermore, we could switch the aggregate function to SUM, which would give us the SUM of all the values for just that page.
Conclusion
Using the ReportItems collection allows a report designer to reference a specific textbox within a report as long as that object is in the same scope level of the textbox containing the ReportItems reference. Alternatively the ReportItems collection can be added to a page header or footer and by utilizing an aggregate function with the caveat that the aggregation is based on the scope of the currently rendered page.
Next Steps
- Report Items-Report View vs. Print View– http://blogs.msdn.com/b/nav/archive/2011/05/19/rdl-report-design-reportitems-and-why-are-fields-not-shown-in-the-header-when-printing-works-ok-in-preview.aspx
- Check out all of the SQL Server Reporting Services tips
