| Author |
Message |
rvink
Groupie
Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
|
   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 Logged |
|
|
rvink
Groupie
Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
|
Posted: 27 Sep 2010 at 10:57am |
|
Anyone?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
rvink
Groupie
Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
rvink
Groupie
Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
rvink
Groupie
Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
|
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 Logged |
|
|
|
