troubleshooting Question

Crystal Reports - summary help

Avatar of Jeff S
Jeff SFlag for United States of America asked on
Crystal ReportsProgrammingDB Reporting Tools
12 Comments2 Solutions1392 ViewsLast Modified:
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%'
           THEN 'No'
           WHEN Cast(PI.EligibilityNotes as varchar(8000)) like '%Decline IHC%'
           THEN 'No'
           WHEN Cast(PI.EligibilityNotes as varchar(8000)) like '%IHC%'
           THEN 'Yes'
           ELSE 'No'
           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?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 12 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros