Crystal 2008 - Conditionally shade cross-tab row

Do you want the entire row shaded if any cell in the row is E?
Can it be AM,E?  If so do you need it shaded?

How are the categories set?

mlmcc

Yes, I want to shade the entire row if any cell in the row is E.   Yes, it can be AM or E in the row.  The categories are data values coming from the database.

I think mlmcc was asking if the value in a single cell could be "E" combined with something else, like "AM,E".  In your example, the second column on the first line appears to be "A,CA".  So, could you have a similar value that includes "E" and, if so, do you need to shade the line because of that?

 Also, you specifically mentioned Mary/Mark Kole, but I'm guessing that you don't want to do this for just that one person.  Would it be for any line with an "E" in it?  Just making sure.

 James

Yes, I would like to shade for any line that has an E in it.  If a row has  'A,M,E',  I would like to shade the entire row.


By the way, we just upgraded to Crystal 2011.  Does is have any capabilities that would allow this?

Need the following output:

Columns     Category 1                        Category 2                  Cateogory 3
Rows A           A,M                                 E                                         (color entire row yellow)
         B           E                                      E                                   E   (color entire row yellow)


In Row A, even though there is no value in the cell, if the Row contains an E highlight the entire row.
Is there a a way to to do with Grid Value function.  I've enter this in the summary format field background formula if CurrentFieldValue = 'E' Then crYellow Else CrNoColor.  If colors only the cells that have an 'E' but I want to highlight the all the cells in that row.

I don't know of a way to do that.  There is no easy way to get at the other cells or to even know how many there are.

mlmcc

I've found that a cross-tab cell does show the bg color if there is no record for the row and column.  Is there a way to do this because the customer wants the entire row shaded?

James, I am not an advanced Cyrstal Reports developer, could  you give me a sample of what you are describing above?

I meant, that a cross-tab cell does NOT show the bg color if there is no record for the row and column.

It would probably simplify things greatly if you could post the report, preferably with some saved data, so that we can see how the cross-tab is set up, where it's located, the report structure, etc., and maybe test trying to change the bg color on those cells.  In the meantime ...

 What kind of "summary" are you using to produce the values in the cross-tab ("A", "A,M", "E", etc.)?  How do you produce those values?  If the lack of a bg color is because the value is an empty string, it might be possible to change how you produce those values so that you get a space, instead of an empty string.  Then you would hopefully see the bg color.

 As for how to use variables to set the color, like I said, it's going to depend on certain things, starting with where the cross-tab is located in the report.  Also, do you have just one level of grouping on the cross-tab rows?  For example, are the rows just grouped by something like a Name field, or do you have something like State and then Name, to get the people grouped by state?

 For a very simple, and general, example, let's assume that the cross-tab rows are grouped by a string field called Name.  Create a formula like the following (call it whatever you like) and put it in the report header.  If the cross-tab is in the report header, this formula needs to be in a section before the one that has the cross-tab.

Shared StringVar test;
test := "some name";
""

 Replace "some name" with the name from one of your cross-tab rows.  Ultimately, the formula would be picking the names that had the values that you were looking for, but for testing purposes, just pick a name.

 Then use a formula like the following to set the bg color in the cross-tab cells:

Shared StringVar test;
if GridRowColumnValue ("your row group") = test then
  crYellow
else
  crNoColor


 Replace "your row group" with the name of the field or formula that you used for the row grouping in the cross-tab, without the {}, and in quotes.  For example, if you used {table1.Name} for the rows, you would use GridRowColumnValue ("table1.Name")

 That should change the bg color for all of the data columns on that row (with values).  You can use the same format formula on any total columns, and the "label" column (with the row name).

 James

James,

Attached is my report.  

What I want is :
  if cert_level_code = 'E' or color_code = '1' then yellow
  else if cerl_level_code = 'S' or color_code = '2' then blue
  else nocolor

The 1 is used to have a value for any titles that the person does not have but one of their records on the row has an expired value.  

The 2 is used to have a value for any titles that the person does not have but one of their records on the row has a suspended value.

The problem is that I can't access the color_code value because it's not in my cross-tab.  I would like to evaluate this value but not show in summary cell.

Need assistance as soon as possible.
Report1.rpt

OK, you've added a couple of new things there, a second cert code ("S"), and color_code.

 Do you still want these tests to apply to a whole row?

 If so, could someone have both an "E" and an "S" code ("E" in one column and "S" in another)?

 If so, how do you handle that?


 Does each person have just one COLOR_CODE?

 If not, how do you handle that?


 Could someone have conflicting cert codes and color codes, like cert code "E" (yellow) and color code 2 (blue)?

 If so, how do you handle that?


 And there's still the basic problem with the cells that have no value (no records for that row-column).  I don't see any way to get the bg color to show in those cells, although I'm using CR 10 and you're using a later version, so you may have some options that I don't.

 FWIW, in your sample report, there are some cases where the bg color doesn't show because there is no value, and some cases where the bg color doesn't show because the value is "" (an empty string, with no spaces).  You could handle the empty string cases by creating a formula like this:

if {TEST_SKILL.CERT_LEVEL_CODE} = "" then
  " "  // a space
else
  {TEST_SKILL.CERT_LEVEL_CODE}

 and using the max for that formula in the cross-tab, instead of the max for the field.  Then you'll get a space instead of the empty string, and the bg color will be visible.

 But that won't help with the cases where there is no value at all (no records for a row-column).  The only way that I know of to handle those is to actually add records to the data so that every person gets at least one record for each column.

 Ignoring the new "S" cert code and COLOR_CODE for the moment, since they may complicate things, you can use my approach to change the bg color for the name column too.  So, you may not have the bg color set/visible for all of the columns, but if it was set for the name column, would that help?

 James

James,

I have flagged  all records for a personnel who has one or more records with an 'E'  with a '1' in a field called color_code.  For a pesonnel who has one or more records with 'S' with '2'.

 Would this work
if {TEST_SKILL.CERT_LEVEL_CODE} = "" then
       if color_code =  "1"  THEN " "  // a space
       else if color_code = "2" THEN "  " // 2 spaces  will crystal read this as 1 space
else
  {TEST_SKILL.CERT_LEVEL_CODE}


In the background formula evaluate on the currentfieldvalue

I'm not sure what you're trying to do, so I'll just make some comments.

 First of all, there's a problem with your formula.  Reformatting it a bit, you have:

if {TEST_SKILL.CERT_LEVEL_CODE} = "" then
  if color_code =  "1"  THEN
    " "  // a space
  else
    if color_code = "2" THEN
      "  " // 2 spaces  will crystal read this as 1 space
    else
      {TEST_SKILL.CERT_LEVEL_CODE}

 You need another "else".  If CERT_LEVEL_CODE is not "", you would (ironically) output an empty string.  You need to add an "else" for either 'if color_code = "2" THEN', or 'if {TEST_SKILL.CERT_LEVEL_CODE} = "" then'.


 The point of the space in my formula was so that the field in the cross-tab was not an empty string, so the bg color would show.  It had nothing to do with selecting the bg color.  Using two spaces instead of one wouldn't make any difference in that context.

 You could check CurrentFieldValue in the cross-tab to see if it's one space or two, but that's only going to apply to the current cell.  So, if someone has a column where they had a record, but the cert level was the empty string, you might have one or two spaces in that column, depending on color_code, and set the color in that column accordingly.  That's not going to affect any other columns.

 James

I finally got the report to shade the columns that have no value but the method is kludgy.  I was hoping for more of a cleaner method.
In my data source, I add a row of data  with a 1 in the column (color_code) for the personnel who do not have one of the selected titles but does have a record that expires. When this case occurs the CERT_LEVEL_CODE column is set to ' '.     I use the same method for suspend by inserting a 2 for the color_code column.  

Created a summary formula  
IF {TABLE.CERT_LEVEL_CODE} <> " " THEN
       {TABLE.CERT_LEVEL_CODE}
ELSE  
    {TABLE.COLOR_CODE}

In the summary format field border background formula

If currentfieldvalue = 'E' or currentfieldvalue = '1' then
    crYellow
else if currentfieldvalue = 'S' or currentfieldvalue = '2' then
    crRed
else crNoColor

The only problem is that the 2 or 1 appears in the summary column.

I had to create a formula for the font color to  change the 1 and 2 to the shade of the background color to hide the number:

if currentfieldvalue = "1" then crYellow
else
 if currentfieldvalue = "2" then  crRed
else crblack

Instead of changing the font color to match the bg, couldn't you just use a similar formula to suppress the field?  Maybe not.  Sometimes the "obvious" doesn't work in a cross-tab.  But if you can change the font color, I think you should be able to suppress it instead.

 James

When I suppress the field, that's exactly what it does.  It shows no color for that cell.

Honestly, I'm not sure what you're saying there.  It just seemed like if you could change the font color to match the bg color, you should be able to suppress the field instead, and suppressing seems a bit simpler (you don't have to pick the right color).  It could also be easier to maintain down the line.  Using a formula to suppress the field seems more obvious than using a formula to change the font color to match the bg color.  I could see someone looking at that later and having some trouble figuring out why some cells don't show.

 James

James,

When I suppress the field wherever there is a 2 or 3 which is in a cell whose background color is red or yellow; the cell background color disappears.

Ah, OK.  Setting the font color to match the bg color allows you to keep the bg color.  Thanks for taking the time to explain why you were doing that, instead of just suppressing.

 James