Conditionally count based on other field data in SSRS Report

Question

I am working SSRS report, I have a field named as Details and I would like to get the count of other field SerialNumber.

So in short I want to get the total count of SerialNumber which has no Details.

I tried below but not working. It always give the total count count without considering blank Details

=CStr(COUNT(IIF(Not(IsNothing(Fields!Details.Value)),Fields!SerialNumber.Value,0)))

How can I achieve this by expression? Please help.

Note performing a logical negation using `Not` operator in `isNothing` function. You actually need the null `Details` values not the `not null`. Try: `COUNT(IIF(IsNothing(Fields!Details.Value),Fields!SerialNumber.Value,Nothing))` — alejandro zuleta, Feb 08 '16 at 13:46
Share a sample of your dataset and the expected results in order to reproduce your issue. — alejandro zuleta, Feb 08 '16 at 14:11

Pedram · Accepted Answer · 2016-02-09T05:34:52.003

Issue was with my data. In dataset I was fetching rows based on Isnull() like below

SELECT CASE 
            WHEN 'Y'='Y' THEN ISNULL(Code ,'') + '  ' + ISNULL(Description ,'')
            ELSE CASE 
                      WHEN ISNULL(Code ,'')='' THEN ISNULL(RefCharge ,'') 
                          +'  '+ISNULL(RefDescription ,'')
                      ELSE ISNULL(Code ,'')+'  '+ISNULL(Description ,'')
                 END
       END               AS Details

So it was showing (3 blank spaces) and my expression was.

=COUNT(IIF(Fields!Details.Value<>"",Fields!Number.Value,Nothing))

Finally I tried below, now it's working fine now.

=COUNT(IIF(Trim(Fields!Details.Value)<>"",Fields!Number.Value,Nothing))

Note: So I have noted that whenever we need to check such kind of conditions we must have to use Trim()

Conditionally count based on other field data in SSRS Report

1 Answers1