如何在不丢失结构的情况下将 SSRS 矩阵导出为 CSV?

发布于 2024-11-06 00:40:04 字数 1226 浏览 10 评论 0原文

考虑以下数据源:

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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

等风来 2024-11-13 00:40:04

这是 SSRS 2005 和 SSRS 2008 之间的报表呈现重大变化的一部分。

我找到的唯一解决方案是:

  1. 导出到 Excel,然后将 Excel 文档另存为 CSV -
    这会扁平化 Excel 格式,并需要设置分组,以便每行重复父组的值。但是,一开始导出到 Excel 时会出现问题,例如,如果连续行包含相同的数据,渲染器有时会忽略数据,可以通过将受影响的列的“DataElementOutput”设置为 True(而不是“”)来停止此问题。 “自动”,允许渲染器猜测您可能认为重要的字段。

  2. 将您的报告构建为平面表格 -
    这几乎违背了最初创建矩阵的目的,并且很痛苦,但是您可以提前定义列,并且可以在查询中或在文本框值中使用大量表达式并设置动态动态地执行此操作列上的可见性。但要实现这一目标可能需要创建数十或数百列来处理特定值的潜在出现。

  3. 不要升级到 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:

  1. 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.

  2. 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.

  3. 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:

黯然 2024-11-13 00:40:04

解决方法如下:

  • 将 CSV 输出源切换为显示所有内容的额外 tablix
    数据按原样。
  • 禁止原始 Tablix 输出到 CSV (Tablix >
    属性>数据元素输出>无输出)。
  • 隐藏额外的 tablix,使其不显示。 (Tablix > 右键单击​​ >
    Tablix 属性 >能见度>隐藏)。

这样您就可以完全控制屏幕显示和 CSV 输出。

Here's an workaround:

  • Switch the source of CSV output to be an extra tablix which shows all
    the data as-is.
  • Disable the original tablix from outputting to CSV (Tablix >
    Properties > DataElementOutput > NoOutput).
  • Hide the extra tablix so it doesn't display. (Tablix > Right Click >
    Tablix Properties > Visibility > Hide).

This way you have full control of the display on-screen and of the CSV output.

南…巷孤猫 2024-11-13 00:40:04

这在 SSRS 2016 上有时有效:

  1. 选择缺少的列,
  2. 仅打开属性
  3. 更改数据 --> DataElementOutput 作为输出
  4. 此操作的工作位置有所不同。例如,当我在 Tablix 上执行此操作时,它不起作用,但当我在列上执行此操作时,它起作用了。您可以尝试创建一个表来测试此后是否效果更好。不幸的是,您无法在页脚上制作表格,这在页脚上特别有用。

This works sometimes on SSRS 2016:

  1. Select the missing column(s)
  2. Open Properties
  3. Change Data Only -- > DataElementOutput as Output
  4. It varies where this operation works. For example when I did this on Tablix it didn't work, but it worked when I did this on the column. You could try create a table just to test if this works better after that. Unfortunately you can't make tables on footers, where this would be especially useful.
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文