SSRS: Count when the field is Null and when the field is blank-CodePudding

I am trying to count the number of null records and also the number of blanks at the same time, sometimes the field I have is Null but also can not be null but is blank.

I have tried this so far but get an aggregate error message when I run the report, but no issues in the expression box.

=SUM(IIF((IsNothing(Fields!TransferStock.Value)=True) OR (Fields!TransferStock.Value=""),Nothing,Fields!TransferStock.Value),1,0)

Thanks

CodePudding user response：

Try the following

=SUM(IIF(VAL(Fields!TransferStock.Value)=0, 0, 1))

VAL will return 0 for NULLs, Empty String and Spaces.

Note: If you have values of Zero that you want to exclude from the count then use this instead

=SUM(IIF(LEN(TRIM(Fields!TransferStock.Value))=0, 0, 1))

This one assumes the TransferStock is text (so it could contain and empty string)