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.
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.
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.
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
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.TextRuns' 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 :)
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
Wednesday, June 12, 2013 - 11:58:04 AM - Scott Murray