SQL Server 2005 Reporting Services:如何计算不为空的行?有任何计算总数的提示吗? [英] SQL Server 2005 Reporting Services: How to count rows that are not null? Any hints for calculating totals?

问题描述

在SQL Server 2005中使用Reporting Services:是否可以仅对不为空的记录进行计数?类似于Excel中的"COUNTA"?我认为这将是一个非常简单的过程，但是我尝试过的任何方法都没有奏效.例如，我尝试对"Completed"使用以下表达式，这是我要计算的一列: = count(IIF(Fields！Completed.Value ="END")) 但是，这会引发参数数量错误"错误. 已完成"的值为结束"或为空.

Using Reporting Services in SQL Server 2005: Is there a way to count only records that are not null; similar to "COUNTA" in Excel? I would think this would be very simple process, but nothing I have tried has worked. For example, I have tried using the following expression for "Completed", which is one column I am trying to count: =count(IIF(Fields!Completed.Value="END")) However, this throws the "wrong number of arguments" error. "Completed" will have a value of "End" or be null.

如有必要，我可以尝试将其用于SQL查询，但该查询已经非常复杂.

If necessary, I can try to work this into my SQL query, but the query is already incredibly complicated.

此外，关于如何计算报告总计以及如何从组总计的文档，我发现的文档很少.有人会对使用什么作为参考有什么建议吗?

Also, I've found very little documentation for how to calculate report totals, and how to total from groups. Would anyone have any recommendations on what to use as a reference?

更新:经过进一步检查，表达式= SUM(IIF(IsNothing(Fields！Completed.Value)，0,1))确实返回了适当数量的记录.我犯了一个错误的想法，认为该报告将实际报告中的记录数加上"end"作为值.由于报告在已完成"中分组，因此结束"在报告中仅针对每个唯一ID出现一次(也在已完成"上方分组).因此，我真的需要根据小计进行计数(求和?)……如果可能的话.

Update: upon further inspection, the expression =SUM(IIF(IsNothing(Fields!Completed.Value),0,1)) DOES indeed return the appropriate number of records. I made the mistake of thinking that the report would tally up the number of records in the actual report with "end" for a value. Since the report groups on "Completed", "End" only shows up once in the report for each unique ID (also being grouped on, above Completed). So I really need to be counting (suming?) based on subtotals...if that's even possible.

推荐答案

=SUM(IIF(IsNothing(Fields!Completed.Value),0,1))

这篇关于SQL Server 2005 Reporting Services:如何计算不为空的行?有任何计算总数的提示吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！