Skip to Content
0

Format Crosstab values when null

May 15, 2017 at 11:31 AM

83

avatar image
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?

sap.png (43.0 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Brian Dong May 15, 2017 at 10:13 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jul 07, 2017 at 08:46 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded