Could anyone show me the code to populate ACCROSS values with 0.00 P15.2CM when there is no amount returned. I need this to default to 0.00 when there is no month value. Please see example

SUM
OPEN_RECEIVABLES/P15.2CM AS ' '

BY HIGHEST TOT_REC NOPRINT
BY ACCOUNT_NUMBER
BY DAYS_TO_PAY
BY CUSTOMER

ACCROSS HIGHEST MONTH_OF_CALENDAR NOPRINT
ACCROSS MONTH AS ' '

This populates the sum of open receivables by month across the page in EXCEL output as expected, however the field months with no open receivables are blank instead of $0.00. Users are asking for dollar value 0.00 for sorting

WF8.1
Any help would be great thanks !! - Tim A

This message has been edited. Last edited by: FP Mod Chuck,

Try using SET NODATA

e.g.

SET NODATA = '$0.00'

TABLE FILE CAR
SUM COMPUTE VAL/P15.2CM = SALES ;
BY COUNTRY
ACROSS SEATS
END

I tried, and no luck

here is the actual code if it helps..

TABLE FILE O
SUM
OPN_REC/P15.2CM AS ' '

BY HIGHEST TOT_REC NOPRINT

BY PTRN_CD
BY DTP
BY CUSTOMER
BY DESK
BY TEAM
BY EVT_333
BY EVT_ST
BY TOT_REC/P15.2CM

ACROSS HIGHEST MOC NOPRINT
ACROSS MNTH AS ' '


HEADING
"Open Demurrage"

Can you reproduce with the CAR file or one of the other sample files ?

this comes back with 0 for the sales amount field under the models with no sales. I see the sales field is formatted to integer. this tells me i need to check the table file formatting for null values

TABLE FILE CAR
SUM
SALES

ACROSS MODEL

-*?FF
-*-EXIT


ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL'
-*ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='Letter',
LEFTMARGIN=0.500000,
RIGHTMARGIN=0.500000,
TOPMARGIN=0.500000,
BOTTOMMARGIN=0.500000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,

$
TYPE=REPORT,
GRID=ON,
FONT='CALIBRI',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
LINES-PER-PAGE=UNLIMITED,
$
TYPE=TITLE,
COLOR='WHITE',
BACKCOLOR=RGB(255 102 0),
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=HEADING,
STYLE=BOLD+ITALIC,
SIZE=10,
$
TYPE=TABHEADING,
SIZE=10,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END

I double checked the field format and the data for opn_rec is definitely P15.2CM. So, i held the end results in a table file and printed. The blank months defaulted back to the $0.00 values. very strange but that seems to work.

ACROSS is just another way to perform a BY where the ACROSS "order" the field in columns when a BY it's by rows.

So lets pretend that you create your report using only BY. If you don't have data in a specific measure for one of the Month, it won't show up in the result as a row, it's the same thing with ACROSS.
It's not a question of data type.

See below 2 sample and pay attention to "Capuccino"

-* With Cappuccino displayed
DEFINE FILE GGSALES
YR    /YY = DATE;
MM    /Mt = DATE;
SALES /P15.2CM = DOLLARS;
END
TABLE FILE GGSALES
SUM SALES  AS 'Sales'
BY REGION  AS ''
BY PRODUCT AS ''
ACROSS YR  AS ''
ACROSS MM  AS ''
WHERE REGION EQ 'Northeast';
WHERE YR EQ 1996;
END
-RUN

-* Without Cappuccino displayed
DEFINE FILE GGSALES
YR    /YY = DATE;
MM    /Mt = DATE;
SALES /P15.2CM = DOLLARS;
END
TABLE FILE GGSALES
SUM SALES  AS 'Sales'
BY REGION  AS ''
BY PRODUCT AS ''
ACROSS YR  AS ''
ACROSS MM  AS ''
WHERE REGION EQ 'Northeast';
WHERE YR EQ 1996;
WHERE MM LT 'Jul';
END
-RUN

And to have the missing data displayed with a valid formatted value, below could be an option

SET NODATA = $0.00
DEFINE FILE GGSALES
YR    /YY = DATE;
MM    /Mt = DATE;
SALES /P15.2CM = DOLLARS;
END
TABLE FILE GGSALES
SUM SALES  AS 'Sales'
BY REGION  AS ''
BY PRODUCT AS ''
ACROSS YR  AS ''
ACROSS MM  AS ''
WHERE REGION EQ 'Northeast';
WHERE YR EQ 1996;
-*WHERE MM LT 'Jul';
END
-RUN