分离 SQL 报告层和表示层？
我正在使用 SQL Server 报告服务。我的大多数查询都以报告的形式运行，这些报告只是导出为 PDF。
我想知道哪种类型的计算（如果有的话）应该在实际的 SQL 查询中完成，哪些应该使用 SSRS IDE（商业智能工作室）进行计算。
例如，如果我有一份 12 个月的销售报告（12 个月），需要最近 3 个月的销售平均值，那么该平均值应该在哪里计算？
大多数计算/聚合应该在表示层中完成吗？有什么例外吗？
I'm using SQL Server Reporting Services. Most of my queries run as reports which are simply exported as PDF.
I'd like to know which type of calculations, if any, should done in the actual SQL query and which should be calculated using the SSRS IDE (Business Intelligence Studio).
For example, if I have a 12 month sales report (12 months) which requires a sales average of the 3 most recent months, where should that average be calculated?
Should most calculations/aggregations be done in the presentation layer? Any exceptions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这可以通过两种方式来看待：
，哪里更有效。其中任何一个的答案都是“视情况而定”。对于还显示趋势的 12 个月报告示例，如果您有几个参数可以更改用于获取数据的逻辑，我会尝试在 SQL 存储过程中执行尽可能多的操作（甚至是平均值）并使报告变得相当“愚蠢”，只显示结果。如果报告非常简单或者逻辑仅特定于该报告，那么在报告中可能会起作用。
为了可重用性，我喜欢用 SQL 做事。对于复杂的显示内容（热图、图表等），在报告中可能更好。
This could be looked at in 2 ways:
The answer to either of these is "it depends". For your example of a 12-month report that also displayed trends, if you have a couple of parameters that change the logic used to get the data, I'd be tempted to do as much as possible in the SQL stored procedure (even the averages) and make the report pretty "dumb" to just display the results. If the report is very simple or the logic is specific to that report only, then in the report might work.
For re-usability I favor doing things in SQL. For complex display stuff (heatmapping, graphs, etc.), in the report is probably better.
保持报告的逻辑性。 SQL Server 将为您提供数据，但最好在报告中保留自定义逻辑。
Keep the logic of the report with the report. SQL Server will give you the data but it is best to keep customized logic with the report.
我不会让报告往返数据库进行计算。如果您的存储过程分组和排序与您的报告布局相匹配，那么 SSRS 在进行聚合计算方面非常高效。
I would stay away from having the report make roundtrips to the database for calculations. If your stored procedure(s) grouping and ordering matches your report layout then SSRS is pretty efficient at making aggregated calculations.