pedram February 2016

Conditionally count based on other field data in SSRS Report

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.

Answers


pedram February 2016

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()

Post Status

Asked in February 2016
Viewed 3,577 times
Voted 6
Answered 1 times

Search




Leave an answer