| Author |
Message |
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
  
Topic: Sum Question
Posted: 05 Aug 2010 at 6:29am |
I am using several fields on a 'Project' report, two of the them are currency fields (Direct Charges, Related Charges).
I am summing these two fields in a Grouped row for projects. I then used a formula to add these two fields together for a 'combined total' on the same Grouped row.
How do I show the 'combined total' in the Report Footer? I tried adding the same 'combined total' formula to the Report Footer which didn't sum accurately. I am assuming I may need another formula?
Here's a sample:
GH1 - Project
Detail - Direct Charges, Related Charges
GF1 - Sum(Direct Charges), Sum(Related Charges), Combined Total
RF - TotalSum(Direct Charges), TotalSum(Related Charges), TotalSum(Combined Total)
|
 IP Logged |
|
|
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 05 Aug 2010 at 7:30am |
|
To make this a bit easier to understand, if I add two fields together in a 'Group' row using a formula to come up with a combined total, do I need to create another formula to show the sum of the combined total in the 'Report Footer' row?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Aug 2010 at 7:40am |
sort of. the easier solution is to make one formula adding the fields together at the row level and then you can do a summary of that formula field at any level of the report using the insert summary function (sigma sign) Edited by DBlank - 05 Aug 2010 at 7:41am
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 05 Aug 2010 at 8:33am |
I thought that might be the case (at the row level). The problem I have is that there are actually 3-grouped fields, (School, Project, Project Num) and would like to show the summary in one or more of the groups.
In the 'Details' row, I can't sum the 'Direct Charges' and the 'Related Charges' since they are on separate lines (Detail rows). I was able to sum them in the 'Project' Group, and that's where I created the formula to show the 'Combined Total' for these fields. I also created another formula to subtract the 'Project Funding' from the 'Combined Total' to come up with a 'Balance'. See sample below......
Direct Charges + Related Charges = Combined Total - Project Funding = Balance
This all works well on the Grouped row for Projects, but when I try to sum the 'Project Funding' and the 'Balance' in the report footer, it doesn't calculate it correctly.
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 05 Aug 2010 at 8:42am |
|
I can see part of my problem. Because there are several rows of 'Details', based depending on the 'Direct Charges' and 'Related Charges', and only one Project Funding amount, I may need to change the table links?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Aug 2010 at 8:45am |
Not sure I understand how you are doing this.
using a formula field of SUM(field) sums all the rows regardless if you place it in a group footer or report footer. It will show the same value.
SUM(field,group) will change values at each group level.
from what you are describing I think you want to conditionally sum values which you would need to do with a shared variable formula or a Running Total. Edited by DBlank - 05 Aug 2010 at 8:46am
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 05 Aug 2010 at 8:53am |
I'm not sure about the shared variable, but have worked with the Running Totals.
Another solution I came up with is to export the report to Excel, and add the Project Funding and Balance up, since these are the only two fields that aren't adding up correctly in the report footer.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Aug 2010 at 8:55am |
|
Can you show some row level data and how you want to summarize it?
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 05 Aug 2010 at 9:20am |
GH1 - School
GH2 - Project Num
Gh3 - Project (I have to Group to his level since several different project descriptions have the same project number)
PH - Description Direct Charges Related Charges Combined Total Project Funding Balance
Detail Force account $1,000
Detail Purchase order $500
Detail Force account $750
Detail Consultant Fee $1,200
GF3 (sum) $1,500 + $1,950 = $3,450 - $5,000 = $1,550
‘Project Funding’ is only at a Grouped level, not on each detailed row. ‘Balance’ is a formula at the Grouped level, not on each detailed row.
Thanks for your help with this. However, I have to go to a jobsite now and will work more with this tomorrow morning. Thanks again.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Aug 2010 at 9:44am |
although you are displaying the project funding at a group level it exists as a data element at the detail level. So you use a Running Total to create the value for both display and use in calculattions.
Likely you have one repeating value of $5000 ofr every detail row within a project (Grouplevel3)
create an RT as..
Name=Project_Funding_GF3
Field to sumamrize=project funding
Type= Maximum
Evaluate=for each record
reset=on change of group (group3)
place in GF3 and you should see the same correct value
You can now get your final value of 1550 as
SUM(directcharges,group3)+SUM(relatedcharges,group3)-#Project_Funding_GF3
You will have to create a different RT for a final summary
Name=Project_Funding_GF3
Field to sumamrize=project funding
Type= SUM
Evaluate=on change of group(group3)
reset=never
place in RF and you should see the SUM of each grouped 3 data
|
| IP Logged |
|
|
|
