在RDLC中动态生成列 [英] Generate columns dynamically in RDLC

问题描述

我正在尝试在ASP.NET中生成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.

我有一个返回数据表的函数，通过在RDLC报告向导中选择此函数，我可以成功生成报告.

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.

这是我更改的功能

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

您会看到，我对该函数所做的唯一更改是从数据库查询并在遍历数据库数据的循环内生成报表数据集列.但是由于此更改，我的功能未显示在报告向导"中.如果我省略代码，它将再次显示.

我可以使用此函数很好地生成GridView，但是问题在于RDLC报告.

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.

推荐答案

我有相同的需求，并对解决方案进行了一些调查.
我并没有尝试所有方法，但仍然列出了我认为有意义的方法.

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. 使用RDL代替RDLC
   因为RDLC很难实现动态列.您可能只想更改为RDL.

2. 列出所有列，然后隐藏不需要的列
   当您的列有最大限制并且很容易通过使用表达式设置列可见性"来实现时，此方法就起作用.

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.

在运行时创建RDLC
RDLC基于XML，因此在运行时生成适合您的数据结构的RDLC是很有意义的.
好吧，我没有选择它，因为我认为RDLC的架构有点复杂，而且即使需要动态列，我的表实际上也很简单.
如果您确实需要使用此最终解决方案，则可能需要搜索是否有某种库可以帮助您进行构建.

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.

将表格划分为单元格列表，然后按组行和列标识对其进行重组
这是我的同事吉米建议的.有点古怪，但是如果您的表没有像ColumnSpan这样的复杂结构或东西，我发现它很有用.详细内容如下例

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     

按月分组时，我可以列出全部12个月并隐藏不需要的那些.
但是，按年份分组时将不起作用.

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.

实施解决方案4.
首先，准备完全需要的数据表.

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

第二，将数据表划分为 List< ReportCell> .

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

第三，将此列表用作ReportViewer的来源.

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();

最后，在RDLC中添加一个表.删除除数据字段以外的所有内容.
使用值"属性设置数据.

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

然后通过"ColumnName"属性添加父组(删除排序)

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

并在"RowId"属性上创建详细信息"组现在，rdlc应该能够显示与数据表完全相同的所有内容.

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

为了更好地理解，我在我的GitHub 上做了一个演示项目.

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

这篇关于在RDLC中动态生成列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！