First save your report with a different name--perhaps using "-sub" as an extension. In this "sub" report, insert a group header 2b section, assuming that {table.orderref} is your Grp #2. Then create a formula to be placed in GH 2b:
//{@dupe} - this is all one formula!:
whileprintingrecords;
shared numbervar array all;
shared numbervar k;
shared numbervar array dupe;
numbervar i;
if sum({table.amt},{table.Order Ref}) in all then (
k := k + 1;
if k < 1000 then (
redim preserve dupe[k];
if not(sum({table.amt},{table.Order Ref})in dupe) then
dupe[k] := sum({table.amt},{table.Order Ref})
)
)
;//to determine unique values in dupe array
if not(sum({table.amt},{table.Order Ref}) in all) then
(
i := i + 1;
if i < 1000 then (
redim preserve all;
all:= sum({table.amt},{table.Order Ref})
)
); //creates array of all unique sums
Next, save this subreport, and reopen the original report, and place the sub in a report header section. To make this sub invisible, suppress all sections WITHIN the subreport, and in the section expert, check "suppress blank section" for all sections that are visible. Then in the main report, choose format->subreport->borders and remove the borders around the subreport. Then select format->subreport->subreport tab and check "suppress blank subreport." In the section expert, choose report header->check "suppress blank section". You must not suppress the subreport object itself OR the report header section. Doing so would prevent the shared variables from passing to the main report.
Next, in the main report, create a formula:
//{@multdupes} to be placed in the GH2b (you can suppress this formula):
whileprintingrecords;
shared numbervar array dupe;
numbervar m;
numbervar array multdupes;
if sum({table.amt},{table.Order Ref})in dupe and
not(sum({table.amt},{table.Order Ref})in multdupes) then (
m := m + 1;
redim preserve multdupes[m];
multdupes[m] := sum({table.amt},{table.Order Ref})
);
m
Then you choose to do one of two things:
1) To show all duplicate values in one color (blue, here) select the group sum ->right click->format field->font->color-> x+2 and enter:
whileprintingrecords;
shared numbervar array dupe;
if sum({table.amt},{table.Order Ref}) in dupe then
color(0,0,225) else
crBlack
Or,to show different colors for each unique set of dupes, you can use this formula instead, but note that the formula must be manually set up for the maximum number of dupe sets expected. In this case, it is set up to accommodate five different unique values, repeating one or more times. I did work at trying to automate this so that it was not dependent on manual adjustments, and was semi-successful, but there will still kinks to work out. If I figure it out, I'll respond again.
2) whileprintingrecords;
numbervar array multdupes;
numbervar c;
if ubound(multdupes)>=1 and
sum({table.amt},{table.Order Ref})= multdupes[1] then
c := color(0,0,225) else
if ubound(multdupes)>=2 and
sum({table.amt},{table.Order Ref})= multdupes[2] then
c := color(0,225,225) else
if ubound(multdupes)>=3 and
sum({table.amt},{table.Order Ref})= multdupes[3] then
c := color(225,100,225) else
if ubound(multdupes)>=4 and
sum({table.amt},{table.Order Ref})= multdupes[4] then
c := color(100,225,0) else
if ubound(multdupes)>=5 and
sum({table.amt},{table.Order Ref})= multdupes[5] then
c := color(225,0,100) else
c := crBlack
;
c
-LB
