CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2008-r2
SkyeBoniwell
SkyeBoniwell

Reputation: 7092

expression to count field only if another field is distinct in the dataset

I have a column in my report that adds up the value of a field depending on that fields value, like this:

=COUNT(iif(Fields!isAcceptable.Value > 0, 1, Nothing))

However, I need that count to be dependent on the another field called testItemId. Sometimes testItemId, can appear 2 or more times in the dataset, but I need the above count expression to only count once per testItemId. So if the same testItemId appears 4 times, I only want the isAceptable value to be counted once.

I also tried COUNTDISTINCT, but that just gave me a '-1' for each row.

Is there a way to do do this in the expression?

Thanks

Upvotes: 1

Views: 1412

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

Try this:

=CountDistinct(IIF(Fields!isAcceptable.Value>0,Fields!TestID.Value,Nothing))

It will produce this:

enter image description here

UPDATE:

It says: if the row is acceptable (greater than 0) it will pass TestID to the CountDistinct function otherwise it will pass nothing (think the row is ommited). CountDistinct will get the TestID values sum by 1 when a new TestId value is passed.

Returns a count of all distinct non-null values specified by the expression, evaluated in the context of the given scope.

Reference

Let me know if this helps you.

Upvotes: 1

Related Questions