Highlight matching entries in a column 1

[idehen]

Hello, I have a report that has a grouping and the amount is a sum of Amount totals based on this group. What i will like to do is check within the same column if an entry already exist and highlight it in bold or colour.

Example:

I am grouping by example Stock Name:

Stockname: Amazon Shares[
Order Ref | Amount
123 | 240
345 | 345
678 | 2,898
789 | 745
978 | 240
998 | 120
999 | 63

What i will like to do is highlight 240 as this is showing twice. Note this is not a duplicate as the Refs are different. I just want to either colour or highlight this because this number already exist in the column as rows 1 and 5. This is a sum(Amounts) and are not displayed in the details section but rather at the group level.

Is there a way to do this?

Thanks

 

[lbass]

Would you want both 240's to be highlighted or only the second one? if there were two sets of duplicate sums, e.g, 745 was also repeated, would you want the 240's and the 745's to be the same color?

-LB

 

[idehen]

Thanks lbass, both duplicate sums should be highlighted e.g 240 both highlighted. And where another another sum duplicate is found, a different highlight e.g 745, if possible.

 

[lbass]

I Can't get to this until later today. Can you clarify the maximum number of group instances? Could there be more than 1000?

-LB

 

[idehen]

No, not more than 1000.

 

[lbass]

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

 

[lbass]

A correction:

The subreport should be placed in a Group Header 1 section, since you are checking for dupes within this group. You should also then add a reset formula in the report header of the subreport:

whileprintingrecords;
shared numbervar array all := 0;
shared numbervar k := 0;
shared numbervar array dupe := 0;
numbervar i := 0;

Note also that if you choose the second option, dupes beyond the five unique values (or whatever number of colors you set up for unique values) will just remain black, so if this is critical you must make sure there are sufficient colors to accommodate the maximum number of unique sums.

-LB

 

[lbass]

One more thing--you should link the subreport to the main report on the Group #1 field.

-LB

 

[idehen]

Hello lbass. Thanks so much for taking the time to test and post this process. I used option 1 for the colour highlight and worked well, however it seems that the first row of every sub group is always highlighted irrespective if it has a duplicate or not. Where duplicate exist, this worked but couldn't figure out why first row is always selected even if that is the only row in the sub group.

 

[lbass]

To troubleshoot this, I would have to know how you implemented this. Can you please confirm in what sections you placed each formula and where you put the subreport?

I tested this and didn't have this happen. Can I also assume you made no changes to my formulas except to substitute your actual field names?

-LB

 

[idehen]

Saved a Sub-report version of the main report and created a formula by copying and pasting your {@dupe} to mine and Substituting sum totals with mine.
>> insert the formula in the group header 2b which is {table.orderef}.

Created a new formula in the main report using what you provided //{@multiples} and again sub where required. This i placed in GH2b of the main and suppressed.
>>Then insert the sub report in the main report at GH1b which is the Stockname and joined both the sub and main reports by StockName.

And i copied the colour formula to the font as suggested:
whileprintingrecords;
shared numbervar array dupe;
if sum({table.amt},{table.Order Ref}) in dupe then
color(0,0,225) else
crBlack

 

[lbass]

Can you please clarify which group you mean by subgroup? Do you mean that the first Order Ref summary within each Stock Name group is highlighted?

-LB

 

[lbass]

Also, you put a reset formula in the report header of the subreport?

-LB

 

[idehen]

Just added the reset formula and works perfect!
Thanks very much lbass

 

[lbass]

If anyone is trying Option 2 above, please note that you would also need to add a reset formula in the main report in the Group #1 footer like this:

Whileprintingrecords;
Numbervar array multdupes := 0;
Numbervar n := 0;
Numbervar c := 0;
Numbervar m := 0;

Remember that this option is limited by the number of options you set in the color formula--which should be greater than or equal to the maximum expected number of duplicates within Group 1.

-LB

 

[idehen]

Thanks for your help. I will test option 2 also, no harm having more alternative solutions to a problem. Really appreciate it

 

[idehen]

Lbass - just wondering if i had the following

Stockname: Amazon Shares
Order Ref | Amount
123 | 240
345 | 345
678 | 2,898
789 | 745
978 | 240
998 | 120
999 | 63

Stockname: Amazon Shares B
Order Ref | Amount
222 | 745
456 | 888

How can i get the duplicate to look across the all sections for duplicate. E.g getting amounts 745 to be highlighted in colour as though they are not in the same group but still a duplicate.

The formula you provided works perfectly fine for the first example i gave but now am asked if this could be applied to any where in the report a duplicate appears irrespective if in the same group.

Thanks

 

[lbass]

I'm assuming you are using Option 1. Move the subreport to the report header and remove the reset formula. One problem that might arise is the limit of 1000 values, depending upon your dataset.

-LB

 

[idehen]

Yes, i am using option 1. I moved the sub-report to the report header and nothing changed. 745 is not highlighted, but 240 only is.

 

[lbass]

Remove the reset formula as suggested.

-LB