Formula - Sum of Multiple Group Header data

[Former Member]

Hi,

I'm looking to create a formula in Group 1 Footer  that sums data from the Group 3 Header and Group 4 Header.  I've done variations of this, but always in some way a Sum of a Sum, where the Header level is already in the picklist.

Having a devil of a time getting the syntax right.

Right now I have:

sum({Header 3},{Field}) + sum({Header 4},{Field})

What am I doing wrong?  How specifically does Header 3 need to be referenced?

Thanks!

Matt

[abhilash_kumar]

Hi Matthew,

Do you have already have a Sum on the Group Headers 3 and 4? Are you trying to add these two figures and show the result on Group Footer 1?

If yes, then try this:

1) Create a formula with the code below and place this on the Group Footer #3:

whilepringtingrecords;

numbervar gr3;

gr3 := gr3 + Sum({measure_field}, {group_field_3})

2) Create another formula and place this on the Group Footer #4:

whilepringtingrecords;

numbervar gr4;

gr4 := gr4 + Sum({measure_field}, {group_field_4})

3) Create this formula to display the results on Group Footer 1:

whilepringtingrecords;

numbervar gr4;

numbervar gr3;

gr3 + gr4

4) One last formula to reset the variables and this goes on the Group Header #1:

whilepringtingrecords;

numbervar gr4 := 0;

numbervar gr3 := 0;

Hope this helps.

-Abhilash

[Former Member]

No, I currently have no summary fields anywhere on the report.

The groups are:

Group 1 - Invoice Number

Group 2 - (redundant)

Group 3 - Line Items on the Invoice

Group 4 - BOM details for a Line Item

I want to just create a formula that sums all the Line Item and BOM details for an Invoice.

Seems to me that I've done this before without using buckets.

Am I wrong?

[abhilash_kumar]

OK. The formulae above should work just fine.

-Abhilash

[Former Member]

Okay,

What I'm asking for is a different lesson.

If I have Field 1 in the Detail Section and I create a Summary using: Insert - Summary, I can get the same thing by creating a formula sum({Field 1}), correct?

Now if I have Field 2 in Group 2 Header that I want to summarize using Insert - Summary, unless I'm going crazy, I have accomplished this in the past by creating a formula.

What is the syntax for doing this?

Thanks!

[abhilash_kumar]

Hey Matt,

Sum({Field1}) will show you the sum of all rows for that column.

Sum({Field1}, {Group1}) shows the sum of all rows for that column in a particular group.

If you insert a summary on a Field that's on the Group Header 2, it will only take into account the first row in that group. It will work great as long as there is only 1 row under Group Header 2. If there are multiple rows then your summaries are going to be incorrect.

Hope this makes sense?

-Abhilash

[Former Member]

Definitely, thank you.

As I was exploring, I remembered that, but it was still driving me a little nuts that I couldn't get the syntax right.  I have about 200 reports that I've created over the last two and a half years, and I know I have a few examples, but I wasn't looking forward to scanning through them all.  Our hardware support team wiped dates off of all these files what the migrated to a new server, which made it even more daunting.  Didn't even think field,group, I was trying all variations of group,field.

Thank you for your help, Abhilash, and your patience.