SSRS: How to count true rows

Question

I have a report with a dataset that has a column with booleans. In the table footer I want to display x / y where x is how many rows that were true and y is how many rows there was total.

Currently I have this:

=Count(Fields!Transfered.Value).ToString() + " / " + CountRows().ToString()

But the first becomes same as the last part. I then tried this:

=Sum(Fields!Transfered.Value).ToString() + " / " + CountRows().ToString()

But that generates an error, which I guess I can understand. I thought that if I converted the booleans into numbers where true is 1 and false is 0, then it could work out nicely. But how can I do that? Or is it a smarter way to do this all together?

Update: Have now also tried

=Sum(CInt(Fields!Transfered.Value)).ToString() + " / " + CountRows().ToString()

And got a negative result... O.o

Also found a way that worked, which I posted as an answer. But I won't accept it as an answer yet incase someone has a better way to do this =)

Answer 1

I can tell you why things went wrong...

• Count(Fields!Transfered.Value) is simply the number of rows. aka CountRows()
• Sum(Fields!Transfered.Value) is trying to aggregate "true" or "false" = error
• Sum(CInt(Fields!Transfered.Value)) will sum -1 and 0 because VB.NET true = -1
• Sum(IIF(Fields!Transfered.Value, 1, 0)) fixes the sign issue = your solution

To avoid the extra IIF, you could use negate the sum of all the -1s

= -Sum(CInt(Fields!Transfered.Value)).ToString() + " / " + CountRows().ToString()

In the end, either solution would be OK and both are equally kludgy

Answer 2

Figured out a way to do it, although there is probably a better more clear and logical way...

=Sum(IIF(Fields!Transfered.Value, 1, 0)).ToString() + " / " + CountRows().ToString()

Answer 3

I came across this today and at least verified that I wasn't the only one that got a negative sum value for bools out of SSRS. Thanks.

My solution was to use ABS for absolute value. I like that better than just negating the expression solely because it's easier to see visually where the '-' might be missed if you're not careful in reading the expression.

but it's essentially the exact same thing