动态SSRS报告
我在 SSRS 中创建动态报告时遇到问题。我的问题是：
在一个表中，我使用 SQLScripts 列存储了 SQL 脚本。如果执行这些 SQL 脚本，每个脚本会获得不同数量的列。
我的问题是，我有一份包含这些脚本按钮的报告，例如 test1、test2...之类的。如果您按 test1 按钮，这应该测试一个 SQL 脚本，并且应该显示带有该 sql 脚本中适当列的报告。
我无法为每个测试报告创建单独的报告，它们已经足够了。我有什么选择可以解决这个问题吗？
I had a problem in creating the Dynamic report in SSRS. My problem is:
In a table I have stored SQL scripts with the column SQLScripts. If you execute these SQL scripts you get different number of columns for each script.
My problem is, I have one report with buttons of these scripts, for example test1, test2...like that. If you press test1 button this should take the test one SQL script and should display the report with appropiate columns in that sqlscripts.
I can't create individual reports for each test report, they are plenty. Are there any options for me to solve this problem...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
到目前为止，我能够让它发挥作用的唯一方法是：
每个报告都有 2 个数据集。
“DataHeaders”需要具有“ReportData”中数据字段的正确名称。请务必小心，因为 SSRS 会用“_”替换空格和特殊字符。
现在，创建一个表（或矩阵）并将 DataHeaders 拖动为报表的列。 （这应该是一个分组列）。如果此时运行它，您将看到所有列，但没有任何数据。现在神奇之处来了：
创建另一个采用“DataField”参数的报表。在此报表中创建另一个表或矩阵，并将其数据集属性设置为“ReportData”。在表的 DATA 单元格中，将其设置为表达式 =Fields(Parameters!DataField.Value).Value
现在返回到您的第一个报表。右键单击并插入子报表。右键单击子报表并选择“子报表属性”。在常规下，选择您创建的第二个报告用作子报告。在参数下，选择 DataField 参数并将其值设置为类似 =Fields!DataField.Value
在我的例子中，我在此表达式中进行了一些格式化，以解决上述空格和特殊字符的问题，因为我的存储过程最初是在 ASP.NET 中使用的，这只是一个概念证明。
根据我的经验，性能也不是很好。事实上，它有点慢，尽管我还没有机会将其切换为使用共享数据集，我怀疑这会有所帮助。如果您找到更好的解决方案，请告诉我。
The only way I've been able to get this to work sofar is:
Each report has 2 datasets.
The "DataHeaders" need to have the proper name of the datafields in "ReportData". Be careful since SSRS replaces blanks and special characters with "_"
Now, create a table (or matrix) and drag the DataHeaders as the Columns of your report. (This should be a grouped column). If you run it at this point, you'll see all your columns without any data. Now comes the magic:
Create another report that takes a "DataField" parameter. Create another table or matrix within this report and set it's dataset property to be "ReportData". In the DATA cell for the table, set it to the expression =Fields(Parameters!DataField.Value).Value
Now go back to your first report. Right click and insert a subreport. Right click on the subreport and select "Subreport Properties". Under general, select the second report you created to be used as the subreport. Under parameters, select the DataField parameter and set its value to something like =Fields!DataField.Value
In my case I did some formatting in this expression to fix the above mentioned issue with spaces and special characters, since my stored procedure was initially used in ASP.NET and this was just a proof of concept.
Also in my experience the performance isn't great. In fact it was kinda slow, though I haven't had a chance to switch it to use a shared dataset, which I suspect would help a bit. Please let me know if you find a better solution.
我还没有找到完全动态地执行此操作的方法。这是一个类似的问题和一些可能的解决方案：
我如何表示未知数量的列SSRS？
I have not found a way to do this completely dynamically. Here is a similar question with some possible solutions:
How do i represent an unknown number of columns in SSRS?
您基本上需要首先从基于大量 SQL 脚本的其他数据集中创建一个“主数据集”。主数据集应包含以最简单的形式（即以简单的列表格式）呈现的数据。
最后，转到 SSRS 中的工具栏并将“矩阵”拖到报告中。矩阵表的作用类似于 Excel 中的数据透视表或 Access 中的交叉表查询，将显示数据集中的所有内容。
You basically need to create a 'master dataset' from the other Datasets that are based on your multitude of SQL scripts first.The master dataset should contain the data to be presented in it's most simplistic form, i.e. in a simple list format.
Finally, go to the toolbar in SSRS and drag a 'Matrix' into the report. A Matrix table acts similar to a pivot table in Excel or a CrossTab query in Access that will display whatever's in the Dataset.