Solved

Sum of a Sum Formula Field

Posted on 2011-03-07
18
602 Views
Last Modified: 2012-08-14
I need to Sum a Formula Sum field.  I've heard that to “Sum a Sum field” you need to use manual summaries.  Here’s what I have.  

FORMULA – @TotalCost

{WORKORDER.ACTMATCOST}+{WORKORDER.ACTLABCOST}+ {WORKORDER.ACTTOOLCOST} + {WORKORDER.ACTSERVCOST}

TotalCost is now displayed in each Location Header – works great.  Now I want to take the @TotalCost of each Location – and get a Grand Total in the Report Footer?  

Thank you for your assistance.  
0
Comment
Question by:washvt
  • 7
  • 5
  • 4
  • +1
18 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 167 total points
ID: 35061379
Your formula suggests that you are adding up values from the first record in each group.  Is that what you intend?

To create a grand total you need 2 more formulas.. I am assuming these are currency fields; if they are number you need to modify the variable declaration.

In the report header

Whileprintingrecords;
Currencyvar Gtot:=0;
""
Change  your current formula field :

Whileprintingrecords;
Currencyvar Gtot;
Gtot:=Gtot+ {WORKORDER.ACTMATCOST}+{WORKORDER.ACTLABCOST}+ {WORKORDER.ACTTOOLCOST} + {WORKORDER.ACTSERVCOST};
{WORKORDER.ACTMATCOST}+{WORKORDER.ACTLABCOST}+ {WORKORDER.ACTTOOLCOST} + {WORKORDER.ACTSERVCOST}

In the report footer:

Whileprintingrecords;
Currencyvar Gtot;
Gtot
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 167 total points
ID: 35061464
I think he has a summary of that "field" or formula

If that is the case then simply create another summary of the formula and put it in the report footer or header.

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 166 total points
ID: 35067987
If you want to get a grand total of that formula, but only include the values once for each Location, you could use formulas like the ones that Peter posted, or use a running total on that formula, set it to evaluate "On change of group" (or field), and chose the Location group (or field).  Then that formula will only be added to the total once for each Location.

 James
0
 

Author Comment

by:washvt
ID: 35084382
Thank you Peter.  I used your formulas - however I'm still getting $0.00 in the footer and header?  Thank you.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35085703
Can you show the formulas you are usnig and where you placed them?

mlmcc
0
 

Author Comment

by:washvt
ID: 35086900
Formuals created - No I created formulas and placed them in the Header, Fooder, and Group - should I have used Selection Expert for each section and created the formulas there?

Group Header for LOCATION.LOCATION (this works fine - getting the total I'm looking for)

Currencyvar Gtot;
Gtot:=Gtot+ {WORKORDER.ACTMATCOST}+{WORKORDER.ACTLABCOST}+ {WORKORDER.ACTTOOLCOST} + {WORKORDER.ACTSERVCOST};
{WORKORDER.ACTMATCOST}+{WORKORDER.ACTLABCOST}+ {WORKORDER.ACTTOOLCOST} + {WORKORDER.ACTSERVCOST}

Header

Whileprintingrecords;
Currencyvar Gtot:=0;


Footer

Whileprintingrecords;
Currencyvar Gtot;
Gtot

Thanks.
0
 
LVL 34

Expert Comment

by:James0628
ID: 35087162
You should have Whileprintingrecords in the first formula too.  Without that, the formula may produce the correct total on the report, but not be updating the Gtot variable properly.

 When you said before that you were getting 0 in the footer and header, did you mean a group footer and header, or the report footer and header?  The formula in the report header will only produce 0, because it's creating the Gtot variable and setting it to 0.  If you don't want to see that 0 on the report, you can add "" at the end (as in the formula that Peter posted), so that the formula produces no visible output, or suppress that formula, or the entire report header section, if there's nothing else in that section that you need to see.

 If you want to actually see the final total in the report header, you can't do that this way.  The Gtot variable will be updated as the records are read, and it won't have a total until the last record has been read.

 James
0
 

Author Comment

by:washvt
ID: 35087272
Getting $0.00 in Report Footer and Header.  

I did add the "" at the end of the formula and am not seeing the $0.00.  

So - now thta I'm seeing my totals correctly - how do I get a grand total for the Location totals?

i've attached a sample.  Thank you for your assistance.

Location-Asset-Hiearchy-with-Cos.docx
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 100

Expert Comment

by:mlmcc
ID: 35087313
THe footer formula should show the total.

As stated above change this formula

Group Header for LOCATION.LOCATION (this works fine - getting the total I'm looking for)

Currencyvar Gtot;
Gtot:=Gtot+ {WORKORDER.ACTMATCOST}+{WORKORDER.ACTLABCOST}+ {WORKORDER.ACTTOOLCOST} + {WORKORDER.ACTSERVCOST};
{WORKORDER.ACTMATCOST}+{WORKORDER.ACTLABCOST}+ {WORKORDER.ACTTOOLCOST} + {WORKORDER.ACTSERVCOST}


to

Group Header for LOCATION.LOCATION (this works fine - getting the total I'm looking for)
WhilePrintingRecords;
Currencyvar Gtot;
Gtot:=Gtot+ {WORKORDER.ACTMATCOST}+{WORKORDER.ACTLABCOST}+ {WORKORDER.ACTTOOLCOST} + {WORKORDER.ACTSERVCOST};
{WORKORDER.ACTMATCOST}+{WORKORDER.ACTLABCOST}+ {WORKORDER.ACTTOOLCOST} + {WORKORDER.ACTSERVCOST}

mlmcc
0
 

Author Comment

by:washvt
ID: 35087780
Thanks so much - closer.  Now I have Report Footer is totaling the Loations on the second parge only - not both pages.  Where should the Footer reside to get the costs from each Location?

See Attached.  Thanks.


Locatioon---Asset-Hierarchy-Cost.pdf
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35089524
You would want it in the Page Footer however it will post the current total at that point not the overall total.

mlmcc
0
 

Author Comment

by:washvt
ID: 35100512
Thank you - I know have the formula in the Page Footer and I'm seeing the total per page.  Am I correct - there is no way to total both pages to one GRAND TOTAL?  Thanks again.
Locatioon---Asset-Hierarchy-Cost.pdf
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35103147
The one on each page should be the total to that point not just for the page.

Did you put the first declare in the page header rather than the report header?
Whileprintingrecords;
Currencyvar Gtot:=0;
""
mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 35115009
You're welcome.

 FWIW, I think maybe some of the points/credit should go to mlmcc.  If nothing else, I'm guessing that his last post helped you fix the grand totals.

 James
0
 

Author Comment

by:washvt
ID: 35141609
I would like to thank all for posting solutions.  Would like to open case - so I can give all credit for responses.

Thank you again.

James0628:
mlmcc:
peter57r:
0
 
LVL 34

Expert Comment

by:James0628
ID: 35146262
You're welcome again.  :-)

 James
0
 

Author Closing Comment

by:washvt
ID: 35334964
Thank you.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
selecting date modified field from a table 2 39
business objects to connect to MSSQL 6 62
setup wamp server for first time 2 44
ebay table structure 2 0
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now