Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] NODATA='0.00 ' is dropping space at end
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] NODATA='0.00 ' is dropping space at end
 Login/Join
 
Member
posted
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,


WebFOCUS 8.2
Windows, All Outputs
 
Posts: 7 | Registered: July 27, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2392 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
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?


WebFOCUS 8.2
Windows, All Outputs
 
Posts: 7 | Registered: July 27, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2392 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] NODATA='0.00 ' is dropping space at end

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.