Solved

Crystal Reports XI: Changing Color by Row Within Crosstab

Posted on 2009-05-20
7
2,122 Views
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!

CrystalReportSample.png
0
Comment
Question by:Donovan Moore
  • 4
  • 2
7 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 100 total points
Comment Utility
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

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
  crRed
else
  DefaultAttribute


 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" :
  crRed
 Case "Yellow" :
  crYellow
 Case "Black" :
  crBlack
 Case "White" :
  crWhite
 Case "Orange (Logos Only)" :
  Color (255, 150, 100)
 Default :
  DefaultAttribute


 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.

 James
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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.

 James
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Donovan Moore
Comment Utility
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).
0
 
LVL 34

Accepted Solution

by:
James0628 earned 400 total points
Comment Utility
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.

 James
0
 

Author Closing Comment

by:Donovan Moore
Comment Utility
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)!
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
I appreciate that.  Sorry I couldn't find a real solution.

 James
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

772 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

9 Experts available now in Live!

Get 1:1 Help Now