[CLOSED] NODATA='0.00 ' is dropping space at end

August 13, 2020, 10:08 AM

I am trying to create an XLSX format report. The end user is complaining because the data doesn't line up.

Issue: The data coming in from the database can be zeroes and there can also be missing data. The format they want is floating $, commas, and () around negatives numbers, so I used the following: SUM CUST_PAYMENT_RECEIVED_AT/D12.2MCB

And they want $0.00 to print on report for missing data. So, in WF I used the SET NODATA='$0.00 ' – the problem is it will not print the ending space so the report is not lining up - the reason I need the extra space at the end is due to the () around negative numbers. When I do a SET NODATA='$0.00)' the data lines up!

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

August 13, 2020, 10:41 AM

MartinY

Using SET NODATA it like sending a character string in case that there is missing data in field, not a real numeric value.

Try the following
Important lines are the SET and the NSALES where it test for MISSING value
In my sample a fake missing data in TSALES for FRANCE and generates a negative value for ENGLAND

-*-* Force to have a displayed 0 in front of the decimal value
SET CENT-ZERO = ON

DEFINE FILE CAR
TSALES /I6 MISSING ON = IF COUNTRY EQ 'FRANCE' THEN MISSING ELSE SALES;
NSALES /D12.2MCB MISSING ON = IF TSALES EQ MISSING THEN 0 ELSE IF COUNTRY EQ 'ENGLAND' THEN TSALES * -1 ELSE TSALES;
-*-* Below line is the real NSALES that should be used assuming that original data SALES may already contains missing value or negative ones
-*NSALES /D12.2MCB MISSING ON = IF SALES EQ MISSING THEN 0 ELSE SALES;
END
TABLE FILE CAR
SUM SALES
    TSALES
    NSALES
BY COUNTRY
ON TABLE PCHOLD FORMAT XLSX
END
-RUN

August 13, 2020, 11:20 AM

AnnetteTate

Thanks SO MUCH for the quick response!

I tried the code and the report is printing spaces for the missing data. So, maybe I am confused on what the missing data means.

The database results set does not have nulls in the field. I think the "missing data" is generated by WF when it builds the grid-like report.

Is there a way for me to send you an example of report and code? Doesn't look like forum allows attachements?

August 13, 2020, 11:44 AM

MartinY

Use the code tag which is the last on on the ribbon that looks like the below

</>

WF is not generating "missing data" except if in your data relationship (parent/child) does not exist depending on how you manage the relationship : this is a whole subject by itself...
WF will display a empty data in the grid when the row/column intersection have no data to display.
Per example I have a report that shows sales per Region (A, B, C) and Month (Jan to Dec) but Region-C has no sales in June so, that row/column intersection will remain empty.

One way to avoid that is to first create a hold file with only 0 data for each Region & Month that will then be merged with your real data.
That way you will always have at least 0$ in each cell (Region/Month) and my previous code will work.

Sample code:

-*-* This step is only to create an empty row/col value for West and Feb
DEFINE FILE GGSALES
MM       /MONTH = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS/I8 AS 'DOLLARS'
BY REGION
BY MM
WHERE (REGION EQ 'West' AND MM NE 2) OR REGION NE 'West';
ON TABLE HOLD AS EXTDATA
END
-RUN

-*-* This step is to create the default matrix data
TABLE FILE GGSALES
SUM COMPUTE DOLLARS/I8 = 0;
BY REGION
BY MM
ON TABLE HOLD AS DEFAULTDATA
END
-RUN

SET CENT-ZERO = ON
TABLE FILE EXTDATA
SUM DOLLARS/D12.2MCB
BY REGION AS ''
ACROSS MM AS ''
-*-* To perform a MORE, all fields must have the same format and name between each used files
MORE
FILE DEFAULTDATA
END
-RUN