CODEWITHSUNDEEP
sql-servervb.netreporting-services
Svish
Svish

Reputation: 158051

SSRS: How to count true rows

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 =)

Upvotes: 6

Views: 14404

Answers (4)

Guish
Guish

Reputation: 5160

Here is a way where you count on a grouped value

=CountDistinct(IIF(Fields!TrueOrFalseField.Value, 
                   Fields!GroupedField.Value,
                   "BadValueToBeRemovedFromCount"))
              -IIF(CountDistinct(Fields!TrueOrFalseField.Value)=1 
                                 and First(Fields!TrueOrFalseField.Value)
                    , 0, 1)

The second part 

-IIF(CountDistinct(Fields!TrueOrFalseField.Value)=1 
                                     and First(Fields!TrueOrFalseField.Value)
                        , 0, 1)

remove 1 from the count if it has True and False value or if there are only one distinct false value.

Upvotes: 0

Vonvee
Vonvee

Reputation: 11

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

Upvotes: 1

gbn
gbn

Reputation: 432261

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

Upvotes: 9

Svish
Svish

Reputation: 158051

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()

Upvotes: 1

Related Questions