[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Crystal Reports XI: Changing Color by Row Within Crosstab

Posted on 2009-05-20
Medium Priority
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 101

Assisted Solution

mlmcc earned 400 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.

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  


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 1600 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

649 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