Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.
Course Of The Month
6 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Sum of a Sum Formula Field
Posted on 2011-03-07
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}+{WO
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.
FORMULA – @TotalCost
{WORKORDER.ACTMATCOST}+{WO
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
7
5
4- +1
18 Comments
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}+{WO
{WORKORDER.ACTMATCOST}+{WO
In the report footer:
Whileprintingrecords;
Currencyvar Gtot;
Gtot
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}+{WO
{WORKORDER.ACTMATCOST}+{WO
In the report footer:
Whileprintingrecords;
Currencyvar Gtot;
Gtot
Active 2 days ago
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
If that is the case then simply create another summary of the formula and put it in the report footer or header.
mlmcc
Active today
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
James
Thank you Peter. I used your formulas - however I'm still getting $0.00 in the footer and header? Thank you.
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}+{WO
{WORKORDER.ACTMATCOST}+{WO
Header
Whileprintingrecords;
Currencyvar Gtot:=0;
Footer
Whileprintingrecords;
Currencyvar Gtot;
Gtot
Thanks.
Group Header for LOCATION.LOCATION (this works fine - getting the total I'm looking for)
Currencyvar Gtot;
Gtot:=Gtot+ {WORKORDER.ACTMATCOST}+{WO
{WORKORDER.ACTMATCOST}+{WO
Header
Whileprintingrecords;
Currencyvar Gtot:=0;
Footer
Whileprintingrecords;
Currencyvar Gtot;
Gtot
Thanks.
Active today
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
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
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
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
Active 2 days ago
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}+{WO
{WORKORDER.ACTMATCOST}+{WO
to
Group Header for LOCATION.LOCATION (this works fine - getting the total I'm looking for)
WhilePrintingRecords;
Currencyvar Gtot;
Gtot:=Gtot+ {WORKORDER.ACTMATCOST}+{WO
{WORKORDER.ACTMATCOST}+{WO
mlmcc
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}+{WO
{WORKORDER.ACTMATCOST}+{WO
to
Group Header for LOCATION.LOCATION (this works fine - getting the total I'm looking for)
WhilePrintingRecords;
Currencyvar Gtot;
Gtot:=Gtot+ {WORKORDER.ACTMATCOST}+{WO
{WORKORDER.ACTMATCOST}+{WO
mlmcc
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
See Attached. Thanks.
Locatioon---Asset-Hierarchy-Cost.pdf
Active 2 days ago
You would want it in the Page Footer however it will post the current total at that point not the overall total.
mlmcc
mlmcc
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
Locatioon---Asset-Hierarchy-Cost.pdf
Active 2 days ago
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
Did you put the first declare in the page header rather than the report header?
Whileprintingrecords;
Currencyvar Gtot:=0;
""
mlmcc
Active today
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
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
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:
Thank you again.
James0628:
mlmcc:
peter57r:
Featured Post
Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs. This Article is intended to be a resource for such a task.
Preface
The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption. Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Suggested Courses
Course of the Month6 days, 6 hours left to enroll
707 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.