SSRS - rsMultiReportItemsInPageSectionExpression Error

Question

The Particulars:

I have a report that displays information about invoices. There is a page break between multiple invoices (each invoice gets its own page). What I want in the report header is the result of this expression (ex. "June, 2009"):

=MonthName(Month(ReportItems!textbox1.Value)) & ", " & cstr(Year(ReportItems!textbox1.Value))

But I get this exception (textbox2 is in the header):

Error 1 [rsMultiReportItemsInPageSectionExpression] The Value expression for the textbox ‘textbox2’ refers to more than one report item. An expression in a page header or footer can refer to only one report item.

First of all... Why would that even matter?! Second of all... How can I work around this strange restriction?

---

The Big Picture:

The reason I'm grabbing the text from another textbox is to work around the restriction that you can't use data fields in a header or footer. So there's a hidden column that shows the invoice date next to every transaction. Then the table header has the expression:

=First(Fields!InvoiceDate.Value, "table1_Group1")

This seems ridiculous and I hope I'm just doing something wrong. Yes I realize I could simply have a "month" and "year" text box in the header but that pushes the complexity way too high for the simple requirement of showing an invoice date in the header of a report.

Answer 1

One wild guess would be that textbox1 exists for every page of your report. So if you have two invoices, you'll have two pages and thus 2 x textbox1, so SSRS does not know which one to refer to.

You might try to play with report parameters as described here. If nothing else helps, I guess you'll have to put the invoice ID into the report body.

Answer 2

I came back to this issue and found a way around the buggy behavior in SSRS.

All formatting can be done inside the column header instead of up in the page header. The text box in the page header simply grabs the already formatted (and complete) text from the column header all at once. No more multiple references.

This is a hackish workaround for a strange limitation which is the result of a hackish workaround for another strange limitation. But it works.

Answer 3

I'm running into the exact same problem. It seems like the most rediculous restriction ever. Why would it matter if you reference the same ReportItem more than once. It's odd that something so simple is easily accomplished in Microsoft's "inferior" DB program Access, but is not even possible in the SQL version.

Thanks for sharing your work around to what seems like a pointless limitation in SSRS. Hopefully I can get that to work for me as well.

edit: Well, I haven't been able to figure out how to use a column header/footer to accomplish what I need. This should be simple. I have a SUM of time (seconds) in the Page Footer that tells me the total amount of time spent for everyone in the report. I just want to display the seconds in the Hours, Minutes, Seconds format. No problem...if you can refer to the same ReportItem 3 times for the conversion process.