# How to summarize a formula field within a group except suppressed (dupplicate) values

Hello!

I am trying to summarize values within  a group, but only values that are not suppressed.

I am suppressing the values that are dupplicate in that group.

Can't figure what to do next.

Need help!
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Do you need the duplicate values?
If not try setting the DISTINCT VALUES option in the DATABASE menu

If you need the duplicates for some reason or the distinct values doesn't work then there is no way to use the SUMMARY functions and eliminate the duplicate values from consideration.

How are you suppressing them?

There is one way to calculate the total
It takes several formulas

Name - DeclareVariables
``````WhilePrintingRecords;
Global NumberVar GroupTotal :=0;
Global BooleanVar IsFirstInGroup := True;
''
``````

Name - ResetVariables
``````WhilePrintingRecords;
Global NumberVar GroupTotal;
Global BooleanVar IsFirstInGroup;
GroupTotal := 0;
IsFirstInGroup := True;
''
``````

In the detail section add a formula to calculate the total
``````WhilePrintingRecords;
Global NumberVar GroupTotal;
Global BooleanVar IsFirstInGroup;
If IsFirstInGroup OR {YourField} <> Previous({YourField}) then
(
GroupTotal := GroupTotal  + {YourField};
IsFirstInGroup := False;
);
''
``````

To display it add a formula to the group footer or the report footer
Name - DispTotal
``````WhilePrintingRecords;
Global NumberVar GroupTotal;
GroupTotal
``````

mlmcc

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
You could also use a running total.  I don't use them often because I prefer to have full control

Create a NEW Running Total
Set the EVALUATE option
ON CHANGE OF FIELD
Set the RESET option
ON CHANGE OF GROUP

mlmcc
Author Commented:
I'm gonna try to explain in a better way.

In the report there are several groups, one of them is called CLIENTS. I want to sum a particular field that is set to suppress if there are dupplicated values in the group.

I'm suppressing this way: Format Field > Common > Suppress if duplicated checkbox.

Whenever I generate the report, I want it to summarize every value of this particular field that it's not suppressed.
Commented:
Did you try either method?
There is no way to determine that the field is suppressed since you used that method.  By definition it is suppressed if it matches the previous value

mlmcc
Commented:
I just built a report and the formula method works.  The running total works except if the last value of a group matches the first value in the next group.

mlmcc
Author Commented:
I just didn't understand the "In the detail section add a formula to calculate the total" part.

How/Where do I input that?

Sorry, it's my first time messing around Crystal Reports.
Commented:
IN the field explorer there is a branch for FORMUAS
Right click it and click NEW
Put the name in and formula editor will open

Enter just as I have them written.  Only change the Calculate one to use your actual field.

mlmcc
Author Commented:
Yep, didn't understand the formulas yet, but that did the trick.

Many thanks!!!
Author Commented:
Hi, mlmcc!

Need some more help, if I may.

The formula worked, yet only for one field.

I have now 3 fields to do the same thing, but the codes only work for the first field I apply.
Commented:
The easy way to handle that is to replicate the formula for each field.  You will have to change the variable names.

For instance the   DeclareVariables formula could be

``````WhilePrintingRecords;
Global NumberVar GroupTotal1 :=0;
Global BooleanVar IsFirstInGroup1 := True;
Global NumberVar GroupTotal2 :=0;
Global BooleanVar IsFirstInGroup2 := True;
Global NumberVar GroupTotal3 :=0;
Global BooleanVar IsFirstInGroup3 := True;
''
``````

The others would be modified in the same way

mlmcc
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.