在 RDLC 中动态生成列 [英] Generate columns dynamically in RDLC
问题描述
我正在尝试在 ASP.NET 中生成 RDLC 报告，其中我的数据集的列将是动态的并且仅在运行时确定.
我有一个返回数据表的函数，通过在 RDLC 报告向导中选择这个函数，我可以成功生成我的报告.
public DataTable GetTable(){//这里我们创建了一个四列的数据表.DataTable table = new DataTable();table.Columns.Add("剂量", typeof(int));table.Columns.Add("Drug", typeof(string));table.Columns.Add("患者", typeof(string));table.Columns.Add("Date", typeof(DateTime));table.Columns.Add("testColumn", typeof(DateTime));//这里我们添加了五个 DataRow.table.Rows.Add(25, "Indocin", "David", DateTime.Now);table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);table.Rows.Add(10, "肼苯哒嗪", "Christoff", DateTime.Now);table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);返回表；}但是，如果我对函数稍作改动，使我的数据表真正动态化，通过填充数据库中的列，我的函数就不会出现在报表向导中.
这是我改过的功能
public DataTable GetTable2(){//这里我们创建了一个四列的数据表.DataTable table = new DataTable();table.Columns.Add("剂量", typeof(int));table.Columns.Add("Drug", typeof(string));table.Columns.Add("患者", typeof(string));table.Columns.Add("Date", typeof(DateTime));table.Columns.Add("testColumn", typeof(DateTime));SqlConnection 连接 = 新的 SqlConnection();连接 = Connection.getConnection();连接.打开()；字符串表名 = "";表名 += "主题";string Query = "select * from " + tableName + " where Status = 0;";SqlDataAdapter da = new SqlDataAdapter(Query, connection);数据集 ds = 新数据集();da.Fill(ds);DataRowCollection 集合 = ds.Tables[0].Rows;foreach(集合中的 DataRow 行){//这里我们添加了五个 DataRow.table.Rows.Add(25, "Indocin", "David", DateTime.Now);table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);table.Rows.Add(10, "肼苯哒嗪", "Christoff", DateTime.Now);table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);}连接.关闭();返回表；}您看，我对该函数所做的唯一更改是从数据库查询并在循环访问数据库数据的循环中生成报告数据集列.但由于此更改，我的函数没有显示在报表向导中.如果我省略代码，它会再次显示.
我可以使用此函数很好地生成 GridView，但问题在于 RDLC 报告.
我的目标是使用数据库结果生成报告数据表.请帮帮我.
我有同样的需求，并做了一些解决方案的调查.
我没有尝试所有这些，但仍然列出了我认为有意义的那些.
- 使用 RDL 代替 RDLC
因为RDLC不容易实现动态列.您可能只想更改为 RDL. 列出所有列，然后隐藏不需要的列
当您的列有最大限制并且通过使用表达式设置列可见性"很容易实现时，这会起作用.在运行时创建 RDLC
RDLC 基于 XML，因此您可以在运行时生成适合您的数据结构的 RDLC.
嗯，我没有选择这个，因为我认为 RDLC 的模式有点复杂，而且我的表实际上很简单，即使它需要动态列.
如果您真的需要使用这个终极解决方案，您可能需要搜索是否有某种库可以帮助您进行构建.将表格分成单元格列表，并按组行和列标识重新组合它们
这是我的同事吉米建议的.有点 hacky 但我发现如果您的表没有像 ColumnSpan 或其他东西这样的复杂结构，它会非常有用.详情如下例
<前>产品名称 |数量 |日期----------- |------ |---------产品A |1 |2016-01-01产品A |2 |2016-01-15产品A |3 |2016-01-31产品A |1 |2016-02-01产品A |2 |2017-01-01产品A |3 |2017-01-15产品A |1 |2017-01-31产品A |2 |2017-02-01产品A |3 |2017-02-15产品A |1 |2017-02-28产品B |2 |2016-01-01产品B |3 |2016-01-15产品B |1 |2016-01-30产品B |2 |2016-02-01产品B |3 |2017-01-01
我需要按月或按年计算总量，结果需要像
<前>产品名称 |简 |二月----------- |------ |------产品A |12 |7产品B |9 |2<前>产品名称 |2016 |2017年----------- |------ |------产品A |7 |12产品B |8 |3
当按月分组时，我可以列出所有 12 个月并隐藏那些我不需要的.
但是，如果按年分组就行不通了.
实施解决方案 4.
首先，准备好你需要的数据表.
其次，将DataTable分成List.
公共类 ReportCell{公共 int RowId { 获取；放;}公共字符串列名 { 获取；放;}公共字符串值 { 获取；放;}公共静态列表ConvertTableToCells(DataTable表格){列表<ReportCell>单元格 = 新列表();foreach(table.Rows 中的 DataRow 行){foreach(table.Columns 中的 DataColumn col){ReportCell 单元格 = 新的 ReportCell{ColumnName = col.Caption，RowId = table.Rows.IndexOf(row),值 = row[col.ColumnName].ToString()};单元格.添加(单元格)；}}返回细胞；}} 第三，使用这个List作为ReportViewer的来源.
//在 DAO 中公共列表<ReportCell>GetReportCells(DataTable表格){返回 ReportCell.ConvertTableToCells(table);}//在 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 中添加一个表.删除除数据字段之外的所有内容.
使用值"属性设置数据.
然后通过ColumnName"属性添加父组(删除排序)
并在RowId"属性上创建详细信息组现在 rdlc 应该能够显示与 DataTable 完全相同的所有内容.
为了更好的理解，我在我的GitHub上做了一个演示项目.
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.
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.
- Use RDL instead of RDLC
Because RDLC isn't easy to achieve dynamic columns. You may want just change to RDL. 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.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.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.
这篇关于在 RDLC 中动态生成列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
