Learn more about SQL Server tools


Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories


SQL Server Reporting Services ReportItems Collection

By:   |   Read Comments (10)   |   Related Tips: > Reporting Services Parameters


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.


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: 


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


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:

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


Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

Get free SQL tips:

*Enter Code refresh code     

Saturday, December 10, 2016 - 7:12:20 PM - Scott Murray Back To Top


Glad it worked

Saturday, December 10, 2016 - 5:02:00 AM - sumit Back To Top


Nice !! I was struggling to find way to calculate reportItems

Thanks a lot :)



Wednesday, May 06, 2015 - 9:52:13 AM - Scott Murray Back To Top

as noted you can only use these in the headers and footers... sounds like you need to use some vb code or variable to pass the appropriate values.  I do not believe reportitems will work in your scenario.

Wednesday, May 06, 2015 - 9:32:35 AM - Hamza Javed Back To Top


i'm using ReportItems in my report and it is giving me this error as you described above : ("The Value expression for the textrun 'Amount8.Paragraphs[0].TextRuns[0]' uses an aggregate function on a report item.  Aggregate functions can be used only on report items contained in page headers and footers.") but my scenario is different. i have report containg 3 tables and their last columns are like below :

----     -----      -----     -----    -------      3333.00 (Table 1)

----     -----      -----     -----    -------      3348.00 (Table 2)

----     -----      -----     -----    -------      2798.00 (Table 2)

----     -----      -----     -----    -------      2798.00 (Table 3)

now the first row of Table 2 uses  value of row of Table 1(3333.00) to calculate 3348.00. but second row of Table 2 uses the value from 1st row(3348.00) to calculate 2798.00. Table 3 has nothing to do with them. now problem is that 3333.00 is coming from query so we can easily calculate 3348.00 but i dont know how to calculate 2798.00 as it required value of above cell which is 3348.00. similarly if Table 2 generate 10 rows than 1st value of last column should be calculated from 3333.00 and remaing rows will calculate their values from above cell. so i have done this : "IIf(RowNumber(DatasetName) = 1, value of Table1(3333.00) + and remaining values to calculate 3348.00, Previous(ReportItems!MyTextBoxName.Value)+ other values)". Now it gives me error that I right at bigining due to ReportItems. I know my scenario is complicated and its more complicated to explain it but i tried my best. please tell me any solution you have. ohhh important thing is this is SSRS report and i'm using report builder and SQL Server 2014. Any suggestion will be highly appreciated. Thanks :)     

Tuesday, November 05, 2013 - 1:35:05 PM - scott Back To Top

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 Back To Top

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.




Sunday, August 11, 2013 - 7:41:00 AM - Scott Murray Back To Top



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 Back To Top

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 Back To Top

Unfortunately, you get an error ;-(.  

Wednesday, June 12, 2013 - 10:08:30 AM - John Back To Top

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



Learn more about SQL Server tools