Expression COUNT ROW query in Report Builder

Question

Hi,

Forgive me if this sounds like a ridiculously stupid question.

I need to do a row count in report builder (Straight forward so far), I now need it to modify the field so it counts all rows except where a particular fields contains a particular value. i.e. count all records where the customers name does not = 'Smith'

I have Googled it and tried a few different versions but Report Builder is laughing in my face.

Anyone have ideas? Any helps would be greatly appreciated.

Thanks
Andrew

Answer 1

Hi @Andrew ,
Do you mean counting the number of rows except [CustomerName]="Smith"?
You could use the following expressions:

=COUNT(IIF(Fields!YourFieldName.Value<>"Smith",1,Nothing))  

or

=SUM(IIF(Fields!YourFieldName.Value<>"Smith",1,0))  

Assuming your field name is [CustomerName], please refer to the following:

=COUNT(IIF(Fields!CustomerName.Value<>"Smith",1,Nothing))  

I did a test locally,I have a table like this：

I want to count the number of rows when [ProductID] is not "680". I used the following expression:

Preview:

Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.