How to get an accruate sum of the Yes values?

[wanzek1]

I need to figure out a way to get a sum of just the yes values. It puts a yes if the employee should get Per Diem that day and a no if they do not. I have done this over and over in Crystal reports and in Crystal I would just create a formula to put a 1 for yes and a 0 for no. Sum it and be done. However, I have tried this in Report Builder and it doesn't work. When there are 4 days (3 yes and 1 no) no matter what I do I get 4.

Any ideas?

 

[Kaylech]

Hi, I think you can do this by either SUM() or COUNT().

Assuming that your fieldname that records the Yes or No is called perDiem, then something like this should work:

//Using COUNT
COUNT(IIF(Fields!perDiems.Value="Yes",1,Nothing))

//Using SUM
SUM(IIF(Fields!perDiems.Value="Yes",1,0))

 

[CorwinFoo]

You can use the Case Stmt with SUM like this:

SUM
(
CASE
WHEN fieldname = 'Yes' THEN 1
WHEN fieldname = 'No' THEN 0
ELSE 0
END
) AS YesCount

If you COUNT, you'll get the count of all records.