Skip to Content
avatar image
Former Member

Format Crosstab values when null

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?

sap.png (43.0 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 15, 2017 at 10:13 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 07, 2017 at 08:46 AM

    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~.

    Add comment
    10|10000 characters needed characters exceeded