Conditionally Format Cross Tab Field

[Former Member]

I have a cross tab report that I want to conditionally format text color if row# 5 meets my criteria. Below is the formula I am trying in the Format Field editor.

if {@Type} = "8=POH"

then crred

(I have also tried adding the else statement.....else crblack)

I get no results when I try this. I can change the text color via the formatting toolbar, but this changes all data in all 6-8 rows.

Is there another method to conditionally format the row, or is it just not possible?

Thanks

[Former Member]

I just learned this yesterday, so here goes.

In the suppresion formula for the column total, enter the following expression:

numberVar x:=0;

False;

This is going to reset the row count for each column

Then in the suppression formula for the summary field, enter the following expression:

numberVar x := x + 1;

False;

And in the font color conditional formual, use the following expression:

numberVar X;

if (X = 5) and ({@Type} = "8=POH") then crRed else crNoColor;

This should give you a red 5th row if the type condition is met

Fuskie

Who cautions that he does not know what the {@Type} formula contains and that it might need to be modified to be used in this context...

[Former Member]

Thanks Fuskie.... Before I try this, I have to say I am stumped. Where would I find the "suppresion formula for the column total" and "suppression formula for the summary field"

[Former Member]

The summary field in a crosstab is the intersection of your column and row. On the design tab, its the single cell in the center. On the preview, its all the middle cells (as the crosstab is expanded by your data). Right click on the data object and then go to the Common tab. Look foro the Suppression checkbox, and then click on the little "x" button to the right which allows you to create a conditional suppression.

Fuskie

Who notes that the False at the end of the expression ensures that the expression will resolve to false (don't suppress)...

[Former Member]

Thats what I thought. I tried that with no luck. I checked and un-checked the Suppress box too.

I even tried changing it to row 1 and 2, and still no luck.

Any ideas as to what may have went wrong?

[Former Member]

What is in your @Type formula and what is the summary field calculation?

Fuskie

Who needs the truth, the whole truth and nothing but the truth...

[Former Member]

@Type formula is as follows

if {F3413.MRQT} = "05"

then "1+OH"

else if {F3413.MRQT} = "15"

then "2+PO"

else if {F3413.MRQT} = "70"

then "4-FWO"

else if {F3413.MRQT} = "85"

then "8=POH"

else if {F3413.MRQT} = "95"

then "9+PLO"

else if {F3413.MRQT} = "45"

then "6-FST"

else if {F3413.MRQT} = "80"

then "5-PWO"

else if {F3413.MRQT} = "25"

then "3+WO"

else if {F3413.MRQT} = "55"

then "7-SO"

The summary field calculation is the sum of a qty field(number)

Hope this helps

[Former Member]

Yeah. this is probably your problem. Crosstabs are kind of funky in that they are not linearly in the way you think of group headers, details and footers. When you are evaluating data in a conditional formatting expression, the only data you have to work with is the current cell value (CurrentFieldValue) and the data from the current section record. If the crosstab is in a group header or footer, the latter is the first or last record in the group, and if the crosstab is in the report footer, its the last record in the report.

My guess is that your formula is based on individual record data that is not accessible in the cross tab; I would bet if you looked at the MRQT field separately, you would see that it has the value of the section record as described above.

Fuskie

Who off the top of his head does not have an alternative recommendation for you...

[Former Member]

Thanks for all your help Fuskie

Travis

Who is very amused by Fuskies additional comments!