How to dynamically add new columns to report created with Reporting Services?

Imagine you have a report that displays some data and the number of columns cannot be defined at the design stage, i.e. the number of columns depends on user input. That seems to cause some problems when you want to render this report with Reporting Services…

Solution:

Note: The described solution applies to the case when RDLC files are used by ReportViewer control in local mode. However, it can be simply tweaked so it works in general.

Because the number of columns that should be displayed on the report can be named only at runtime it seems reasonable to update the RDLC file and only then deliver the report. RDLC is an XML file that contains the definition of the report – styling and all fields displayed. So all you need to do is to read the RLDC file into XmlDocument, get some nodes and update them.

This is how this can be done. In the RDLC file locate /Report/Body/ReportItems/Table node and do the following inside it:

• define the header of a new column – add a new TableCell inside Header node
• bind the column with data (from DataTable) – add a new TableCell inside Details node
• define the width of the colum – add a new TableColumn inside TableColumns

Apart from that you need to modify the definition of the DataSet that will ship the data. It is defined as a Field node within /Report/DataSets.

The easiest way to add new nodes is to get its parent, copy its last child and update the copy with appropriate values, and add it as the new last child. This way you will probably have to update only a couple of strings instead of creating the new node which has another nodes as children etc.

Then you need to create a DataTable object, populate it with data and bind it with the ReportViewer as its data source.

ReportDataSource rds = new ReportDataSource(DS_NAME, DATA_TABLE);
ReportViewer1.LocalReport.ReportPath = string.Empty;
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(rds);

byte[] rdlBytes = Encoding.UTF8.GetBytes(XML_DOC.OuterXml);
MemoryStream stream = new MemoryStream(rdlBytes);
ReportViewer1.LocalReport.LoadReportDefinition(stream);

Where:

• DS_NAME – name of the data set, which is used in RDLC file (DataSet Name="DS_NAME")
• DATA_TABLE – DataTable object that ships data (System.Data.DataTable)
• XML_DOC – XmlDocument that contains the (updated) definition of RDLC file

That should do the trick. However playing with XmlDocument is not on of the most enjoyable games