c# - Generate columns dynamically in RDLC

Question

I am trying to generate a RDLC report in ASP.NET where the columns of my Dataset will be dynamic and determined only at run time.

I have a made a function that returns a DataTable, and by selecting this function in the RDLC report wizard, I can generate my report successfully.

    public DataTable GetTable()
    {
        // Here we create a DataTable with four columns.
        DataTable table = new DataTable();
        table.Columns.Add("Dosage", typeof(int));
        table.Columns.Add("Drug", typeof(string));
        table.Columns.Add("Patient", typeof(string));
        table.Columns.Add("Date", typeof(DateTime));
        table.Columns.Add("testColumn", typeof(DateTime));

        // Here we add five DataRows.
        table.Rows.Add(25, "Indocin", "David", DateTime.Now);
        table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
        table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
        table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
        table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
        return table;
    }

But, if I make a slight change to the function so that my datatable is truly dynamic, by populating columns from the database, my function then does not show up in the report wizard.

This is my changed function

    public DataTable GetTable2()
    {
        // Here we create a DataTable with four columns.
        DataTable table = new DataTable();
        table.Columns.Add("Dosage", typeof(int));
        table.Columns.Add("Drug", typeof(string));
        table.Columns.Add("Patient", typeof(string));
        table.Columns.Add("Date", typeof(DateTime));
        table.Columns.Add("testColumn", typeof(DateTime));


        SqlConnection connection = new SqlConnection();
        connection = Connection.getConnection();
        connection.Open();


        string tableName = "";

        tableName += "Subject";


        string Query = "select * from " + tableName + " where Status = 0;";


        SqlDataAdapter da = new SqlDataAdapter(Query, connection);
        DataSet ds = new DataSet();
        da.Fill(ds);
        DataRowCollection collection = ds.Tables[0].Rows;
        foreach (DataRow row in collection)
        {

            // Here we add five DataRows.
            table.Rows.Add(25, "Indocin", "David", DateTime.Now);
            table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
            table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
            table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
            table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);

        }


        connection.Close();

        return table;
    }

You see, the only change I have made to the function is querying from the database and generating the report dataset columns within the loop that iterates through database data. But due to this change, my function does not show up in the Report Wizard. If I omit the code, it shows up again.

I can use this function to generate a GridView nicely, but the problem is with RDLC reporting.

My objective is to generate the report datatable using database results. Please help me.

Answer 1

I had the same demand and did a bit survey of solutions.
I didn't try them all but still list those I think making sense.

1. Use RDL instead of RDLC
   Because RDLC isn't easy to achieve dynamic columns. You may want just change to RDL.

2. List all columns then hide those you don't need
   This works when your columns have max limitation and quite easy to implement by setting "Column visibility" with expression.

3. Create RDLC at runtime
   RDLC is based on XML so it makes sense that you generate an RDLC at runtime which fit your data structure.
   Well, I didn't choose this since I think the schema of RDLC is a bit complicated plus my table is actually simple even it needs dynamic columns.
   If you really need to use this ultimate solution you may want to search if there are some sort of libs which can help you with the construction.

4. Divide table into list of cells and reassembly them by group row and column identity
   This was suggested by my colleague Jimmy. A bit hacky but I found it very useful if your table doesn't have complex structures like ColumnSpan or stuff. Detail as the following example

ProductName | Qty    | Date
----------- | ------ | ---------
ProductA    | 1      | 2016-01-01
ProductA    | 2      | 2016-01-15
ProductA    | 3      | 2016-01-31
ProductA    | 1      | 2016-02-01
ProductA    | 2      | 2017-01-01
ProductA    | 3      | 2017-01-15
ProductA    | 1      | 2017-01-31
ProductA    | 2      | 2017-02-01
ProductA    | 3      | 2017-02-15
ProductA    | 1      | 2017-02-28
ProductB    | 2      | 2016-01-01
ProductB    | 3      | 2016-01-15
ProductB    | 1      | 2016-01-30
ProductB    | 2      | 2016-02-01
ProductB    | 3      | 2017-01-01

I need to total quantity by month or year and the result need to form like

ProductName | Jan    | Feb   
----------- | ------ | ------
ProductA    | 12     | 7   
ProductB    | 9      | 2     

ProductName | 2016   | 2017   
----------- | ------ | ------
ProductA    | 7      | 12   
ProductB    | 8      | 3     

When group by month I can list all 12 months and hide those I don't need.
However, it won't work when group by year.

To implement solution 4.
First, Prepare the DataTable formed exactly you need.

Second, Divide the DataTable into List<ReportCell>.

public class ReportCell
{
    public int RowId { get; set; }
    public string ColumnName { get; set; }
    public string Value { get; set; }

    public static List<ReportCell> ConvertTableToCells(DataTable table)
    {
        List<ReportCell> cells = new List<ReportCell>();

        foreach (DataRow row in table.Rows)
        {
            foreach (DataColumn col in table.Columns)
            {
                ReportCell cell = new ReportCell
                {
                    ColumnName = col.Caption,
                    RowId = table.Rows.IndexOf(row),
                    Value = row[col.ColumnName].ToString()
                };

                cells.Add(cell);
            }
        }

        return cells;
    }
}

Third, Use this List as the source of ReportViewer.

// in DAO
public List<ReportCell> GetReportCells(DataTable table)
{
    return ReportCell.ConvertTableToCells(table);
}    

// in aspx.cs
ReportViewer_main.LocalReport.ReportPath = Server.MapPath("~/RDLC/Report_main.rdlc");
ReportViewer_main.LocalReport.DataSources.Add(
    new ReportDataSource("DataSet1", dao.GetReportCells(tableGroupByMonth)));
ReportViewer_main.LocalReport.Refresh();

Finally, In RDLC add a table. Remove everything except data field.
Set data with the "Value" property.

Then Add Parent Group By the "ColumnName" property(Remove the sorting)

and make Details group on the "RowId" property Now the rdlc should able to display everything exactly formed as the DataTable.

For better understanding, I made a demo project on my GitHub.