Counting a field of two columns if the values don't match in SSRS

**kristilee** · kristilee, September 3rd, 2009 06:58 PM

Hi,

I am using SSRS to develop a report to put out on Report Manager. I have a stored procedure that produces two columns that I need to compare in SSRS and count only if the auditlater value is No and the auditnow is Yes. I cant seem to get the =Count(iif(Fields!auditnow.Value = Fields!auditlater.Value, 1, Nothing), "DataSetName") to not count all the occurences. I am new to SSRS and not sure how to make this work for me. Any help would be appreciated.

**Wolffy** · Wolffy, September 3rd, 2009 07:03 PM

Why not make it easier on yourself and return a third column as in

Code:

case
  when auditLater = 'no' and auditnow = 'yes' then 1
  else 0
end as countingField

and then in the Report, just

Code:

=Sum(Fields!countingField.Value)

**kristilee** · kristilee, September 4th, 2009 10:55 AM

Thank you for your reply. Unfortunately I didn't create the stored procedure and it is like pulling teeth to get someone to help with that. Is this the only option?

**Wolffy** · Wolffy, September 4th, 2009 10:58 AM

Nope, not at all. I just like to let the server do all the hard work. The same idea can be done in SSRS. Try something like the following:

Code:

=Sum(iif((Fields!auditNow.Value ='yes' and Fields!auditLater.Value = 'no'), 1, 0)

**kristilee** · kristilee, September 4th, 2009 11:16 AM

Thanks again for your reply--I will keep fiddling with it. It seems to accept it but is now telling me I have the incorrect # of parameters for Sum.

**Wolffy** · Wolffy, September 4th, 2009 12:21 PM

Yeah, looks like a messed up the parens a bit. Not that swift with off-the-cuff SSRS functions.

Thread: Counting a field of two columns if the values don't match in SSRS

LinkBack

Thread Tools

Display

Counting a field of two columns if the values don't match in SSRS

Similar Threads

Fuzzy Match without SSIS

Backup SSRS Server

Export to Excel SSRS 2000 Unreadable Content Error

Use of COALESCE to evaluate multiple columns

How to concatenate 3 columns (conditionally 2), be comparing third and second columns

Bookmarks

Bookmarks

Posting Permissions