how to count non-null rows or rows that are not 1
I have a report and I want to get some totals where I show a total or all the rows that exist (there are rows that have null values and I don't want those counted) or I have a different column that is either zero or one and I want a count of all the 1's.
Problem is I am getting all the rows, regardless of the actual value.
I tried stuff like this for the 1's
= Count(Fields.MyValue = 1)
but that returns the same thing as
= Count(Fields.MyValue)
Or looking for the values that are null I tried something like this
= Count(Fields.MyValue is not null)
How do I get a count of just the non-nulls or the 1's?
I have a report and I want to get some totals where I show a total or all the rows that exist (there are rows that have null values and I don't want those counted) or I have a different column that is either zero or one and I want a count of all the 1's.
Problem is I am getting all the rows, regardless of the actual value.
I tried stuff like this for the 1's
= Count(Fields.MyValue = 1)
but that returns the same thing as
= Count(Fields.MyValue)
Or looking for the values that are null I tried something like this
= Count(Fields.MyValue is not null)
How do I get a count of just the non-nulls or the 1's?
1 Answer, 1 is accepted
0
Squall
Top achievements
Rank 1
Rank 1
answered on 04 Nov 2013, 05:14 PM
Hi, you can use IIF in the Count aggregate:
=Count(IIF(Fields.MyValue is null,0,IIF(Fields.MyValue=1,0,1))
=Count(IIF(Fields.MyValue is null,0,IIF(Fields.MyValue=1,0,1))