Crystal Reports XI: Changing Color by Row Within Crosstab

Posted on 2009-05-20
Last Modified: 2012-05-07
I have a crosstab report (Crystal Reports XI) that lists color information.  I would like the "Row #1 Name" cell to be formatted to display a certain color - pertinent to the information in the row.

I have attempted to set the Background of this cell to use the following code:

WhilePrintingRecords ;
Color ({myField.Red}, {myField.Green}, {myField.Blue})

However, this changes all of the cells in each row to the color of the first row.

I have attached an image (PNG) of what this report currently looks like.  You will see that the first row/cell is the correct color, but the remaining cells are all set to the same color.

Any and all help would be greatly appreciated!

Question by:Donovan Moore
  • 4
  • 2
LVL 100

Assisted Solution

mlmcc earned 100 total points
ID: 24432506
In a cross tab you dont have access except to the values of the cell you are in.  Other wise you have the record the report is on which is probably the first record or last record.

I believe this is doable in CR2008

LVL 34

Expert Comment

ID: 24438278
As mlmcc said, you may not be able to use values from a record like that to format a cross-tab cell.

 Can you use the row label - "Red", "Yellow", "Black", "White" and "Orange (Logos Only)" - to pick the color?  If so, then I think you can do that instead of trying to use fields from a record.

 A simple example:

if CurrentFieldValue = "Red" then

 CurrentFieldValue is a built-in CR function that returns the current field value (Duh :-).  Since you're formatting the row label based on the value in that field, you can use CurrentFieldValue.  FWIW, I also tried using the formula that my cross-tab row was based on and that did _not_ work.  But CurrentFieldValue did.

 To incorporate the various colors, I'd use Select-Case (instead of if's).  Something like:

Select CurrentFieldValue
 Case "Red" :
 Case "Yellow" :
 Case "Black" :
 Case "White" :
 Case "Orange (Logos Only)" :
  Color (255, 150, 100)
 Default :

 There is no crOrange, so I had to use Color () there.  You could use Color () for the other colors as well, to tweak the colors or just for consistency.  I just used crRed, etc. for simplicity.

 Of course if you're changing the bg color then you may also need to change the font color in some cases, so you don't end up with, for example, a white font on a white bg.  You can use the same kind of logic to change the font color.

LVL 34

Expert Comment

ID: 24438295
Oh yeah.  At the end of the Select-Case, for the default value, I just used DefaultAttribute, which, in my case, was black for the font and white for the bg.  You can always replace that with some specific color if you like.

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.


Author Comment

by:Donovan Moore
ID: 24441120
The problem is that I am trying to set the color based on the RGB values (taken from the "Solid Colors" record) within the row.  I have only those RGB values included in the underlying query.  So in my example, the Red would be the (s)RGB values taken from PANTONE 187 C, Yellow would be from PMS 137 C, and so on.

I had already taken care of the font color issue by doing a formula based on the luminescence (taken from the corresponding CIE-L*ab values).
LVL 34

Accepted Solution

James0628 earned 400 total points
ID: 24447297
The only thing that I've been able to come up with is keying off of the value in the cell (eg. "Red", "Black", etc. in the row labels).  If those labels are too generic (which I'm guessing is why you don't want to base colors on them), then you could change the bg color in the "Solid Colors" column based on the values in those cells.  For example, if it's "PANTONE 187 C", you use crRed or Color (255, 0, 0) or whatever.  I don't know of a way to get the values from the record, but if those "Solid Colors" names have fixed color values, and there aren't too many of them, you could put them in a formula.

 I was thinking that if every record in a row had the same RGB values, or the "Solid Colors" column was the only one that had values, then you _might_ be able to use something like Minimum or Maximum to get a value for the group, but, based on my experiments, I don't really see a way to get that either.  I guess it might be possible, depending on your data, but I rather doubt it.  And if different columns have different values and you need the values from the record(s) associated with one specific column in the current row, then I definitely don't see a way to get that.


Author Closing Comment

by:Donovan Moore
ID: 31583518
I'm splitting this one - 400 points going to James0628 for his research and effort in trying to make this work, and 100 to mlmcc for correctly stating that this can't be done (in version XI)!
LVL 34

Expert Comment

ID: 24489106
I appreciate that.  Sorry I couldn't find a real solution.


Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question