Summary Field Help needed

[TechUser23]

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?

 

[lbass]

So the case when is in a SQL expression? Create a formula like this:

if {%IHC Patient} = "No" then 1

Then insert a summary (sum, NOT count) on this.

-LB

 

[TechUser23]

lbass,

Am I creating this in SQL or in Crystal? Can you elaborate just a tad more for me? I really appreciate the help!!!!!

 

[lbass]

Is your "case when" a SQL expression? I meant for you to then use it in a new formula in field explorer->formula->new.

-LB