Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Highlighting cells in Crosstabs Post Reply Post New Topic
Author Message
rvink
Groupie
Groupie
Avatar

Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
Quote rvink Replybullet Topic: Highlighting cells in Crosstabs
    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.


Edited by rvink - 27 Sep 2010 at 10:55am
IP IP Logged
rvink
Groupie
Groupie
Avatar

Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
Quote rvink Replybullet Posted: 27 Sep 2010 at 10:57am
Anyone?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
 
IP IP Logged
rvink
Groupie
Groupie
Avatar

Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
Quote rvink Replybullet 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).
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
 
IP IP Logged
rvink
Groupie
Groupie
Avatar

Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
Quote rvink Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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...
IP IP Logged
rvink
Groupie
Groupie
Avatar

Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
Quote rvink Replybullet 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".



Edited by rvink - 28 Sep 2010 at 4:08pm
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.