multi colored row highlights 1

[MSBrady]

Oy!

CR 10
SQL Server 2005

I have a simple report with one mutli value parameter. I want to highlight the Details section with a different color for each parameter value. The actual colors don't matter, just that each value has it's own color.

thanks

 

[kskid]

You don't have enough information.

If you are comapring a table field to the parameter value(s) i.e. {table.field} in {?parameter}

then in the section expert, click on the x-2 and enter the following formula.

select {table.field}

case A:
crRed
case B:
cryellow
case C:
crsilver
def:
crnocolor


-lw

 

[kskid]

Ooops

show be default, not def

 

[MSBrady]

I am comparing the TABLE field to the parameter in the selection criteria.
The code I have for coloring right now is this:

IF {xCustomNoteWatchListValue.Name} = "Taxes"
THEN crBlue
ELSE IF {xCustomNoteWatchListValue.Name} = "abc"
THEN crYellow
ELSE crGreen

{xCustomNoteWatchListValue.Name} is on Details section along with other fields.

But I can't specify every possible value b/c it changes and grows. So regardless of what they enter I need a different row color for each value. I hope that clarifies it.

 

[lbass]

Do you have some concept of the maximum number of values the parameter might have?

-LB

 

[MSBrady]

If there were ultimately 20 values I would be surprised. There are basically 14 possible CR colors as I see it. I can't use black or white. I have been trying to figure this out, but I need help.

whileprintingrecords;

numbervar x;
numbervar i:
stringvar array xColor := MakeArray ("crTeal", "crOlive", "crGreen", 
"crPurple", "crRed", "crBlue", "crNavy", "crYellow", "crFuschia", "crGray", 
"crSilver")

FOR x := 1 to i
DO (
stringvar str;
str := xColor[x]);
str

It keeps saying that the result has to be numeric.

This is using the color constants. I have no clue how to do a RGB solution. Maybe 3 variables that would increment in 10 point intervals...I don't know.

 

[lbass]

Try:

whileprintingrecords;
numbervar x := x + 1;
numbervar array xColor := [crTeal, crOlive, crGreen, crPurple, crRed, crBlue, crNavy, crYellow, crFuchsia, crGray, crSilver];
numbervar i := ubound(xColor);
numbervar nox;


if x < i then(
redim preserve xColor[i];
nox := xColor[x]
);
nox

-LB

 

[MSBrady]

That is getting closer. I've probably not explained it well enough.
I need it to take into consideration the {xCustomNoteWatchListValue.Name} values. Right now it colors each of the rows returned with a different color until it runs out of colors to use, then it colors all remaining rows with the last color used. I need it to color the rows depending on the values of {xCustomNoteWatchListValue.Name}.
So if they enter 'abc', 'def', 'Taxes', and 'Cold as Ice' then on the report I need each row that has an 'abc' value to be colored the same color regardless of where in the output it falls.
Example:

Desired output

InvoiceNum Name Date Note
123456 abc 11/07/2007 this is a note (Teal)
789012 def 10/13/2007 nother note (Olive)
345678 Taxes 09/23/2007 yet a nother note (Green)
901234 abc 04/06/2006 note note note (Teal)

Current output

InvoiceNum Name Date Note
123456 abc 11/07/2007 this is a note (Teal)
789012 def 10/13/2007 nother note (Olive)
345678 Taxes 09/23/2007 yet a nother note (Green)
901234 abc 04/06/2006 note note note (Purple)

 

[lbass]

Then you just need to use the conventional method:

select {table.name}
case "abc": crTeal
case "def" : crOlive
case "Taxes" : crGreen //etc.
default : crNoColor

-LB

 

[MSBrady]

But there isn't a hard coded list for the possible cases. The user manages the values list. I was using 'abc', 'def', etc as examples. I won't know what they decide to put in once this goes live. So somehow it needs to 'assign' a number (I thought using array elements) and then whenever it encounters that number/element then color the row a color from the xColor array.

 

[lbass]

Okay, this seems to work:

whileprintingrecords;
numbervar i;
numbervar j := ubound({?Parm});
numbervar array z;
numbervar k;

for i := 1 to j do(
if {table.field} = {?Parm} then(
redim preserve z[j+1];
z[1] := i;
for k := 1 to j do(
select z[1]
case 1 : crRed
case 2: crMaroon
case 3: crGreen
case 4: crOlive
case 5: crNavy
case 6: crPurple
case 7 : crTeal
case 8: crSilver
case 9: crBlack
case 10: crLime
case 11: crYellow
case 12: crBlue
case 13: crFuchsia
case 14: crAqua
case 15: crWhite
default : crNoColor
))
); z[1];

-LB

 

[lbass]

Oops, that should have been:

whileprintingrecords;
numbervar i;
numbervar j := ubound({?Parm});
numbervar array z;
numbervar k;
numbervar m;

for i := 1 to j do(
if {table.field} = {?Parm} then(
redim preserve z[j+1];
z[1] := i;
for k := 1 to j do(
m := (
select z[1]
case 1 : crRed
case 2: crMaroon
case 3: crGreen
case 4: crOlive
case 5: crNavy
case 6: crPurple
case 7 : crTeal
case 8: crSilver
case 9: crBlack
case 10: crLime
case 11: crYellow
case 12: crBlue
case 13: crFuchsia
case 14: crAqua
case 15: crWhite
default : crNoColor
))
)
); m

-LB

 

[MSBrady]

You are the bomb! That worked a treat.

In another report I'm using this same report as a sub; only there isn't an input parameter. The {xCustomNoteWatchListValue.Name} values are still the same and come from the same location. I need to do the same thing here - less the parm.

Stay cool.
Cheers.

p.s.
star

 

[lbass]

Are you linking on the field? How is the field limited in the main report? Are you trying to match a color in the main report? I'm not sure what you want to do here.

-LB

 

[MSBrady]

The sub is linked via a RefNbr field that isn't relevant to this solution. There is no matching between the main and the sub as far as color goes. The main doesn't have any WatchListValues. The main is an Invoice report and the sub is an almost identical version of what you just helped me with only it isn't dependent on a parameter. Whereas the first report you helped with pulls Invoices specific to the WatchListValue parm, this report pulls all Invoices and the sub describes the WatchListValues (if any) on a given Invoice.

 

[lbass]

If you want each sub to use the same color scheme per field, and if the values of the field are unknown ahead of time, then insert an unlinked sub in the report header that has the same record selection criteria as the main report. Insert a group on the field you want to base the coloring on (we'll call it {table.field}).

Then create a formula and place it in the group header of this sub:

whileprintingrecords;
shared stringvar x := x + {table.field}+", ";

In the report footer of the RH sub, add this formula:

whileprintingrecords;
shared stringvar x;
shared stringvar array y := split(x,", ");
shared numbervar i;
shared numbervar j := ubound;
shared numbervar array k;

for i := 1 to j do(
redim preserve k[j];
k := (
select i
case 1 : crYellow
case 2 : crRed
case 3 : crBlue
case 4 : crTeal
case 5 : crAqua
case 6 : crGreen //add "j" number of cases
default : crnoColor
)));

Suppress all sections within the subreport.

Then in the other sub (in the group section?), go to the section expert->details->color tab->background->x+2 and enter the following formula:

whileprintingrecords;
shared numbervar array k;
shared numbervar i;
shared numbervar j;
shared stringvar array y;
numbervar m;

for i := 1 to j do(
if {table.field} = y then
m := k
);
m

-LB

 

[MSBrady]

Okay they have now changed this a little; simplified it I guess. There is now a TABLE that contains the possible colors they want associated with a given lable. These possible values are limited to Crystal possible color values. So when they are using the GUI to update the WatchListValues they are at the same time assigning a color; Taxes = Blue, Fees = Red, for a given Invoice. I can link to this TABLE, but I don't know how to tell Crystal to color the row the color that the they are assigning. I have tried adding to the Background Color formula editor:

whileprintingrecords;
numbervar i;
select i
case 1 : "cr" & {xColorOption.Name};
i

and

iwhileprintingrecords;
numbervar i;
select i
case 1 : {@color};
i

where {@color} = "cr" + {xColorOption.Name}

Both times I get Black. When I place {@color} on the report it prints crBlue, crRed, etc.

 

[lbass]

What happens if you just add:

{xColorOption.Name}

...in the background color formula area?

-LB

 

[MSBrady]

Error
"Formula result must be a number.

 

[lbass]

The problem is that the colors in CR are actually numbers, not strings. I think you would have to create a crosswalk formula like this:

select {table.color}
case "crRed" : crRed
case "crBlue" : crBlue //etc.
default : crNoColor

You could place this formula in the background color formula area.

-LB