Conditionally Highlight Cells in Crosstab - Crystal Reports
Hello Experts,
I am using CR version 14.2.4 and attempting to make a sort of 'heat display' in a crosstab by conditionally highlighting the cells. I tried putting the formula below into the formula editor on the Border tab in 'Background', but it did not display a highlight, which didn't surprise me because there are two conditions to reference - the hour and the day.
if distinctcount({CAD_Data.CA
then silver else nocolor
I attached a sample of the crosstab I am working with, and a screenshot of what I am trying to do. Do you know of a way I can use Crystal Reports to create this sort of chart? If Crystal Reports can't do this sort of 'heat' display, can you recommend a graphing software?
Thank you for your help.
Respectfully, ChopSampleDataCFS.pdf
I am using CR version 14.2.4 and attempting to make a sort of 'heat display' in a crosstab by conditionally highlighting the cells. I tried putting the formula below into the formula editor on the Border tab in 'Background', but it did not display a highlight, which didn't surprise me because there are two conditions to reference - the hour and the day.
if distinctcount({CAD_Data.CA
then silver else nocolor
I attached a sample of the crosstab I am working with, and a screenshot of what I am trying to do. Do you know of a way I can use Crystal Reports to create this sort of chart? If Crystal Reports can't do this sort of 'heat' display, can you recommend a graphing software?
Thank you for your help.
Respectfully, ChopSampleDataCFS.pdf
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!
ASKER
Thanks again!
Respectfully, Chop
numbervar LowestValue := 319;
numbervar HighestValue:= 1712;
numbervar RangeEquation:= HighestValue - LowestValue;
numbervar ChunkEquation:= RangeEquation / 13; //13 = number of colors
if currentfieldvalue >= LowestValue
and currentfieldvalue <= ( LowestValue + (1 * ChunkEquation) )
then color(81,143,219) //blue darkest
else
if currentfieldvalue >= LowestValue
and currentfieldvalue <= ( LowestValue + (2 * ChunkEquation) )
then color(121,169,227) //blue middle
else
if currentfieldvalue >= LowestValue
and currentfieldvalue <= ( LowestValue + (3 * ChunkEquation) )
then color(192,218,245) //blue lightest
else
if currentfieldvalue >= LowestValue
and currentfieldvalue <= ( LowestValue + (4 * ChunkEquation) )
then color(95,143,80) //green darkest
else
if currentfieldvalue >= LowestValue
and currentfieldvalue <= ( LowestValue + (5 * ChunkEquation) )
then color(146,187,134) //green middle
else
if currentfieldvalue >= LowestValue
and currentfieldvalue <= ( LowestValue + (6 * ChunkEquation) )
then color(214,230,208) //green lightest
else
if currentfieldvalue >= LowestValue
and currentfieldvalue <= ( LowestValue + (7 * ChunkEquation) )
then color(252,239,150) //yellow
else
if currentfieldvalue >= LowestValue
and currentfieldvalue <= ( LowestValue + (8 * ChunkEquation) )
then color(254,220,186) //orange lighter
else
if currentfieldvalue >= LowestValue
and currentfieldvalue <= ( LowestValue + (9 * ChunkEquation) )
then color(255,164,119) //orange darker
else
if currentfieldvalue >= LowestValue
and currentfieldvalue <= ( LowestValue + (10 * ChunkEquation) )
then color(255,198,198) //red lightest
else
if currentfieldvalue >= LowestValue
and currentfieldvalue <= ( LowestValue + (11 * ChunkEquation) )
then color(255,151,151) //red second lightest
else
if currentfieldvalue >= LowestValue
and currentfieldvalue <= ( LowestValue + (12 * ChunkEquation) )
then color(255,106,106) //red third lightest
else
if currentfieldvalue >= LowestValue
and currentfieldvalue <= ( LowestValue + (13 * ChunkEquation) )
then color(255,55,55) //red darkest
>>>>>>>>>>>>>>>>>>>>>>>>>>
Declare
@Low int
, @High int
, @Range float
, @Chunk Float
, @NumberOfColors int
, @Data int
--------------------------
Set @Data = 1199
--------------------------
Set @NumberOfColors = 15
Set @Low = 319
Set @High = 1712
Set @Range = @High - @Low
Set @Chunk = @Range / @NumberOfColors
--------------------------
Select @NumberOfColors As '@NumberOfColors'
, @Low As '@Low'
, @High As '@High'
, @Range As '@Range'
, @Chunk As '@Chunk'
--------------------------
Select Case
When @Data >= @Low And @Data <= ( @Low + (1 * @Chunk) ) Then 'Color 1'
When @Data > @Low And @Data <= ( @Low + (2 * @Chunk) ) Then 'Color 2'
When @Data > @Low And @Data <= ( @Low + (3 * @Chunk) ) Then 'Color 3'
When @Data > @Low And @Data <= ( @Low + (4 * @Chunk) ) Then 'Color 4'
When @Data > @Low And @Data <= ( @Low + (5 * @Chunk) ) Then 'Color 5'
When @Data > @Low And @Data <= ( @Low + (6 * @Chunk) ) Then 'Color 6'
When @Data > @Low And @Data <= ( @Low + (7 * @Chunk) ) Then 'Color 7'
When @Data > @Low And @Data <= ( @Low + (8 * @Chunk) ) Then 'Color 8'
When @Data > @Low And @Data <= ( @Low + (9 * @Chunk) ) Then 'Color 9'
When @Data > @Low And @Data <= ( @Low + (10 * @Chunk) ) Then 'Color 10'
When @Data > @Low And @Data <= ( @Low + (11 * @Chunk) ) Then 'Color 11'
When @Data > @Low And @Data <= ( @Low + (12 * @Chunk) ) Then 'Color 12'
When @Data > @Low And @Data <= ( @Low + (13 * @Chunk) ) Then 'Color 13'
When @Data > @Low And @Data <= ( @Low + (14 * @Chunk) ) Then 'Color 14'
When @Data > @Low And @Data <= ( @Low + (15 * @Chunk) ) Then 'Color 15'
End