Crystal Reports - summary help
Posted on 2007-07-30
I am having an issue with a Summary Field. In my SQL Query, I have a Field titled "IHC Patient" ... which is based off a Case Statement.
My Case Statement in my SQL Query:
CASE WHEN Cast(PI.EligibilityNotes as varchar(8000)) like '%Declined IHC%'
WHEN Cast(PI.EligibilityNotes as varchar(8000)) like '%Decline IHC%'
WHEN Cast(PI.EligibilityNotes as varchar(8000)) like '%IHC%'
END AS [IHC Patient],
In my report, I have the patients name, patientID (distinct), DOB, Sex, Address, Phone ... etc in my details row. In the Database, a user can check a box and they get all patients with a "yes" on IHC or hit another checkbox and get all the "no"'s. The client came back and requested a total count of patients .... so I have tried summing this by selecting Insert and then down to Summary ... and then I selected Count. I asked it to count the patientID field as it was distinct.
What its doing now is summing all patients and not the distinct ones. For example, I have one patient in my DB set to "No" and 147 set to "yes" ... when I select the checkbox field to just give me the "no"'s its giving me a total count of 148 not 1. Essentially, its totaling the 2 together and I need it not to. Any thoughts?