• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 37
  • Last Modified:

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!
0
Gabriell Porto
Asked:
Gabriell Porto
  • 6
  • 4
1 Solution
 
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
0
 
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
0
 
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

Many thanks!!!
0
 
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.
0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now