Learn more about SQL Server tools

   
   















































SQL Server Reporting Services ReportItems Collection

MSSQLTips author Scott Murray By:   |   Read Comments (6)   |   Related Tips: > Reporting Services Parameters
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.

What is SQL Server Reporting Services 2012 (SSRS) ReportItems collection?

As noted in the previewed report below, the ShipTo box now displays the same information that is in the tbBillTo textbox.

Simple Reference Report

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.

Page Header Reference

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.


Page Header Reference

Page Header Reference Page 3

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.

Tablix Textbox

Attempting to preview the report in BIDS produces the following error.

Tablix 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.

Last Aggregate Function

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.

Last Reference Tablix Error

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.

Last Reference Tablix Page Header

Report with Report Items in Page Header

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.

Report Header Sum

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


Last Update: 6/12/2013


About the author
MSSQLTips author Scott Murray
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, November 05, 2013 - 1:35:05 PM - scott Read The Tip

For a chart you will need to actual values. The reportItems collection only shows you the last value to be placed in that fie.d


Tuesday, November 05, 2013 - 10:58:25 AM - Parthiv Read The Tip

Thanks for this explanation, makes sense to me now. 

But i have a similar problem. I have a table that has a column that uses the ReportItems!Column.value to calculate some vaules. the table works fine and gives me the values i need. 

I am now trying to plot this 'on the run' value on a chart, but it only shows the last value. 

so for example - 

ID      column 2        column 3      column 4 

1            4                    5            (ReportItems!Column 3.Value - ReportItems!column 2.Value) - 1

2            3                    8            (ReportItems!Column 3.Value - ReportItems!column 2.Value)- 5

3            2                    6            (ReportItems!Column 3.Value - ReportItems!column 2.Value)- 4

 

Plot line graph ReportItems!column4.value against ID it shows a flat line for all the ID with value 4

Offcourse this is very simplified my calculations on column4 uses running value calculations. 

 

I would appreciate any help i can get with this.

 

Thanks, 

 


Sunday, August 11, 2013 - 7:41:00 AM - Scott Murray Read The Tip

Phoebe,

 

You can use the report actions properties of a textbox to open a new report and pass the current value into the parameters for the new report.  Please take a look at:  http://www.mssqltips.com/sqlservertip/2831/sql-server-reporting-services-2012-drilldown-features/


Saturday, August 10, 2013 - 7:33:06 PM - Phoebe M Read The Tip

Thank you for the tips. What if I need to pass the value of the cell i click as a parameter to another report, how do I refer the active cell? For example when I click on ProductNumber BK-M18B-48 I want the action to open a report that passes the BK-M18B-48 as parameter.

Thank you for your time

 

Phoebe M


Wednesday, June 12, 2013 - 11:58:04 AM - Scott Murray Read The Tip

Unfortunately, you get an error ;-(.  


Wednesday, June 12, 2013 - 10:08:30 AM - John Read The Tip

But, what if we do not want to put it in the header?

 

Thanks.




 
Sponsor Information