Summary Crystal Reports--at the Group Level--counting if any records satisfy the criteria from the details section.

Summary Crystal Reports--at the Group Level--counting if any records satisfy the criteria from the details section.  

In the details section, there is a field that is either "Y" or "N".  I've added a formula at the group level that states:

IF {FM_Influenza_Reporting.immunization_done} = "Y"
    THEN 1
ELSE  0

Open in new window


This appears to be working correctly when I run the report (see attached photo labeled E1).  

I then added a formula to the Report Footer section that is a SUM of my group count (the 1s and 0s as noted above).  See attached photo labeled E2.


The problem is--when that sum displays, it is showing a sum as though my formula is placed in the detail section instead of the group section.  So while I might have 145 unique patients that satisfy that question (i confirmed that number), it's displaying 348 (the total number of visits).  I'm not quite sure why due to me placing the original 'IF THIS THEN 1' formula in the group header section.

I'm open to a more elegant way of doing this--however when I attempted running totals, I ran into the same issue.  Crystal Reports 2013.

Thanks!
E1.png
E2.png
CMCITDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raghavendra HullurSoftware DeveloperCommented:
Hi,

Is there a specific reason to count the Y or N at group section and display the total at report footer level when you can directly put a sum on the formula included in the detail section?

I guess you are doing that because your summary report is at group level, but can you explain further if it's a specific requirement?
CMCITDAuthor Commented:
I just care about unique patients that did or did not have an immunization during the time period.  However, I do want to see how many encounters they had during the time period--and which provider saw them at that time (For example, a patient may have 3 encounters, with 3 different physicians.  I'd want to see 1)  Did they get a flu shot this year? and 2) How many times and what doctors missed that opportunity?)

That's why I have the detail section at all.  If I put the formula in the detail section however, it wouldn't give me a count of unique patients we missed--but instead a total number of encounters we missed (That's actually what it's doing now).  I want the unique patients missed (the group is based on person_id which is unique patients)
Raghavendra HullurSoftware DeveloperCommented:
I think from what I understand from the original post, you are checking for a condition whether immunization is done. But from the response you provided above, you need to have multiple conditions to be satisfied to return a 1 or 0 at detail section level.

If so, can you check for all the conditions and return 1 when it's satisfied. If you are getting more than 1 records satisfying the conditions for each patient, then you can create an additional formula to check a condition at group level whether the sum of records satisfying a 1 is greater than zero then return 1 else 0 and use this newly created formula to return a sum at report footer level.

I hope this helps.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Use a Running Total to sum the 1 or 0 value.
Set the evaluate option to 'On Change of Group' (and select the appropriate group level) and the reset option to NEVER.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
A short explanation for the increased total value -
The formula you created to get the 1 or 0 is being evaluated for every record so each detail is added to the summary creating the overage.

So long as you want the total in the group and report footers Ido's running total will work just fine.

mlmcc
CMCITDAuthor Commented:
That nailed it--I swear I tried that running total before but was getting the same result.  I wonder if I put it in the wrong section.  THanks again!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SAP Businessobjects

From novice to tech pro — start learning today.