SSRS sum(distinct()) 等效项
我目前正在使用 SSRS 2008 报告，该报告返回类似于以下内容的数据集：
Job# ClientId MoneyIn MoneyOut
------------------------------
1 ABC123 10 25
1 ABC123 10 25
1 ABC123 5 25
2 XYZ123 25 50
2 XYZ123 25 50
3 XYZ123 15 15
其中，如果作业有 MoneyOut 应该等于作业的 MoneyIn 总额已正确平衡。
我遇到的问题是，当在 SSRS 的 tablix 中显示此内容时，我可以通过将字段设置为 =first(Fields!MoneyOut.Value) 来返回作业的正确 但我还需要按天对这些值进行求和，并且尝试执行 MoneyOut 值=sum(first(Fields!MoneyOut.Value)) 会产生有关嵌套聚合函数的错误。
我还尝试使用类似 =sum(ReportItems!MoneyOut1.Value) 之类的方法对文本框的值求和，这会产生错误，表明您只能对页眉或页脚中的报表项使用聚合。
所以我的问题是，是否有某种方法可以复制 SSRS 报告中的 unique() 功能，或者是否有某种方法可以汇总我不知道的文本字段的值？
I am currently working with an SSRS 2008 report that returns a dataset similar to the following:
Job# ClientId MoneyIn MoneyOut
------------------------------
1 ABC123 10 25
1 ABC123 10 25
1 ABC123 5 25
2 XYZ123 25 50
2 XYZ123 25 50
3 XYZ123 15 15
Where MoneyOut should be equal to the total amount of MoneyIn for a job if the job has been balanced out correctly.
The problem that I am running into is when displaying this in a tablix in SSRS I can return the correct MoneyOut value for a job by setting the field to =first(Fields!MoneyOut.Value) but I also need to sum the value of these by day and attempting to do =sum(first(Fields!MoneyOut.Value)) yields an error about nesting aggregate functions.
I've also attempted to sum the value of the textboxes using something like =sum(ReportItems!MoneyOut1.Value) which yields an error that you can only use aggregates on report items in the header or footer.
So my question is, is there some way to duplicate the functionality of distinct() in SSRS reports or is there some way to just total up the values of text fields that I am unaware of?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
当 SQL Server 中有 SUM(DISTINCT ...) 函数时，为什么还需要它的等效函数？
听起来您需要更改向报告提供数据的查询，而不是尝试在报告本身中执行此操作。
Why do you need an equivalent of the SUM(DISTINCT ...) function when you have it in SQL Server?
Sounds like you need to change the query supplying the data to your report, rather than trying to do it in the report itself.
如果我真正理解你的问题，你必须使用 group 对数据进行分组，并将每组中你最喜欢的数据相加。
if I understand your question truely, you must use group for grouping data and sum your favorite data in each group.
将“MoneyOut”字段替换为以下内容：
Replace your 'MoneyOut' field with the following: