Technical Questions: Highlighting cells in Crosstabs

Print Page | Close Window

Highlighting cells in Crosstabs

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11213
Printed Date: 05 May 2024 at 12:48am

Topic: Highlighting cells in Crosstabs

Posted By: rvink
Subject: Highlighting cells in Crosstabs
Date Posted: 26 Sep 2010 at 1:25pm

I have a crosstab which lists lecturer teaching hours by week. Each lecturer has a maximum no of hours they can teach (Max), in some cases it is not entered. I want to highlight cells where the weekly hours exceeds the specified limit. Ideally the table should look something like this:

LectName | Max | w1 | w2 | w3 |
---------------------------------
Lecturer1 | | 12 | 16 | 8 |

Lecturer2 | 12.0 | 12 | 14 | 16 |

Lecturer3 | 14.0 | 14 | 16 | 14 |
---------------------------------

I'm using Crystal Reports 2008. I looked at Calculated Members and Embedded Summaries but I'm not familiar with these and have had no success so far. If it makes any difference, the Max column could be left out as it is displayed elsewhere on the report.

Replies:

Posted By: rvink
Date Posted: 27 Sep 2010 at 10:57am

Anyone?

Posted By: DBlank
Date Posted: 27 Sep 2010 at 11:23am

What is the definition of exceeding hours?

You can right click on any cell in the CT

select Format Field

select Font Tab

click on the fomrula field and use a formual

if currentfieldvalue>13 then crRed else Crblack

this will apply the rule to all field across that row

Posted By: rvink
Date Posted: 27 Sep 2010 at 12:10pm

Perhaps I did not explain clearly enough. I can't use a global formula
if currentfieldvalue>13 then crRed else Crblack
because the the hour limit is different for each lecturer, as given in the "Max" column. For some lecturers the max is 16 hours, others are 10 or some other value, and in many cases it is not specified (NULL).

Posted By: DBlank
Date Posted: 28 Sep 2010 at 4:12am

there may be a more elegant solution but you can try:

if gridrowcolumnvalue("table.lectname") ='Lecturer2' and currentfieldvalue>12 then crRed else

if gridrowcolumnvalue("table.lectname") ='Lecturer3' and currentfieldvalue>14 then crRed else crblack

Posted By: rvink
Date Posted: 28 Sep 2010 at 10:55am

Thanks again DBlank, but no good. That solution is effectively hard coding the max hours of every lecturer into the report. There are 1343 lecturers this year, with lecturers coming and going, and new figures being entered regularly. The hours are completely updated each year.

The closest solution I have so far is to add another summary to my crosstab Max(MaxWeekHours) so each cell has two totals:
- Sum(Hours)
- Max(MaxWeekHours)
I can then format the first summary by comparing the second summary:

if CurrentFieldValue > GridValueAt(currentRowIndex, CurrentColumnIndex,1) then crRed
else crGreen

I can suppress the second summary so it does not appear but it still leaves a lot of empty cells in my report, which I don't want.

I also tried putting the MaxWeekHours as a row header, below the Lecturer Name, but I can't find any way to reference the row headers using the functions given, only the grid values.

Posted By: DBlank
Date Posted: 28 Sep 2010 at 10:59am

I was afraid there might be too many values.

I have seen some more advanced coding around CT values in Brian's book but I rarely use CTs so have not made time to learn it yet so I am out of ideas.

maybe someone else will chime in here...

Posted By: rvink
Date Posted: 28 Sep 2010 at 4:06pm

Cracked it!

In the background formatting formula for the cell, I put the following code

if CurrentFieldValue > GridLabelAt("Lecturer.MaxWeekHours", CurrentRowIndex) then
Color(255, 208,208)
else
crNoColor

This highlights Lecturer Hours (CurrentFieldValue) which is greater than the Max Week Hours allowed.

The function GridLabelAt gives you the Row header (Label) of the of the current row. The first parameter is the name of the row field (there may be multple row levels in a crosstab) - don't use the regular {table.field} syntax. The second parameter tells us which row to get, in this case the current row.

If the first parameter is the name of a column it gives you the column header instead, and the second parameter tells us which column to get.

It will also work if the row or column is a formula, use the name "@FormulaName".

Print Page | Close Window