CODEWITHSUNDEEP
reporting-servicesreportbuilderreportbuilder3.0
Sander
Sander

Reputation: 95

Report builder 3.0 count with where clause

For one of our reports I'm trying to get a count of rows that have the column Canceled with value 1. In the past I used the solution I found on Stackoverflow to use the Sum function with IIF, i.e.

=Sum(iif(Fields!Canceled.Value="True", 1, 0))

But now my source data has multiple rows for one booking_id so I need to add a distinct on that column. In SQL I could easily do

SELECT COUNT(DISTINCT(booking_id)) FROM Booking WHERE Canceled=1

But I can't figure out how to get the same behaviour in Report Builder 3.0. Any ideas?

Upvotes: 2

Views: 6548

Answers (1)

Ian Preston
Ian Preston

Reputation: 39586

Doing this in T-SQL, if possible, as suggested in the comments is not a bad idea, but you can do this in the report with an expression like:

=CountDistinct(IIf(Fields!Canceled.Value = 1, Fields!booking_id.Value, Nothing))

CountDistinct will ignore Nothing (i.e. Null) records, so you can apply a CountDistinct to an IIf expression that returns booking_id or Nothing based on Canceled.

Upvotes: 2

Related Questions