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.

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.


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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

914 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now