Crosstab -Highlight Min and Max value in Each Row 1

[smsaji]

Hi,

Using Cyrstal Reports 10. In the crosstab report, for each row need to change the background color for the minimum and maximum value in that row.

Using stored procedure as datasource.

Read couple of posts here in the forum and tried something like the following and it changes background color for one cell for the whole crosstab . Anyone know, is it possible to highlight the minmum and maximum summaryvalue for each row.

Format->border->background->X+2 :

if CurrentFieldValue in [ maximum ({ProcedureName.SummaryFieldName}) ]
then silver
else
white

-saj

 

[lbass]

In order to do this, I think you would need to have your stored procedure return the value of each cell per row, using the equivalent of one SQL expression per row. Basically, you need to define the sum of the values that intersect based on your column and row fields, and then limit the set to a particular row. I can show you how to do this with a SQL expression, but you would then have to translate this into your stored procedure.

Let's use the Xtreme database and say that your row is "shipper", your column is "year", and your summary is sum of {Orders.Order Amount}. First you would create six SQL expressions like the following:

[{%FedEx}:]
(select sum(A.`Order Amount`) from Orders A where
A.`Ship Via` = Orders.`Ship Via` and
{fn YEAR(A.`Order Date`)} = {fn YEAR(Orders.`Order Date`)} and
A.`Ship Via` = 'FedEx')

You would repeat this for the other five shippers, changing only the shipper named in the last clause.

Your crosstab can still use the original fields/summaries. Then you would right click on your crosstab cells->format field->border->background color->x+2 and enter:

if CurrentFieldValue in[maximum({%Loomis}), maximum({%FedEx}),maximum({%Pickup}), maximum({%Purolator}),maximum({%Parcel Post}), maximum({%UPS})] then crYellow else
if currentfieldvalue in[minimum({%Loomis}), minimum({%FedEx}),minimum({%Pickup}), minimum({%Purolator}),minimum({%Parcel Post}), minimum({%UPS})] then crRed else crNoColor

If you want the minimum and maximum the same color, then place the minimums in the same brackets as the maximums.

-LB

 

[smsaji]

lbass,

Thanks a lot...for the reply. Will work on your solution

-saj

 

[smsaji]

Thank you, lbass, your solution works.

But ’25’ is max in first row, it’s highlighted. (yellow color)
In second row ’59’ is max, so it’s highlighted. But ‘25’ in second row is also highlighted. So second row has two cells highlighted with yellow instead of one. Is there a way to limit it only to first row.

Same thing with min.
0.4 is min in first row . It’s highlighted. (blue color)
There’s 1.1 in first and fourth row. 1.1. is min in fourth row. It is highlighted in both rows . So first row has two cells highlighted in blue instead of one.

In this report, the rows are constant with four rows but the number of columns vary with the parameter from 2 columns to 30 columns.

Any suggestions to compare a value with specific row.

As a sample, tried the following to force highlight only the ‘25’ in second row:, but it highlights none.

Stringvar str = GridRowColumnValue("ProcName.RowName");

IF CurrentFieldValue in [25.0] AND trim(str) = "Delivery Services"
then crYellow
else
crwhite

Any suggestions, welcome.

-saj

 

[lbass]

Sorry about that--I see the problem. My test data didn't have repeated values, so I didn't pick up on this problem. Change the highlighting formula to:

if GridRowColumnValue("ProcName.RowName") = "Delivery Services" then
(
if CurrentFieldValue = maximum({%DeliveryServices}) then crYellow else
if currentfieldvalue = minimum({%DeliveryServices}) then crBlue
) else
if GridRowColumnValue("ProcName.RowName") = "On-Site
Services" then //made-up service
(
if CurrentFieldValue = maximum({%OnSiteServices}) then crYellow else
if currentfieldvalue = minimum({%OnSiteServices}) then crBlue
) else
//etc.

-LB

 

[smsaji]

Will get back after trying. Very much appreciate quick response!!

 

[smsaji]

Awesome...as always! Works Great!!

Million thanks, lbass!

saj