We help IT Professionals succeed at work.

Sum of Each Group Total

rowfei
rowfei asked
on
90 Views
Last Modified: 2016-02-03
I created a total under each group footer, how can I sum each group total and place it into report header?

Thanks,
Comment
Watch Question

CERTIFIED EXPERT

Commented:
What kind of total?  If it's just a regular summary, you can just right-click on the field, select Insert > Summary and create a grand total.  The total field will be created in the report footer, but you can just drag&drop it into the report header.

 James
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
If you need each group total in the report header you will have to use either a subreport or a cross tab.

mlmcc

Author

Commented:
Here is the sample of each group number:

Group 1:

Item A
Item B
Item A
Item C

Group 2:

Item B
Item B
Item C

I use distinct count for each group. Since some item can be on the different group, if I use distinct count for total item, I am getting the much less total count.

That's why I am looking for a solution to sum of each group distinct count to make sure I am getting the right total count.

Thanks
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
That information changes the way it is answered.

YOu will only be able to show the total in the report footer unless you use a subreport.

Basic idea.
Add a formula to the report header
Name - DeclareVariables
WhilePrintingRecords;
Global NumberVar TotalCount;
TotalCount := 0;
''

Open in new window


Add a formula to the group footer
Name - CalcTotal
WhilePrintingRecords;
Global NumberVar TotalCount;
TotalCount := TotalCount + DistinctCount({ItemField},{GroupField});
''

Open in new window


Add a formula to the report footer
Name - DisplayTotal
WhilePrintingRecords;
Global NumberVar TotalCount;
TotalCount 

Open in new window


mlmcc

Author

Commented:
Thanks, mlmcc.

I tried to add those formulas, the total shows on the report footer much less than the actual total. For testing purpose, I removed the  {GroupField}, the total is matched with distinct count of total items. Do you know where is wrong that I can't get the right total of each group?

TotalCount := TotalCount + DistinctCount({ItemField},{GroupField});

Thanks,
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
I assume you changed and used the actual fieldnames for the field and the group.

If you want the total for each group just insert a summary and use DistinctCount and put it in the group footer

mlmcc

Author

Commented:
I might put the wrong group field name. Which group field name should I select? Currently I put the record name that I selected for the group. But the total on the report footer is incorrect.

Thanks again for your help!!!
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
If you right click the Item in the detail section can you insert a summary?
If so put it in the group footer.

Do those counts look correct?

Can you upload the report?
No data needed

mlmcc
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.