Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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, 2017Report 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: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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, 2017Report 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: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders