Crystal Reports - summary help

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?
LVL 7
Jeff SAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
({?INCLUDEWO}= true and {Data.IHC Patient}= 'Yes') or ({?INCLUDE}= true and {Data.IHC Patient}= 'No')

Is ?Includewo the parameter field that indicates you want to see the yeses?
Does '{?INCLUDEWO}= true' mean that you want to SEE the Yeses or you want to SUPPRESS the Yeses?
0
 
peter57rCommented:
There is nothing in your post which says that you are selecting only  YESes in your report.
A summary count will count all the records.

If you want a count of Yeses then you either create a group based on your yes/no field and count the records in each group, or alternatively you can use a Running total field and only colunt Yes values.
0
 
gopatincCommented:
Also you are talking about Checkboxes, but how do they translate into prompts for your report?
Does each check box translate as a True/False box?
Is is possible to have both checkboxes selected?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jeff SAuthor Commented:
peter57r:

I added in a group based off the IHC Patient Field and added in a running total off this. I placed the running total in Group Footer section.

Maybe I'm missing something else obvious ... but I know I'm close (thanks to you)! Now I get 2 entries off my "Total Patients" running total .... one for the sum of "yes' and one for the sum of "no". I specifically selected only to report back the Yes and got the total for this and in addition I got the sum for the "no" patients. Any thoughts?
0
 
Jeff SAuthor Commented:
gopatinc:

I took all my fields in the details tab and formatted them with the following suppress statement:

{?INCLUDEWO}= true and {Data.IHC Patient}= 'Yes' or {?INCLUDE}= true and {Data.IHC Patient}= 'No'

The (?INCLUDEWO) and (?INCLUDE) are my checkbox's inside the application. Is is possible to have both checkboxes selected? - By not placing a checkmark in either field gives you both sets of patients ... or you could also place a checkmark into each (giving the same result essentially).

0
 
peter57rCommented:
I don't understand what the problem is now.
You have the total of Yeses, which is what I thought you wanted.
0
 
Jeff SAuthor Commented:
peter57r:

The issue is I am getting two totals (I believe its due to the group I added). Its giving me a total for the "Yes" paitients and a total for the "No" patients on any selection I make.

If I select "Yes" I only want to get a total on the "Yes" patients. If I select "No", I want only the total on the "No" patients. If I dont select a "Yes" or "No", I want the total of all patients (Yes and No).
0
 
peter57rCommented:
How are you telling CR that you only want Yeses reported?
0
 
Jeff SAuthor Commented:
I took all my fields in the details tab and formatted them with the following suppress statement:

{?INCLUDEWO}= true and {Data.IHC Patient}= 'Yes' or {?INCLUDE}= true and {Data.IHC Patient}= 'No'
0
 
gopatincCommented:
The problem is because you are conditionally supressing records, but they are still being fetched from the database and hence you see both totals
Instead put this condition in the select expert. This will negate the need for the conditional supress
0
 
gopatincConnect With a Mentor Commented:
continued.....

now only the Yes OR No values are returned from the db and hence the count is easier to manage.
If putting the condition in the select is not possible (dont know why that should be a problem but nevertheless) then create 2 formula : 1 which increments by 1 for only Yes records and another which increments by 1 only for No records. Place both these fields in the details section (do NOT suppress them, instead hide them by making them very small in size (font size 1 and color white (background))
Create a 3rd formula which reads either the 1st formula or the 2nd formula and place this formula in the Group / Report Footer.
0
 
Jeff SAuthor Commented:
Thank you gopatinc and peter57r - your help and dedication lead me to the right solution. I re-coded the SQL query some and changed my techniques in Crystal and got my desired results. Many thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.