cancel
Showing results for 
Search instead for 
Did you mean: 

Conditional Format a CrossTab

Former Member
0 Kudos

Hello I have an existing cross-tab report that I need to do a conditional format on. In my report I have a sum of QTY Required by Day in the Summary section of the cross-tab. I also have a field call Balance on Hand on each Row. I would like to change the color for any of daily QTY required that are greater than the Balance on Hand. This is simple in a Standard Report, but for some reason Iu2019m struggling figure out how to make it work in a cross-tab. Thanks

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

You need to use the function gridrowcolumnvalue() to check the other column values with the existing one in conditional font color.

EX:

if gridrowcolumnvalue("field name")>currentfieldvalue then

crred

else

crblack

HTH,

Raghavendra.G

Former Member
0 Kudos

Thanks I must be doing something wrong when I insert this formula into the font condition. I receive an error saying a string is required. When I look at the help for the gridrowcolumnvalue command it says I need to use a Alias in the aurgument.

Former Member
0 Kudos

One more thing if I just use the currentfieldvalue command like if currentfieldvalue > QTY on Hand Then crRed Else crBlack. It works but it changes all fields in the row red even if only one matches the criteria.

former_member260594
Active Contributor
0 Kudos

If I understand correctly you have 2 summary values in the crosstab and you want to highlight the first summaryvalue, required, based on whether or not it is greater than the second summary value, on hand. If you were to switch the summary values around so that on hand appeared first then you could right click on it, format field, background and conditionally enter in a formula that assigns the currentfield value to a variable but output no color;

numbervar a:= currentfieldvalue;

crnocolor

Then in the second summary value right click, format field, background and conditionally compare the currentfieldvalue to the variable value;

numbervar a;

if currentfieldvalue > a then crred else crblack

Former Member
0 Kudos

The Font formula field is for the font name - Courier, Arial, etc. You need to put the formula in the Color formula field of the Font tab in the Format Field dialog...

HTH,

Carl

Former Member
0 Kudos

Ok I think I am very close to getting this. Just to be clear I only have one summary field in my cross tab call QTY Required. I have a row included that is Balance on Hand. The QTY Required is summarized by muti days. I want to change the background or the font for anything in the QTY Required field that > the Balance on Hand field. Right now if I use the method mentioned above it seems to be change the background for items that do not meet the condition. Is there a way for me to post my report as a PDF or image so you can see what I'm trying to do?

Thanks

Former Member
0 Kudos

Just cut/paste the formula that you have for Background Color. That should be enough...

Carl

Former Member
0 Kudos

This is what I have on my QTY on Hand field

numbervar a:=currentfieldvalue;

crnocolor

This is what I have on the summrized field call QTY Required

numbervar a;

if currentfieldvalue > a then crRed else crNocolor

It changes some fields to red but some of the fileds it's changing are not less the QTY on Hand. I starting to thing that is has to do with the way I have my rows set in the cross-tab

Former Member
0 Kudos

I think it's easier than that! Try this:


if currentfieldvalue > GridRowColumnValue("QTY On Hand") then crRed else crNocolor

The online help for GridRowColumnValue gives a fairly decent example, including changing font color based on values in the cross tab...

HTH,

Carl

Former Member
0 Kudos

I tried that before but since the gridrowcolumnvalue want a string it erros out is there away I can convert my QTY on Hand field to a string?

Former Member
0 Kudos

According to the Help, the string that it is looking for is the "field alias", which whenever I tried to use the function was the field name without the "{@" and "}", so my example would work if your field name was {@QTY On Hand}.

To be honest, I'm not all that familiar with field aliases. You may need to look that up in the Help...

HTH,

Carl

Former Member
0 Kudos

This was the issue once I got the Alias figured out the formula worked thanks for your help

Former Member
0 Kudos

I have one related question is it possible to do the same thing but with a total of a summerized field. I have tried it but since my QTY ON HAND field is not on the same row as the Total field it does not seem to work. Thanks for all your help by the way.

Former Member
0 Kudos

Can't you just use sum({@QTY On Hand})?

HTH,

Carl

Answers (0)