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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 35

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 35

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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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 35

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 35

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

730 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