Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
481 views
in Technique[技术] by (71.8m points)

c# - Generate columns dynamically in RDLC

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;
    }

enter image description here

enter image description here

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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. enter image description here

Then Add Parent Group By the "ColumnName" property(Remove the sorting) enter image description here

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

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...