how can I suppress duplicate field amount in Crystal Report?

[santosh1]

In Crystal Report, how can I suppress duplicate field?
I tried to suppress the duplicate field by checking the 'Suppress duplicated field' box
in the format editor, it didn't work. How can I can fix this problem?
Thanks in advance. Below is an example (I suppressed the employee id, date, amount).
The total should be $30 since employee id '098232' is a duplicate field.

Medtronic
Employee ID Date Amount

098232 10/2/2003 $10 *Note: duplicate field
967789 10/8 $20

Abbott Laboratories

Employee ID Date Amount

098232 10/2/2003 $10
680001 9/3/2003 $10
_____________________________________________
Total $40

 

[Ngolem]

I suppose one company is a very close subsiduary of the other company is it.

"suppress if Duplicate" only works if the fields are in succession which in this case they are not.

Question: Does it matter which Group the employee is listed??

I suppose not

My approach would be to track the employees as their info is written and doing a manual (not summary total) on the $ involved

How many distinct employees are we taking here...more than 1000? If so we can use a single array to keep track of employee ID

Others may do it differently but this is my approach

Place the following in the Report Header suppressed

//@initialize (suppressed in Report header)

WhilePrintingRecords;
//estimate number of employees total and initialize for 50%
// more...I will initialize for a potential 10 employees here
StringVar Array employeeID := [ "","","","","","","","","","" ];
NumberVar pointer := 0;
numberVar total := 0;

Now in the details for the "Amount" field put this formula

//@CalcTotal

whilePrintingRecords;
StringVar Array employeeID;
NumberVar pointer ;
numberVar total ;
BooleanVar flag := True;

if not {Table.EmployeeID} in employeeID then
(
pointer := pointer + 1;
if pointer <= 10 then // I only initialized for 10 employees
(
employeeID[pointer] := {Table.EmployeeID} ;
total := total + {Table.amount};
flag := False;
);
);
{Table.amount};

now in the detail section place the following in the &quot;Conditional suppress&quot;

EvaluateAfter({@CalcTotal});
BooleanVar flag;
flag;

I think this should work....if there are more than 1000 employees then more arrays will be necessary

Jim Broadbent

 

[lbass]

Here's another approach. Create a formula {@dupe} for the details section:

whileprintingrecords;
stringvar id;
numbervar amt := 0;

if instr(id, totext({table.emplID},0,&quot;&quot) = 0 then
id := id + totext({table.emplID},0,&quot;&quot + &quot; &quot; else id := id;
if onfirstrecord then amt := {table.amount} else
if not onfirstrecord and
trim(mid(id, instrrev(trim(id),&quot; &quot)) = totext({table.emplID},0,&quot;&quot then
amt := {table.amount} else amt := 0;
amt;

Then go to format section->details->suppress->x+2 and enter:

{@dupe} = 0

This will suppress all records except the first for each employee. If your definition of duplicate is different, ie., if an employee can legitimately appear more than once, then you need to tell us what constitutes a duplicate.

To get the grand total, create a formula {@sumdupe} for the details section:

whileprintingrecords;
numbervar sumdupe := sumdupe + {@dupe};

And then create a display formula for the report footer:

whileprintingrecords;
numbervar sumdupe;

You didn't say what version you are running, but if less than 9.0, you might run into the 254-character limit using this solution.

-LB

 

[santosh1]

I think the program cannot find duplicates on other groups.
Is there a method that you can check duplicates b/w various groups? Here is my formula to check duplicates within one group. is it possible to check duplicates b/w various groups? Thanks.

WhilePrintingRecords;

CurrencyVar RunningTotal;
If not (not OnFirstRecord and {Employee_Report.empID}= previous ({Employee_Report.empID})) then
RunningTotal := RunningTotal + {Employee_Report.Amount}

 

[santosh1]

lbass,

I am using CR version less than 9.0, got err msg like u said.

 

[lbass]

I initially assumed this wasn't possible, but is there any chance you could just change the report format? If you removed the group on company and instead added the company field to the details section, you could group or sort on employeeID, and then suppression could be based on previous records, etc.

If you can't change your report format, then please tell us how many employees could be in the report. There is a way to increase the number of characters, using complex formulas and combining them in text boxes, but this probably doesn't make sense if you have a large number of employees.

Have you tried Jim's approach? I can't say whether it works (it's beyond me), but it appears that his approach might not be limited in the same way as my suggestion.

-LB

 

[lbass]

Here's a solution that should work for up to 108 employees (up to 762 characters, with 7 digits and a space for each employee ID). This is an adaptation of a solution by Naith for segmenting string length so that a combination of fields each no greater than the 254 limit can together exceed that limit.

You should discard my earlier formulas so that they don't confound the following ones. Create the following formulas:

{@dupe} to be placed in the details section:
whileprintingrecords;
stringvar id1;
stringvar id2;
stringvar id3;

if instr(trim(id3),totext({table.emplID},0,&quot;&quot) = 0 then
if instr(trim(id2),totext({table.emplID},0,&quot;&quot) = 0 then
if trim(id2) = &quot;&quot; then
if length(id3) + length(totext({table.emplID},0,&quot;&quot+&quot; &quot > 254
then id2 := id2 + totext({table.emplID},0,&quot;&quot + &quot; &quot; else
id3 := id3 + totext({table.emplID},0,&quot;&quot + &quot; &quot;
else
if instr(trim(id1),totext({table.emplID},0,&quot;&quot) = 0 then
if trim(id1) = &quot;&quot; then
if length(id2) + length(totext({table.emplID},0,&quot;&quot+&quot; &quot > 254
then id1 := id1 + totext({table.emplID},0,&quot;&quot + &quot; &quot; else
id2 := id2 + totext({table.emplID},0,&quot;&quot + &quot; &quot;
else
if length(id1) + length(totext({table.emplID},0,&quot;&quot+&quot; &quot > 254
then id1 := id1
else id1 := id1 + totext({table.emplID},0,&quot;&quot + &quot; &quot;
else id1 := id1
else id2 := id2
else id3 := id3;

{@amt} to be placed in the details section:
whileprintingrecords;
numbervar amt ;

if onfirstrecord then amt := {table.amount} else

if not onfirstrecord and
((len(trim({@id3})) = 7 and
{@id3} = totext({table.emplID},0,&quot;&quot) or
(len(trim({@id2})) = 7 and
{@id2} = totext({table.emplID},0,&quot;&quot) or
(len(trim({@id1})) = 7 and
{@id1} = totext({table.emplID},0,&quot;&quot))
then amt := 0 else

if not onfirstrecord and
(len(trim({@id3})) > 7 and
instr(trim({@id3}),totext({table.emplID},0,&quot;&quot) > 0) or
(len(trim({@id2})) > 7 and
instr(trim({@id2}),totext({table.emplID},0,&quot;&quot) > 0) or
(len(trim({@id1})) > 7 and
instr(trim({@id1}),totext({table.emplID},0,&quot;&quot) > 0) then
amt := 0 else amt := {table.amount};

{@id1}--this does not have to be placed on the report canvas:
whileprintingrecords;
stringvar id1;

{@id2}--this does not have to be placed on the report canvas:
whileprintingrecords;
stringvar id2;

{@id3}--this does not have to be placed on the report canvas:
whileprintingrecords;
stringvar id3;

{@sumamt) to be placed in the details section:
whileprintingrecords;
numbervar sumamt := sumamt + {@amt};

{@displaysumamt}--this is the grand total to be placed in the report footer:
whileprintingrecords;
numbervar sumamt;

The logic of the formulas could be extended for more employees by added new id segments (id4, id5, id6, etc.). Note that formulas ({@id1},{@id2},{@id3}) are used in {@amt} in order to carry the variable amounts forward, since {@dupe} will only carry forward values that fall within the 254-character segment that is current at the time.

-LB