如何在不丢失结构的情况下将 SSRS 矩阵导出为 CSV？
考虑以下数据源：
declare @Test table (EmpId int, ProdId int, Sold int)
insert @Test (EmpId, ProdId, Sold) values (1, 1, 1)
insert @Test (EmpId, ProdId, Sold) values (1, 2, 2)
insert @Test (EmpId, ProdId, Sold) values (1, 3, 3)
insert @Test (EmpId, ProdId, Sold) values (1, 4, 4)
insert @Test (EmpId, ProdId, Sold) values (2, 1, 5)
insert @Test (EmpId, ProdId, Sold) values (2, 2, 6)
insert @Test (EmpId, ProdId, Sold) values (2, 3, 7)
insert @Test (EmpId, ProdId, Sold) values (2, 4, 8)
select * from @Test
我创建一个 Sql Server Reporting Services (SSRS) 2008 R2 报告，其中包含一个配置如下的矩阵：
| | [ProdId] |
| [EmpId] | [Sum(Sold)] |
在预览模式下，呈现为以下内容（如预期）：
| | 1 | 2 | 3 | 4 |
| 1 | 1 | 2 | 3 | 4 |
| 2 | 5 | 6 | 7 | 8 |
但是当我将其导出到 CSV 时我明白了：
| EmpId | ProdId | Sold |
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 1 | 3 | 3 |
| 1 | 4 | 4 |
| 2 | 1 | 5 |
| 2 | 2 | 6 |
| 2 | 3 | 7 |
| 2 | 4 | 8 |
换句话说，当我导出到 CSV 时，我会丢失矩阵布局，并且每行数据都会呈现为单行 CSV。请注意，如果我导出到 Excel，报告会按预期工作，因此到目前为止，此问题似乎仅限于 CSV。我尝试将矩阵重组为表中表，但这也无法解决问题。有什么建议吗？
Consider the following data source:
declare @Test table (EmpId int, ProdId int, Sold int)
insert @Test (EmpId, ProdId, Sold) values (1, 1, 1)
insert @Test (EmpId, ProdId, Sold) values (1, 2, 2)
insert @Test (EmpId, ProdId, Sold) values (1, 3, 3)
insert @Test (EmpId, ProdId, Sold) values (1, 4, 4)
insert @Test (EmpId, ProdId, Sold) values (2, 1, 5)
insert @Test (EmpId, ProdId, Sold) values (2, 2, 6)
insert @Test (EmpId, ProdId, Sold) values (2, 3, 7)
insert @Test (EmpId, ProdId, Sold) values (2, 4, 8)
select * from @Test
I create a Sql Server Reporting Services (SSRS) 2008 R2 report that contains a single Matrix configured like this:
| | [ProdId] |
| [EmpId] | [Sum(Sold)] |
Which, in preview mode, renders to the following (as expected):
| | 1 | 2 | 3 | 4 |
| 1 | 1 | 2 | 3 | 4 |
| 2 | 5 | 6 | 7 | 8 |
But when I export it to CSV I get this:
| EmpId | ProdId | Sold |
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 1 | 3 | 3 |
| 1 | 4 | 4 |
| 2 | 1 | 5 |
| 2 | 2 | 6 |
| 2 | 3 | 7 |
| 2 | 4 | 8 |
In other words, when I export to CSV I lose the matrix layout and each row of data is rendered to a single row of CSV. Note that the report works as expected if I export to Excel, so this problem seems limited to CSV so far. I have tried restructuring the matrix as a table-within-a-table but that doesn't solve it either. Any suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这是 SSRS 2005 和 SSRS 2008 之间的报表呈现重大变化的一部分。
我找到的唯一解决方案是：
导出到 Excel，然后将 Excel 文档另存为 CSV -
这会扁平化 Excel 格式，并需要设置分组，以便每行重复父组的值。但是，一开始导出到 Excel 时会出现问题，例如，如果连续行包含相同的数据，渲染器有时会忽略数据，可以通过将受影响的列的“DataElementOutput”设置为 True（而不是“”）来停止此问题。 “自动”，允许渲染器猜测您可能认为重要的字段。
将您的报告构建为平面表格 -
这几乎违背了最初创建矩阵的目的，并且很痛苦，但是您可以提前定义列，并且可以在查询中或在文本框值中使用大量表达式并设置动态动态地执行此操作列上的可见性。但要实现这一目标可能需要创建数十或数百列来处理特定值的潜在出现。
不要升级到 2008 -
如果矩阵报告和导出格式对业务至关重要，那么确实没有什么好方法来重新创建 2008 年的功能，坚持使用 2005 SSRS 是获得旧渲染的唯一可靠方法。
资源：
This is part of a big change to the report rendering between SSRS 2005 and SSRS 2008.
The only solutions I've found are:
Export to Excel, then save the Excel document as CSV -
This flattens the Excel format, and requires the groupings to be set up so that each row repeats the values of the parent groups. However, there are issues with exporting to Excel to begin with, such as is if consecutive rows contain the same data, the renderer sometimes omits the data, which can be stopped by setting the 'DataElementOutput' to True for the affected columns instead of 'Auto' which allows the renderer to guess what fields you may think are important.
Build your report as a flat table -
This pretty much defeats the point of making a matrix to begin with, and is a pain, but you can define the columns in advance, and can do so dynamically either in the query or using a lot of expressions in the textbox value and setting dynamic visibility on the columns. But to pull this off could potentially involve creating dozens or hundreds of columns to handle the potential appearance of a particular value.
Don't upgrade to 2008 -
If matrix reporting, and the formatting of the export, are business critical, there is really no good way to recreate the functionality in 2008, sticking with 2005 SSRS is the only sure-fire way to get the old rendering.
Resources:
解决方法如下：
数据按原样。
属性>数据元素输出>无输出）。
Tablix 属性 >能见度>隐藏）。
这样您就可以完全控制屏幕显示和 CSV 输出。
Here's an workaround:
the data as-is.
Properties > DataElementOutput > NoOutput).
Tablix Properties > Visibility > Hide).
This way you have full control of the display on-screen and of the CSV output.
这在 SSRS 2016 上有时有效：
This works sometimes on SSRS 2016: