Sometimes, when doing reports, you need to do some calculations on summary fields that are placed in a “sub-summary part”, but trying to do a calculation on the summary fields does not work correctly in this context because, when you try to do a calculation on it, the summary field is a total of the “found set”, not the “groups” you have sorted by.
Say I have a report with a sub-summary part on it and I want to do a calculation on the result of my summary. For example:
I have a report which is based on an invoices table and is sorted by the “company_name” field. The sub-summary part is set to group when sorted by the same “company_name” field.
In the body we place the “invoice_total” field to show the total of each invoice for each company.
In the company sub-summary part we place the “invoice_total” field also, which will now display the total of all invoices for each company.
Now we need to get the total of all invoices for each company so we can do a calculation on it, but if we just use the “invoice_total” summary field in the sub-summary part in our calculation field, “invoice_total” will not be the total of all invoices per company, but instead it will be the total of all invoices in our found set of invoices, even if we place this new calculation field in the sub-summary part.
To solve this we now need to use the
function to get the total of all invoices for our “groups” which is “company_name”.
Returns the value of summaryField for the current range of when the is by breakField.
The first parameter is the summary field you want to get a summary of per group in your report, which in our case is the “invoice_total” field.
The second parameter is the field that you are sorting by for your groups, “company_name” field.
First we need to create a new calculation field and name it “company_invoice_total”.
Place the calculation field into the sub-summary part that is grouping by “company_name” when sorted.
In the calculation place this line:
GetSummary ( invoice_total ; company_name )
What this calculation does is, because the calculation field has been placed in the sub-summary part and we are using “GetSummary”, instead of the summary field “invoice_total”, it now gets the total of all invoices for each company name group.
Now we are able to do a calculation on the total of all invoices per group.
All we have to do is create a new calculation field and place it in the sub-summary part that is grouped by company and use the calculation field “company_invoice_total” that we just created. For example, in the calculation field place this line:
company_invoice_total * 2
Now go back to browse mode and you will see that the total now for each company has doubled, but if we did this:
invoice_total * 2
It would just show the total of all invoices doubled for every company.