TABLE OF CONTENTS
- SUM up a value of a field in the Total Section of a template
- Add SUM totals to Project and Invoice Report
- Calculate totals per Group in Financial Reports with GROUPSUM
SUM up a value of a field in the Total Section of a template
Since 6.6. SP2 you can calculate a total value (SUM) of the Input of a number value, including Object Customs, in Financial, User and Object Reports.
We have added the ability to add a sum value in the Footer section of Financial, Object and User Reports.
This total sum can be calculated from any number field from any sections/group in the Print Designer, and even from any number value, from custom fields.
This is very helpful if you use string operators to break out numbers from different categories to different columns. Before you could not get a total of these "break out" values but now this is possible.
How to do it?
In the first Footer section, create a new Text Element by clicking on the green plus icon, and in the Text field write:
SUM([the name of the element you want the sum for])
This is an example when using a Custom field:
In this example we have Objects with a custom field called [Purchase Price[ and you want to see the total value of the Purchase Price for all Objects in the report. When you design your Financial report one of the Groups could be ‘Object’.
In the Footer or Total section of the Financial Report you create a new Text Element from the green plus icon, and write (don't add a space after the word SUM):
SUM(Purchase Price)
The system will calculate the total of that field in the SUM field in the total section.
What numbers can I SUM up?
The sum can only be calculated if the element is shown in "Money", so if you need to add hour fields that show 6.5 and 6.0, this won't work. You will need to add a String Operator > Math, see here:
Rounding: Standard, Decimals 2 and Format: Money.
Then the SUM() will work.
The fields that you need to sum together must be visible in the report. If you don't want the hours to show 6,00 do the following:
Create a copy of the field you need to sum up. Rename the copy and then add the math operator above to it. Create the SUM on this new field, but then move this field outside of the white report page so it won't be printed.
If you untick the copied field, the sum will stop working.
Limitations:
The SUM element cannot be exported to excel
The SUM element cannot be used in a String Operator
The SUM value will only show decimals higher than .00 (e.g. will show .92, but not .00)
This command can be used for any field where the Input of that element is a number (whether it’s a money, Quantity or typed number in any form).
Add SUM totals to Project and Invoice Report
That allows you to create Totals based on elements created with String Operators or values from Custom fields. (v6.7+)
If you have created some new elements in the Project/Invoice Report Details or Element Row section using string operators or if you have number values added to custom fields you can create a Total of these values in the Project and Invoice Reports.
In the Report Total section click on the green plus icon and select "Text". In the Text box add the word SUM followed by a parenthesis containing the name of the element that should be summed up, for example if you created a new element called "Sell Row Editors", then the formula in the Text field should be SUM(Sell Row Editors). This will sum up all values from the selected element.
Note that if the number is round, i.e. does not have any decimals, the total will not show 00 for decimals.
Calculate totals per Group in Financial Reports with GROUPSUM
From v6.8 you can also add a sum to any ‘Total For xxx’ Financial Report grouping with the new option: GROUPSUM(fieldname)