Format Crosstab values when null

[Former Member]

Hi,

I am trying to format values in a CrossTab and need to format my null and '0' values with a white background. I use the following code:

  IF CurrentRowIndex in [2,4] THEN 
      IF GridValueAt(CurrentRowIndex, CurrentColumnIndex, CurrentSummaryIndex) > GridValueAt(CurrentRowIndex-1, CurrentColumnIndex, CurrentSummaryIndex)
    THEN Color (200, 230, 200)
    ELSE
      IF GridValueAt(CurrentRowIndex, CurrentColumnIndex, CurrentSummaryIndex) < GridValueAt(CurrentRowIndex-1, CurrentColumnIndex, CurrentSummaryIndex)
    THEN Color (255, 190, 190)
    ELSE CrNocolor
ELSE CrNocolor

Results are currently formatted as below:

I am looking for code to make the background of null values white. I have tried the following, but the isnull syntax requires a field value and doesn't allow crosstab values.

IF isnull(GridValueAt(CurrentRowIndex, CurrentColumnIndex, CurrentSummaryIndex))
    THEN Color (255, 190, 190)

Can anyone assist?

[Former Member]

If formatting "zeroes" with a white background is an option then conditionally formatting based on

gridvalueat(currentrowindex, currentcolumnindex, currentsummaryindex)=0 ought to do the trick. Yes, isnull() requires a field, but the crosstab's displayed value is "0" not null.

so your background formatting formula should be

if gridvalueat(currentrowindex, currentcolumnindex, currentsummaryindex)=0 then color(255,255,255) else ~some other color or condition~.

[former_member292966]

Hi Marius,

A simple thing to try is to open the report and go to File | Report Options. There should be an option for Convert NULL values to Default. That will check the type of field and for a numeric field change the NULL to 0. For a string field, it will change it to an empty string.

Brian