Highligthing in cross tab by comparing column values

[porsche2k7]

Hi All,

I have the following cross tab report.

Revenue Remit
Sept 100 100
Aug 90 80
July 230 220

I need to highlight the values in the Remit column where Remit <> Revenue. Any ideas on how to accomplish this?

Thanks in advance.

 

[lbass]

Can you clarify whether revenue and remit are separate summary fields? Or are they two instances of one column field?

Please explain the actual set up in crosstab expert: row, column, and summary fields.

-LB

 

[porsche2k7]

Revenue & Remit are separate summary fields.

Row = MonthYear
Column = None (No Values for Column)
Summary Fields = Revenue & Remit

 

[lbass]

Okay, then you can right click on the revenue field->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar rev := currentfieldvalue;
false

Then select the remit field->right click->format field->borders->color->background->x+2 and enter:

whileprintingrecords;
numbervar rev;
if currentfieldvalue <> rev then
cryellow else
crnocolor

-LB

 

[porsche2k7]

Worked out great. Also just wondering, how do I highlight the entire row when remit <> revenue?

Thanks LB. This is not the first time you are helping me out.

 

[lbass]

This is more complicated. You would have to place the crosstab in the report footer. In the main report, insert a group on your row field, which I will assume is a string. Then create formulas like this:

//{@diff}:
{table.revenue}-{table.remit}

//{@moyrarray} to be placed in the monthyear group footer:
whileprintingrecords;
stringvar array x;
numbervar i := i + 1;
if sum({@diff},{@monthyear}) <> 0 then (
redim preserve x;
x := {@monthyear}
);

Then on the crosstab, highlight the row label, the rev and remit summaries all at once->right click->format field->borders->color->background->x+2 and enter:

whileprintingrecords;
stringvar array x;
if gridrowcolumnvalue("@MonthYear") in x then
cryellow else
crnocolor

You should replace @MonthYear with the name of your row field, replacing the curly brackets with ". Make sure that the row field and the group field in the main report are the same. I did test this, so it should work for you.

If you can't group on the row field for some reason, insert the crosstab in a subreport so you can. You can suppress the group and detail sections.

-LB

 

[porsche2k7]

Thanks LB. Works great.