SQL Server Reporting Services and Headers requiring dataset fields.

Any search for "fields in headers SSRS" in Google will come up with many search results with pages addressing workarounds for one of Microsoft SQL Server Reporting Service's biggest weakness: the inability to add fields to the header or footer from a dataset.

This limitation makes some sense in that a dataset returns rows of fields and so the problem might lie in identifying which row should be displayed, however one should be able to define exactly what to display- the first or last row or maybe an aggregate of the rows.

All that to say, this is yet another workaround I haven't seen elsewhere. It addresses the problem of maintaining data in the header regardless of what page the report is being viewed on.

Why another work around? I tend to avoid storing fields in internal parameters because I've had problems with it before, and shared variables are a horrible solution in a production enviroment because of the possibility of concurrently running reports. It seems like these are always the solutions given to this issue.

So here goes: the way I've solved it is by making a group within the table displaying my dataset, and then using the footer of the group to store a list box, (if you are needing to use the footer, simply extend it to add another footer row right below it). In that list box you can place another table or fields and you can even have this list box pull from another datasource. When done, set the group footer's "Hidden" property to "True" so that it doesn't show on the rendered report, and you're set. You can now reference these fields from the header or footer like so:

=First(ReportItems!NameOfTextboxInListItem.Value,"datasourceORgroupName")

And since it's an expression, you can do whatever you want. You can also use aggregates and such- just make sure you modify the expression on the field/table pulling directly from the dataset, and not the field in the header/footer that will actually be displaying the data.

The only major problem I've run into with this approach (that I am still working on) is that the fields in the header will export as blank if I export the report as a PDF. It will do fine in HTML, or even Excel. (Which is funny since most rendering errors seem to be tied to Excel instead of the other way around).

If any solution is found to this issue, or if someone can explain to me the PDF rendering process and why these fields show up as blank, it would be greatly appreciated!

Hopefully this post was of some use to those that do not need to export in PDF...

- Jheatt