• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1371
  • Last Modified:

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?
0
Jeff S
Asked:
Jeff S
  • 5
  • 4
  • 3
2 Solutions
 
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
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
peter57rCommented:
({?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
 
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
 
gopatincCommented:
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now