如何在 SSRS 中表示未知数量的列？
我正在 Sql Server Reporting Services 中处理一个相当复杂的报告。 我的 SP 返回动态数量的列，每个列都是动态命名的。
基本上可以想象一个计时应用程序。 每列动态代表一个时间段，该时间段是为该团队计费的。 如果在报告涵盖的时间段内没有向该存储桶收取时间，则不会显示该时间段。 每个存储桶都有自己的标识符，我需要将其作为列标题。
我有一个 SP 可以返回这一切。 它通过使用 exec 语句执行一些动态 SQL 来实现这一点（我知道这很丑，但我使用的是 SQL 2000，因此 PIVOT 选项不起作用）
我可以拥有无限数量的存储桶，并且可能会显示任何或全部存储桶。
我发现了这个 - http://www.codeproject.com/KB/reporting-services /DynamicReport.aspx - 这很有帮助，但在示例中，他的列数有限，他只是根据哪些列具有值来隐藏或显示它们。 就我而言，我的列数是可变的，因此我需要报告来添加列。
有什么想法吗？
I'm working on a rather complex report in Sql Server Reporting Services. My SP returns a dynamic number of columns each of which are dynamically named.
Basically think of a time keeping application. Each column that is dynamic represents a time bucket that time was charged to for that team. If no time was charged to that bucket for the period of time the report covers it doesn't show. Each bucket has its own identifier which i need to be the column headers.
I have an SP that returns this all. It does it by doing a bit of dynamic SQL with an exec statement (ugly i know but I'm on SQL 2000 so a PIVOT option wouldn't work)
I can have an indefinite number of buckets and any or all might show.
I found this - http://www.codeproject.com/KB/reporting-services/DynamicReport.aspx - which is helpful but in the example he has a finite number of columns and he just hides or shows them according to which ones have values. In my case i have a variable number of columns so somehow i need the report to add columns.
Any thoughts?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
只要您知道最大列数，就可以轻松地做到这一点。
首先，使用查询结果命名列，以便您可以将其传递到查询或在那里派生它。 其次，只需构建报表，就好像它具有最大列数一样，如果它们为空则隐藏它们。
例如，我必须构建一个报告，报告长达一年的月度销售数据，但这些月份不一定从一月份开始。 我在一栏中传回月份名称，然后是报告的数字。 在 .rdl 上，我构建了 12 组列，每个可能的月份一组，并且仅使用表达式隐藏该列（如果该列为空）。 结果是报告似乎扩展到所需的列数。
当然，它并不是真正动态的，因为它可以在不知道上限的情况下扩展到您需要的范围。
As long as you know a maximum number of columns, it's possible to do this after a fashion.
First, name the columns with a result from your query, so you can either pass it in to the query or derive it there. Second, just build out the report as if it had the maximum number of columns, and hide them if they are empty.
For example, I had to build a report that would report monthly sales numbers for up to a year, but the months weren't necessarily starting in January. I passed back the month name in one column, followed by the numbers for my report. On the .rdl, I built out 12 sets of columns, one for each possible month, and just used an expression to hide the column if it were empty. The result is the report appears to expand out to the number of columns needed.
Of course, it's not really dynamic in the sense that it can expand out as far as you need without knowing the upper bound.
这是可以做到的。 我这样做了并且效果很好。
在我的方法中，您不必知道最大列数或显示和隐藏列。 使用矩阵并修改 sp 以将动态数据返回到本博客文章中提到的结构 http:// /sonalimendis.blogspot.com/2011/07/dynamic-column-rdls.html
This can be done. I did this and it works fine.
You don't have to know the maximum number of columns or show and hide columns in my approach. Use a matrix and modify your sp to return dynamic data to the structure mentioned in this blog post http://sonalimendis.blogspot.com/2011/07/dynamic-column-rdls.html
构建 2 个相关数据集，第一个用于报告内容，第二个用于其列标签列表。
报表内容的Dataset必须有固定的列数和名称。 您可以分配一些最大列数。
在此示例中，我将前 2 列固定或始终可见，并通过多值参数选择显示最多 4 列，或取决于查询条件。 和往常一样，我们也可能有一个总数。 因此，它可能如下所示：
第二个数据集及其值将如下所示：
我省略了第四个标签以证明并非所有列都被某个查询条件使用。 请记住，两个数据集都与同一查询相关。
现在创建一个名为@columns 的参数； 使用第二个数据集填充其可用值和默认值。
对于这 4 个动态列中的每一个，使用以下表达式设置列可见性：
对于每个列标题文本框，使用以下表达式：
对于总计，这里是其可见性的表达式：
这里是其可见性的表达式：价值观：
就这些了，希望对你有帮助。
另外，第二个数据集 dsColumns 还可以保存其他列属性，例如：颜色、宽度、字体等。
Build 2 related Datasets, first one for the report content, and the second one for the list of its column labels.
The Dataset of the report content must have a fixed number of columns and name. You can allocate some maximum number of columns.
In this example I have the first 2 columns as fixed, or always visible, and a maximum of 4 columns to be displayed by choice through a multivalued parameter, or depends on the query conditions. And as usual, we may have a total as well. So, it may look like this:
The second Dataset with its values will look like this:
I have omitted the 4th Label to demonstrate that not all columns are being used by a certain query condition. Remember that both Datasets are meant to be related to the same query.
Now create a parameter named, say, @columns; populate its Available Values and Default Values with the second Dataset.
For each of those 4 dynamic columns, set the column visibility with the following expression:
And for each of their column header Text Boxes, use the following expression:
As for the Total, here is the expression for its visibility:
And here is for its values:
That's all, hope it helps.
Bonus, that second Dataset,
dsColumns, can also hold other column attributes, such as: color, width, fonts, etc.我认为最好的方法是添加表中的所有列，并借助从 SP 获得的参数来编辑它的可见性属性。这将解决动态列的目的，但是在查看报告时，您会得到很多空白，您可以使用 SSRS - 动态隐藏列时保持表格宽度相同？，您的报告就准备好了
I think the best way to do it is add all the columns in your table and edit the visibility property of it with the help of arguments that you get from your SP..this will solve the purpose of dynamic column but when viewing the report you will get a lot of white-space which you can solve with SSRS - Keep a table the same width when hiding columns dynamically? and your report will be ready
我过去曾需要这样做，我得出的结论是“你不能”，但我对此并不乐观。 如果您找到解决方案，我很想听听。
想到的一个问题是，您需要使用将从存储过程返回的列的名称来定义报告，如果您不知道这些名称或有多少个，那么如何才能你定义了报告？
我对如何执行此操作的唯一想法是通过 C# 动态创建报告定义（.rdl 文件），但当时，我无法找到用于执行此操作的 MS API，而且我怀疑现在是否存在这样的 API 。 我找到了一个开源的，但我没有走那条路。
I've had the need to do this in the past and the conclusion I came to is "you can't", however I'm not positive about that. If you find a solution, I'd love to hear about it.
An issue that comes to mind is that you need to define the report using the names of the columns that you're going to get back from the stored proc, and if you don't know those names or how many there are, how can you define the report?
The only idea I had on how to do this is to dynamically create the report definition (.rdl file) via C#, but at the time, I wasn't able to find an MS API for doing so, and I doubt one exists now. I found an open source one, but I didn't pursue that route.