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!
Gabriell PortoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

mlmccCommented:
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

In the report header add a formula
Name - DeclareVariables
WhilePrintingRecords;
Global NumberVar GroupTotal :=0;
Global BooleanVar IsFirstInGroup := True;
''

Open in new window


In the group header add a formula to reset the total
Name - ResetVariables
WhilePrintingRecords;
Global NumberVar GroupTotal;
Global BooleanVar IsFirstInGroup;
GroupTotal := 0;
IsFirstInGroup := True;
''

Open in new window


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;
);
''

Open in new window


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

Open in new window


mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
mlmccCommented:
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
Select your field to summarize
Set the EVALUATE option
     ON CHANGE OF FIELD
Set the RESET option
     ON CHANGE OF GROUP

mlmcc
Gabriell PortoAuthor 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.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

mlmccCommented:
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
mlmccCommented:
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
Gabriell PortoAuthor 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.
mlmccCommented:
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
Gabriell PortoAuthor Commented:
Yep, didn't understand the formulas yet, but that did the trick.

Many thanks!!!
Gabriell PortoAuthor 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.
mlmccCommented:
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;
''

Open in new window


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.