Avatar of rowfei
rowfei asked on

Sum of Each Group Total

I created a total under each group footer, how can I sum each group total and place it into report header?

Thanks,
Crystal ReportsProgramming

Avatar of undefined
Last Comment
James0628

8/22/2022 - Mon
James0628

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 McCracken

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

mlmcc
ASKER
rowfei

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Mike McCracken

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
ASKER
rowfei

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 McCracken

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
rowfei

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 McCracken

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
ASKER CERTIFIED SOLUTION
James0628

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question