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 icon smile How to dynamically add new columns to report created with Reporting Services?

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

30 Responses to “How to dynamically add new columns to report created with Reporting Services?”


  • Hi,

    I want to design report using Sql reporting service (Remote mode). The Data comes from stored procedure . The Stored procedure returns dynamic columns depending upon the columns. How can I design the report.. Is it possible?

  • A.Balaa,

    I had the same problem and this post explains how I did it.
    Basically I created a report which contained those columns that always appear. The dynamic columns are added at runtime by changing RDLC file (adding definition of those columns).

    Jarek

  • Thanks for your immediate reply Jarek :-) .

    A.Balaa,

  • Can you put sample project

  • Unfortunately not any more….

  • Hi,

    I want to dynamically format the contents of a rdlc table. Lets say that the contents that I get from a database is “Hello world”, but I want the “hello” to be bold. Is there any possible way for this to be done by adding tags or anything to the data in the database?

  • Hi,

    How to adjust rdlc Header width, after dynamically hide a few rdlc column in Body? Is there any Idea? Thanks.

  • Hi,

    How do I adjust overall width of rdlc report when some columns are hidden?

  • How can be add report header dynamically to dynamically created reporting services.I have created reporting service dynamically but i am unable to design report header dynamically please reply as soon as possible.

  • I have an RDLC with 5 tables and I have a dataset with one fillmethod and each of the 5 tables will need to display different set of data depending on different values in the fillmethod. Is is possible to tie fillmethodA to table A, fillmethodb to tableb all in the same RDLC?

    Thanks in advance

  • Can you please explain the process how i can to update the definition of RDLC file.

    Thanks in Advance

  • Pankaj,

    RDL/RDLC is a simple XML file. You need to open that file for edition in text editor and make appropriate changes according to RDL Specification that is available there: http://msdn.microsoft.com/en-us/library/dd297486.aspx

    Cheers,
    Jarek

  • Please can you dig out a sample project or even more sample c# code?

  • Rad,

    Unfortunately I no longer have example codes so I can’t share it. However, as I replied to Pankaj read on XML parsing in C# and RDL/RDLC documentation. Once you have modified RDL in a variable, it can be passed to ReportViewer according to the instruction in the post.

    Cheers,
    Jarek

  • Hi guys!

    After an entire morning of looking for information about how to bind dynamic columns to a report I decided to develop a kind of “table manager” to do it. I attach the code below so you can try it. I’m sorry but it’s kind of “personalised” (and the comments are in Catalan) but I have the deadline of the project very close! :)

    STEPS to make it work:

    1. Create a report and put a Table in it. Give a name to the table “Ex.: Dades”

    2. Add 8 columns (I needed 8) and give name to all of them just changing the number you put on the back. For the headers I used capsCol1, capsCol2, capsCol3, … and for the data i used valCol1, valCol2, valCol3. It’s important that the numbers are from 1 to 8.

    3. You have to create a List with 8 ColumnReport, one for each column informing the Title, the Position, the Width, the formula you want to use to get the information for the rows (Ex.: “=Fields!Afortunat.Value”)

    public class ColumnReport : IComparable
    {
    private string _titolColumna, _formulaColumna;
    private int _ample, _posicio;
    private bool _visible;

    public string ColumnTitle
    {
    get { return _titolColumna; }
    set { _titolColumna = value; }
    }

    public string FieldFormula
    {
    get { return _formulaColumna; }
    set { _formulaColumna = value; }
    }

    public int Width
    {
    get { return _ample; }
    set { _ample = value; }
    }

    public int ColPosition
    {
    get { return _posicio; }
    set { _posicio = value; }
    }

    public bool Visible
    {
    get { return _visible; }
    set { _visible = value; }
    }

    #region Miembros de IComparable

    public int CompareTo(object obj)
    {
    return this.ColPosition.CompareTo(((ColumnReport)obj).ColPosition);
    }

    #endregion

    Then You just have to use the class GestorReport. Intantiate it and provide the follow information: reportPath, tableName (the name you gave to the table), the two prefixes of the headers fields and value filds (capsCol and valCol in this example) and pass also the list of ColumnReport

    Here is the code:

    public class GestorReport
    {
    private string _pathReport, _nomTaula, _prefixColTitol, _prefixColValor;
    private List _lstColumnes;

    public GestorReport(string pathReport, string tableName, string prefixColTitle, string prefixColValue, List lstColumnes)
    {
    _pathReport = pathReport;
    _nomTaula = tableName;
    _lstColumnes = lstColumnes;
    _prefixColTitol = prefixColTitle;
    _prefixColValor = prefixColValue;
    }

    public XmlDocument Configurar()
    {
    // 1. Obtenim el report
    XmlDocument objXmlDocument = new XmlDocument();
    objXmlDocument.Load(_pathReport);
    XmlNamespaceManager mgr = new XmlNamespaceManager(objXmlDocument.NameTable);
    string uri = “http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition”;
    mgr.AddNamespace(“df”, uri);

    // Val, ara obtenim totes les columnes on hi ha els tamanys!
    XmlNodeList wCols = objXmlDocument.SelectNodes(String.Format(“/df:Report/df:Body/df:ReportItems/df:Tablix[@Name='{0}']/df:TablixBody/df:TablixColumns/df:TablixColumn/df:Width”, _nomTaula), mgr);

    IEnumerator it = _lstColumnes.GetEnumerator();
    for (int i = 0 ; i < _lstColumnes.Count; i++)
    {
    ColumnReport ci = _lstColumnes[i];

    // Som-hi… qu? hem de fer?
    // 0. Configurar l'amplada
    // Aix? va per ordre…segons la i on estem ja l'hi podem anar endinyant l'amplada
    wCols[i].InnerText = ci.Width + "cm";

    // Ara anem a per les coses serioses
    XmlNode campTitol = objXmlDocument.SelectSingleNode(String.Format("/df:Report/df:Body/df:ReportItems/df:Tablix[@Name='{0}']/df:TablixBody/df:TablixRows/df:TablixRow/df:TablixCells/df:TablixCell/df:CellContents/df:Textbox[@Name='{1}{2}']", _nomTaula, _prefixColTitol, ci.ColPosition), mgr);
    XmlNode campValor = objXmlDocument.SelectSingleNode(String.Format("/df:Report/df:Body/df:ReportItems/df:Tablix[@Name='{0}']/df:TablixBody/df:TablixRows/df:TablixRow/df:TablixCells/df:TablixCell/df:CellContents/df:Textbox[@Name='{1}{2}']", _nomTaula, _prefixColValor, ci.ColPosition), mgr);

    // 1. Configurem la visibilitat
    if (!ci.Visible)
    {
    XmlElement nVis = objXmlDocument.CreateElement("Visibility", uri);
    XmlElement nHid = objXmlDocument.CreateElement("Hidden", uri);
    nHid.InnerText = "true";
    nVis.AppendChild(nHid);
    campTitol.AppendChild(nVis);

    wCols[i].InnerText = "0cm";
    }

    if (!ci.Visible)
    {
    //XmlNode campVisibilitatValor = campValor.SelectSingleNode("./df:Visibility/df:Hidden", mgr);
    XmlElement nVis = objXmlDocument.CreateElement("Visibility", uri);
    XmlElement nHid = objXmlDocument.CreateElement("Hidden", uri);
    nHid.InnerText = "true";
    nVis.AppendChild(nHid);
    campValor.AppendChild(nVis);

    wCols[i].InnerText = "0cm";
    }

    // 2. Configurar el títol de la columna (es podría fer per par?metres per? sudem)
    XmlNode campTitolValor = campTitol.SelectSingleNode("./df:Paragraphs/df:Paragraph/df:TextRuns/df:TextRun/df:Value", mgr);
    campTitolValor.InnerText = ci.ColumnTitle;

    // 3. Configurar la f?rmula
    XmlNode campValorValor = campValor.SelectSingleNode("./df:Paragraphs/df:Paragraph/df:TextRuns/df:TextRun/df:Value", mgr);
    campValorValor.InnerText = ci.FieldFormula;
    }

    return objXmlDocument;
    }
    }

    Then, you just have to call Configurar() and you'll get an XmlDocument which can be passed to the reportViewer as follows:

    GestorReport gi = new GestorReport(@"C:\Users\Jordi\Documents\Projectes\Habitatge assequible\Programació\Sortejos\Sortejos\Report1.rdlc", "Dades", "capsCol", "valCol", lstColumnes);
    XmlDocument reportAjustat = gi.Configurar();

    // Configurem el report al viewer…: Report, par?metres i datasets
    reportViewer1.LocalReport.ReportPath = string.Empty;
    reportViewer1.LocalReport.DataSources.Clear();

    // A. The dataset with the data
    reportViewer1.LocalReport.DataSources.Add(new ReportDataSource("ResultatSorteig", lstResultat.Elements));

    // B. THE REPORT
    byte[] rdlBytes = Encoding.UTF8.GetBytes(reportAjustat.OuterXml);
    MemoryStream stream = new MemoryStream(rdlBytes);
    reportViewer1.LocalReport.LoadReportDefinition(stream);

    // C. Some parameters
    ReportParameter[] parametres = new ReportParameter[1];
    parametres[0] = new ReportParameter("VisibilitatColumnaAfortunat", "S", true);
    reportViewer1.LocalReport.SetParameters(parametres);

    // Refresquem i avall que fa baixada!
    reportViewer1.RefreshReport();
    reportViewer1.Show();

    Hope this helps! :)

  • Jordi,

    Many thanks for posting a code example. This is more or less similar to the code I created in past, when I was writing about this problem. I didn’t test your code, but assume it works like a charm :)

    Cheers,
    Jarek

  • All i did was PIVOT (or UNPIVOT)in my dynamic SQL and use the Matrix control to pivot (or unpivot) back the data.

  • Hi All,
    I want to create rdlc report with dynamic columns header(that get from table)
    if someone know how to do, pls kindly help me.thanks a lot.

  • You might want to take a looks at http://www.rptgen.com

    Thanks

  • I found this thread very helpful in creating my own version of dynamically adding columns to a .rdlc report and thougt I would share my solution in hopes it will help someone else out

    in the page that holds the reportviewer I have this method that executes on load. Note that I don’t set a datasource here b/c I’m setting it in the reportviewer control using a DataSourceId. However, you could set one here as well and it would work the same.

    Private Sub renderReportPreview()

    Dim convertor As New ConfigureRDLC()

    Dim dict As New Dictionary(Of String, String)

    ‘add columns here to the dictionary
    dict.Add(“Test”, “TestId”)

    ‘code for this is in the class for ConfigureRDLC()
    Dim doc As XmlDocument = convertor.addColumns(Server.MapPath(“path_to_report”, 1, dict)

    Me.ReportViewer1.LocalReport.ReportPath = String.Empty
    Me.ReportViewer1.LocalReport.DisplayName = “DetailReport”
    Dim bytes As Byte() = Encoding.UTF8.GetBytes(doc.OuterXml)
    Dim stream As New MemoryStream(bytes)
    ReportViewer1.LocalReport.LoadReportDefinition(stream)

    ‘Refresh the report
    ReportViewer1.LocalReport.Refresh()

    End Sub

    ——————————————
    And here is the class that modifies the xml of the .rdlc report. Note: my report template only has one table on it with one column containing of a header row an the datarow. This method takes that xml markup and modifies it to add as many columns as you pass in through the dictionary. Just make sure the column names you pass in are the same as the column names coming from your datasource.

    Public Class ConfigureRDLC

    Public Sub New()

    End Sub

    Public Function addColumns(ByVal rptPath As String, ByVal numOfColumns As Integer, ByVal columns As Dictionary(Of String, String)) As XmlDocument

    Dim objXML As New XmlDocument
    objXML.Load(rptPath)

    Dim mgr As New XmlNamespaceManager(objXML.NameTable)
    Dim uri As String = “http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition”

    mgr.AddNamespace(“df”, uri)

    ‘get the talix node
    Dim tablixNode As XmlNode = objXML.SelectSingleNode(String.Format(“/df:Report/df:Body/df:ReportItems/df:Tablix[@Name='{0}']/df:TablixBody”, “Tablix1″), mgr)

    ‘get current nodes
    ‘column Node
    Dim colNode As XmlNode = tablixNode.ChildNodes(0)
    ‘row one column one
    Dim rowNode1 As XmlNode = tablixNode.ChildNodes(1).ChildNodes(0).ChildNodes(1)

    ‘row tow column one
    Dim rowNode2 As XmlNode = tablixNode.ChildNodes(1).ChildNodes(1).ChildNodes(1)

    ‘add the number of columns to the sheet
    For i As Integer = 0 To numOfColumns – 1
    Dim newCol As XmlNode = colNode.ChildNodes(0).Clone
    colNode.AppendChild(newCol)
    Next

    For Each item As KeyValuePair(Of String, String) In columns
    ‘create header row
    Dim newRow1 As XmlNode = rowNode1.ChildNodes(0).Clone
    newRow1.ChildNodes(0).ChildNodes(0).Attributes(0).Value = Replace(item.Key.ToString, ” “, “”)
    newRow1.ChildNodes(0).ChildNodes(0).ChildNodes(2).ChildNodes(0).ChildNodes(0).ChildNodes(0).ChildNodes(0).InnerText = item.Key

    ‘create data row
    Dim newRow2 As XmlNode = rowNode2.ChildNodes(0).Clone
    newRow2.ChildNodes(0).ChildNodes(0).Attributes(0).Value = Replace(item.Key.ToString, ” “, “”) & “Data”
    newRow2.ChildNodes(0).ChildNodes(0).ChildNodes(2).ChildNodes(0).ChildNodes(0).ChildNodes(0).ChildNodes(0).InnerText = “=Fields!” & item.Value & “.Value”
    ‘add the rows to the report
    rowNode1.AppendChild(newRow1)
    rowNode2.AppendChild(newRow2)
    Next

    ‘add to tablixcolumn
    Dim tablixColumnNode As XmlNode = objXML.SelectSingleNode(String.Format(“/df:Report/df:Body/df:ReportItems/df:Tablix[@Name='{0}']/df:TablixColumnHierarchy”, “Tablix1″), mgr)

    Dim tablixColumnAdd As XmlNode = tablixColumnNode.ChildNodes(0)

    For i As Integer = 0 To numOfColumns – 1
    Dim newColumnNode As XmlNode = tablixColumnAdd.ChildNodes(0).Clone()
    tablixColumnAdd.AppendChild(newColumnNode)
    Next

    Return objXML

    End Function

    ————————————
    And that’s it! you now have a report with two columns. The original one which will always be there when the report renders, and the new column with the header name “Test” and the dataColumn “TestId”

  • Thanks Zach! I hope this page could help more people now.

  • Thanks Jarosław and Zach! Your suggestions helped me to solve the problem on how to programmatically change the value of the report Variables.

    I just added the following code to the addColumns function (as per the Zach’s sample)

    ———————

    Dim rptVars As New Dictionary(Of String, String)

    rptVars.Add(“AppName”,”The Reporter!”)
    rptVars.Add(“AppVersion”,”Beta2, Build 1901″)
    rptVars.Add(“AppCopyright”,”(C) EDC 2011″)
    ‘ add all the variables of the report
    ‘ please! take care of the variables name capitalization, as XML *IS* case-sentitive by design

    ‘ navigate the key/value dictionary
    For Each item As KeyValuePair(Of String, String) In rptVars
    ‘ get the single Variable node
    Dim VariablesNode As XmlNode = objXML.SelectSingleNode(String.Format(“/df:Report/df:Variables/df:Variable[@Name='{0}']/df:Value”, item.Key), mgr)
    If Not VariablesNode Is Nothing Then
    ‘ fill-in the value
    VariablesNode.FirstChild.Value = item.Value
    End If
    Next

    ———————————–

    Thanks again!

  • What if the report definition changes to add different tag names in the schema? Will your program still work?

  • I want to create dynamic columns in RDLC reports with the dynamic Dataset.

    We are entering the columns name in a Rows in database tables (Because number of columns are not defined)and converting the rows into columns and relatd data from other tables and creating the dynamic dataset.

    We have a to bind above defined dataset with RDLC file.

    Is there any way to define dynamic columns in RDLC file and bind the data with it?

    Piyush

  • I’ve just finished this code and it works fine:

    public static XmlDocument Configurar(string path, string tableName, List colunas)
    {
    XmlDocument objXmlDocument = new XmlDocument();
    objXmlDocument.Load(path);
    XmlNamespaceManager mgr = new XmlNamespaceManager(objXmlDocument.NameTable);
    string uri = “http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition”;
    mgr.AddNamespace(“df”, uri);

    XmlNode tablixCols = objXmlDocument.SelectSingleNode(String.Format(“/df:Report/df:Body/df:ReportItems/df:Tablix[@Name='{0}']/df:TablixBody/df:TablixColumns”, tableName), mgr);

    XmlNode dataField = objXmlDocument.SelectSingleNode(“/df:Report/df:DataSets/df:DataSet/df:Fields/df:Field”, mgr);

    XmlNode tablixMember = objXmlDocument.SelectSingleNode(String.Format(“/df:Report/df:Body/df:ReportItems/df:Tablix[@Name='{0}']/df:TablixColumnHierarchy/df:TablixMembers/df:TablixMember”, tableName), mgr);

    XmlNode rowHeaders = objXmlDocument.SelectSingleNode(String.Format(“/df:Report/df:Body/df:ReportItems/df:Tablix[@Name='{0}']/df:TablixBody/df:TablixRows/df:TablixRow”, tableName), mgr);
    XmlNode rowValues = rowHeaders.NextSibling.SelectSingleNode(“./df:TablixCells”, mgr);
    rowHeaders = rowHeaders.SelectSingleNode(“./df:TablixCells”, mgr);

    XmlNode sampleCol = tablixCols.SelectSingleNode(“./df:TablixColumn”, mgr);
    XmlNode sampleHeader = rowHeaders.SelectSingleNode(“./df:TablixCell”, mgr);
    XmlNode sampleValue = rowValues.SelectSingleNode(“./df:TablixCell”, mgr);

    foreach (ReportColumn coluna in colunas)
    {
    XmlNode newDataField = dataField.CloneNode(true);
    newDataField.SelectSingleNode(“./df:DataField”, mgr).InnerText = coluna.ColumnTitle;
    newDataField.Attributes["Name"].Value = coluna.ColumnTitle;
    dataField.ParentNode.AppendChild(newDataField);

    XmlNode newCol = sampleCol.CloneNode(true);
    XmlNode newHeader = sampleHeader.CloneNode(true);
    XmlNode newValue = sampleValue.CloneNode(true);

    newHeader.SelectSingleNode(“./df:CellContents/df:Textbox”, mgr).Attributes["Name"].Value = “Header” + coluna.ColumnTitle;
    newValue.SelectSingleNode(“./df:CellContents/df:Textbox”, mgr).Attributes["Name"].Value = “Value” + coluna.ColumnTitle;

    newHeader.SelectSingleNode(“./df:CellContents/df:Textbox/df:Paragraphs/df:Paragraph/df:TextRuns/df:TextRun/df:Value”, mgr).InnerText = coluna.ColumnTitle;
    newValue.SelectSingleNode(“./df:CellContents/df:Textbox/df:Paragraphs/df:Paragraph/df:TextRuns/df:TextRun/df:Value”, mgr).InnerText = string.Format(“=Fields!{0}.Value”, coluna.ColumnTitle);

    tablixCols.AppendChild(newCol);
    rowHeaders.AppendChild(newHeader);
    rowValues.AppendChild(newValue);

    tablixMember.ParentNode.AppendChild(tablixMember.CloneNode(true));
    }

    return objXmlDocument;
    }

  • yeah SAM u r right, if u use Matrix then dynamic column generation is possible at runtime..

    I have got a problem while generating an Identity card. I have to print two column in a row so that the wastage of paper is less. can anyone help me…

  • Hi Jarosław, I want create a report with Reporting Services in C# but dynamic, for to use vey stored procedure, but I don’t understand how can doing it. I’m Programmer Junior. Can you help me? Please, my e_mail is alfaaereo of hotmail.

  • Viviana,

    Sorry for late response… Your question is so generic that it’s really hard to help you. I’d suggest google the problem, there must be tutorials that describe how to achieve it.

    Best,
    Jarek

Leave a Reply