This is a migrated thread and some comments may be shown as answers.

how to count non-null rows or rows that are not 1

1 Answer 136 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
No
Top achievements
Rank 1
No asked on 01 Nov 2013, 10:46 AM
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?

1 Answer, 1 is accepted

Sort by
0
Squall
Top achievements
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))
Tags
General Discussions
Asked by
No
Top achievements
Rank 1
Answers by
Squall
Top achievements
Rank 1
Share this question
or