Sum on Group Level

[bobhagan]

I’m trying to create a report for an Emergency Food Program that counts families with Adults Only, Elderly Only, Adults/Elderly, Adults/Children, Adults/Elderly/Children.
My query creates for each month’s visits records that show fam_id, client_id and dob.
I’ve done a report that groups families and counts on the detail line 1 based on dob for adult. elderly and child. On the family group level I have functions such as: Adult only

IF Sum ({@If_Adult}, {@Family}) > 0 AND ISNull(Sum ({@If_Senior}, {@Family})) AND ISNULL(Sum ({@If_Child}, {@Family})) THEN 1

This displays fine on the family footer. Unfortunately, there doesn’t seem to be any way to sum these for the report summary. I’m used to R&R where this would be easy. What am I missing?

Bob Hagan

 

[kenhamady]

You can do this using variables. You will need a running total field using the 3-Formula technique described in faq149-182. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[bobhagan]

Ok. I thought that might be the case.

I tried running totals when I first tried to convert my old R&R reports to Crystal I finally realized summarizing from the detail line solved most of my problems. But this type doesn't "know" if its met the condition until it gets to the end of the group.

Thanks
Bob Hagan

 

[kenhamady]

Actually, the SUM functions in the formula you listed are available at the beginning or end of the group. That would be a "PreProcessed" total in R&R terminology. If you place the formula you show above in the GH or Details, you will see that you get the same value.

So you can place your evaluate formula for your running total either in the group Header,or in the details or in the Group footer - depending on how often you want to 'Accumulate' your total. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[bobhagan]

Yoiks! I'll have to think about that.

I actually got the running total to work with a detail

whilePrintingRecords ;
NumberVar lnAdultOnly ;
IF Next({food_fams.fam_id}) <> {food_fams.fam_id}
AND {@If_Adult}> 0 AND
ISNull({@If_Senior}) AND
ISNULL({@If_Child})
THEN lnAdultOnly := lnAdultOnly + 1

the start function in the report header, and the display in the grandtotal footer. I want to count an occurrence only once per family. I looked back at some questions I'd posted long ago and remembered the Prior/Next
functions to find the family break.

Bob Hagan

 

[kenhamady]

rather than using previous/next, just put the accumulation formula into a group footer or header. Then it will only occur once per group and only evaluate the first or last record of the group. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[mdw1958]

I have experienced a problem in the past where the accumulator function executed twice for the last record in the last group which lead to the last value being added twice. We had to use the previous function to test a unique key to insure that this did not happen. I never received an adequate explanation for this problem, so I just made a practice of testing to prevent double counts if I had to use running totals.
It does not sound like you have experienced this problem; therefore, I am wondering if it is safe to go swimming again? (Sorry, Jaws was on TBS again.)

 

[kenhamady]

I have never seen this problem with variables. What version was it? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[bobhagan]

<<rather than using previous/next, just put the accumulation formula into a group footer or header. Then it will only occur once per group and only evaluate the first or last record of the group. >>

That actually does work for me.

Thanks
Bob Hagan

 

[mdw1958]

I think this happened back in version 6. Since version 7, I do most everything in stored procedures and use sum() as much as possible. We do home health care software and I have reports that want counts by discipline and status. I use a calculated field in sp or a formula to return 0 or 1 and use sum to present the count.
Way back when, I implemented these with running totals ( manually done with global variables ) and had the problem with updating the totals twice as well as problems with groups that spanned a page. Once I got a work around, I just did not go back to the original solution.
I have recently observed that I can not recreate some of those early problems. Unfortunately, the problems I observed did not happen all of the time even then. The original problems were found by clients. That leaves a lasting impression.

 

[kenhamady]

These problems were usually caused by invisible or visible repeating group headers. Properties like &quot;keep group together&quot; and &quot;Repeate Group Header on each new page&quot; could often cause these problems. Some of these problems also showed up in the 'automatic' running totals in v7, but were cleaned up in v8.

However, the &quot;repeat group header&quot; can still make a formula evaluate twice, so you have to be careful where you place a formula that evaluates. You also have to be careful how you write the evaluate if it is using a condition. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dgillz]

You can use the statment:

Not InRepeatedGroupHeader

to keep the formula from evaluating more than once per group. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com