Using GridRowColumnValue to format a text object

[tmunson99]

I have a cross tab that I have successfully conditionally formatted the background color using GridRowColumnValue (see below). However, I can only use it to format fields and not text objects. In other words, my column total 'Total' heading is white while everything else is colored based on the conditional formatting applied. If I try to apply the conditional formatting to the background of the text object, I get the error, 'This function can only be used in field formatting formulas'.

IF GridRowColumnValue ("Something") STARTSWITH "XXX"
THEN crLime
...
...
...


Is there a way to format a text object based on the value of it's originating column data in the cross tab?

 

[lbass]

Please identify the names of your row, column, and summary fields.

-LB

 

[tmunson99]

Sure.

Rows:
vwSummary.Rate Pool
vwSummary.Project Type

Columns:
vwSummary.BA
vwSummary.BU

Summarized Fields:
Sum of vwSummary.Dollars
Percentage of Sum of vwSummary.Dollars

 

[lbass]

Sorry, I meant to ask you to also provide the exact formula you are using for formatting.

-LB

 

[tmunson99]

No problem...a couple modifications just for privacy, but you get the idea.

IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "AAA"
THEN crLime
ELSE IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "BBB"
THEN Color(175, 238, 238)
ELSE IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "CCC"
THEN Color(255, 255, 128)
ELSE IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "DDD"
THEN Color(64, 159, 255)
ELSE IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "EEE"
THEN Color(170, 234, 149)
ELSE IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "FFF"
THEN Color(255, 128, 0)
ELSE IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "GGG"
THEN Color(185, 211, 238)
ELSE IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "HHH"
THEN crYellow
ELSE
crNoColor

 

[lbass]

If you use the default setting with the total column first, then the following works. Change your formula above to:
whileprintingrecords;
numbervar x;
IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "AAA"
THEN x := crLime
ELSE IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "BBB"
THEN x := Color(175, 238, 238)
ELSE IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "CCC"
THEN x := Color(255, 255, 128)
ELSE IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "DDD"
THEN x := Color(64, 159, 255)
ELSE IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "EEE"
THEN x := Color(170, 234, 149)
ELSE IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "FFF"
THEN x := Color(255, 128, 0)
ELSE IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "GGG"
THEN x := Color(185, 211, 238)
ELSE IF GridRowColumnValue ("vw_LBR_Payroll_Summary.BA") STARTSWITH "HHH"
THEN x := crYellow
ELSE
x := crNoColor;
x

Then select the "Total" label->format field->background->color->x+2 and enter:

whileprintingrecords;
numbervar x;

If you use the setting where columns appear last, the total column label takes on the color value of the next column to the right. I couldn't figure out how to adjust for that.

-LB

 

[tmunson99]

That's work great except I have exactly what you described. My "Total" label is on the right and it takes on the color of the next grouping. If we could figure that out, it would be perfect! Great idea to assign the color to a variable. It's strange that it would assign it the next color since it should still be evaluating the previous grouping. If you come up with a solution, I'd appreciate it if you post it.