Solved

Crystal 2008 -  Conditionally shade cross-tab row

Posted on 2012-03-30
20
455 Views
Last Modified: 2012-06-08
Category1        Category2          Category3     ..... more columns

John Doe       AM              A,CA                      SA
Mary Kole      E             E             E                ...... more columns

I would like to shade the entire summary cells for Mark Kole yellow.  If not E, do not shade. AM, TA,CA SA, E are the the summary field values in this example.
If value is E,
0
Comment
Question by:cookiejar
  • 10
  • 8
  • 2
20 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 37789145
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
0
 

Author Comment

by:cookiejar
ID: 37789231
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.
0
 
LVL 34

Expert Comment

by:James0628
ID: 37790058
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
0
 

Author Comment

by:cookiejar
ID: 37839637
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?
0
 

Author Comment

by:cookiejar
ID: 37840224
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37840865
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
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 37841293
If a cell is empty (there are no records at all for that row and column), then I don't know if the background color will even show up, so that may be a separate problem.  Ignoring that issue for the moment ...

 I've come up with something that might work.  The basic idea seems to work.  You'll have to decide if it's practical for your report; and whether or not it's just too much trouble.

 The basic idea is to use variables to save a "switch" for each row, saying whether or not there was an "E" in that row.  If your cross-tab is in the report footer, you may be able to set the variables in the body of the report.  Otherwise, you may have to use a subreport to set the variables, which means reading your data a second time.  If you have a lot of data, that may simply take too long.  For my test, I just set a variable to a fixed value in the report header.  I haven't tried the subreport approach.  In theory it would work, but it may depend on exactly how CR builds the report (when it runs the subreport, vs when it builds the cross-tab).

  Let's say that your cross-tab rows are grouped on Name.  You would have two arrays.  The first array would have one entry for each Name (row).  The second array would contain a "switch" (eg. 1 or 0) for each Name, saying whether or not any records for that Name contained "E".  Those arrays would be filled at some point before the cross-tab is produced.  Then in the cross-tab, you would use GridRowColumnValue to find the Name in that row in the first array, and then check the corresponding element in the second array to see if there were any "E" records, and set the bg color accordingly.

 I tried this and it did work for me, although I just did a very simplified version.  I just set a simple shared variable (not an array) to a specific value in the report header, and checked that variable in the format formula, and the cells in that row in the cross-tab had a yellow bg.  Since the simple shared variable worked, I can't think of any reason that shared arrays wouldn't work.  The real question is when/how you would set the variables in your report.  And there's still the question of whether a cross-tab cell with no value (because there were no records for that row and column) would even show a bg color.

 James
0
 

Author Comment

by:cookiejar
ID: 37843250
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?
0
 

Author Comment

by:cookiejar
ID: 37843568
I meant, that a cross-tab cell does NOT show the bg color if there is no record for the row and column.
0
 
LVL 34

Expert Comment

by:James0628
ID: 37845708
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
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:cookiejar
ID: 37867713
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
0
 
LVL 34

Expert Comment

by:James0628
ID: 37869345
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
0
 

Author Comment

by:cookiejar
ID: 37872232
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
0
 
LVL 34

Expert Comment

by:James0628
ID: 37874846
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
0
 

Author Comment

by:cookiejar
ID: 37926304
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
0
 
LVL 34

Expert Comment

by:James0628
ID: 37927780
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
0
 

Author Comment

by:cookiejar
ID: 37939507
When I suppress the field, that's exactly what it does.  It shows no color for that cell.
0
 
LVL 34

Expert Comment

by:James0628
ID: 37940218
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
0
 

Author Comment

by:cookiejar
ID: 37942425
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.
0
 
LVL 34

Expert Comment

by:James0628
ID: 37943112
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

706 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

20 Experts available now in Live!

Get 1:1 Help Now