Problem summarizing on a formula/group 1

[xsw1971]

I have a report that contains 4 groups. In group #4 I have 3 "subtotals" and a "total". The "total" is a formula (@MonthlySubtotal) that calculates the sum of the "subtotals". I need to create a summary (grand total?) for all of the @MonthlySubtotals within each group #1. When I go to insert summary/subtotal/grand total, the @MonthlySubtotal formula is not an option to summarize on. Any suggestions?

Thanks,
JennieM

 

[kenhamady]

You have to do it "backwards" because you can't summarize a summary.

Instead, copy the subtotals and paste them to the new Group Footer. This makes them subtotals at that level. Then write a formula that adds those subtotals up like you did at the original group level. You can delete the subtotals after that if you don't need them. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[xsw1971]

Ken, this didn't work. My 1st Group#4 "subtotal" is the sum of an extended cost formula. The 2nd G#4 "subtotal" is a formula that calculates lab costs for a designated period of time. The 3rd G#4 "subtotal" is a formula that calculates engineering costs for a designated period of time. The Group#4 "total" is a formula that adds those 3 fields up. When I copied the three fields into the Group#1 footer (to make them subtotals at that level), it correctly added the extended cost formula for the entire group, but only pulled in the very last lab cost and engineering cost amounts.

Is there not a way to add up my G#4 "totals", since that field is a formula, not a summary?

Thanks for your input.
JennieM

 

[kenhamady]

It is most likely a formula that relies on summaries, which is the same thing. That is why it doesn't show up.

If pasting them in the new footer doesn't give the right total, then they aren't really CR subtotals, they are running totals or formulas that do summaries. What are they? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[xsw1971]

The first field is a true summary of G4 detail information (extended costs on lab equipment). The 2nd and 3rd fields are not CR summaries in any way - they a.) determine if lab and engineering services are needed based on a database field and if so b.)calculate each cost based on a set of parameter dates.

Then in the G4 footer I have a formula that adds those 3 fields.

What I'm trying to add now is a G1 total that adds sums all the G4 totals, but it will not let me perform that summary on that formula field.

Thank you,
JennieM

 

[kenhamady]

You might have to use variables to create a running total formula. The technique is outlined in the FAQ on running totals in the general forum. Use the 3-formula technique.
This is the only way to do a summary of a field that relies on a summary.

If you post the formulas for the other 2 fields I may be able to suggest a simpler alternative. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[xsw1971]

Using variables works! Thank you!! There's only one question left: My report is so big, I need to repeat my group header on each page. When I put the first formula(of the 3-formula approach) in my group header, it starts the running total over on each new page, which messes up the final total in the group 1 footer.

Is there a way to display the header on head page without allowing the running total to start over?

Thanks!!
JennieM

 

[kenhamady]

There is an InRepeatedGroupHeader function that will allow you to distinguish between the Repeats and originals by saying:

If InRepeatedGroupHeader
then ....

Or you could use the Group footer instead of the Group Header to do things once per group. (You might need to split it into A and B to get the incrementing in the correct order). Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/