Report builder 3.0 count with where clause

Question

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?

I'm not sure you can do this in the report. I would just do it in SQL. — Tab Alleman
If the datasource is a sql query, I agree with Tab - just do it in sql - let us know if you need help with that or if your not using a query as your datasource — tintyethan

Ian Preston Ian Preston · Accepted Answer · 2014-09-05T14:05:06

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.

Report builder 3.0 count with where clause

1 Answers