[closed]How to display hyphen "-" instead of zero (0) value in Excel?

Focal Point

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7347018826

February 16, 2013, 05:07 PM

bug

[closed]How to display hyphen "-" instead of zero (0) value in Excel?

My client wanted to display hyphen "-" instead of zero values in an Excel report. I can only find the zero-suppressing format, which just display nothing in the cell. So here is the question:

1. Is it possible to do this natively in WebFOCUS?

2. If #1 is no, can I pass an Excel cell formatting string from WebFOCUS to the output spreadsheet?

This message has been edited. Last edited by: bug,

February 17, 2013, 07:15 AM

Ram Prasad E

Change zeros to missing values and set NODATA to hypen("-"). Hope this should work.

Thanks,
Ram

February 17, 2013, 07:42 AM

Ram Prasad E

Here is the sample code.

SET NODATA = -
-RUN
TABLE FILE CAR
SUM
SALES
COMPUTE NEW_COL/D12 MISSING ON = IF SALES EQ 0 THEN MISSING ELSE SALES;
BY COUNTRY
ON TABLE HOLD AS EXL1 FORMAT HTMTABLE
END
-RUN
SET HTMLFORMTYPE='XLS'
-RUN
-HTMLFORM BEGIN
<HTML>
<BODY>
!IBI.FIL.EXL1;
</BODY>
</HTML>
-HTMLFORM END

Hope this helps.

Thanks,
Ram

February 18, 2013, 09:09 AM

bug

Thanks Ram! This may work for non-monetary data. When the numbers are dollar amount, a left-justified dollar sign ($) is required. If I define the missing data as "$ -" then the dollar sign is not justified with other non-zero value amounts. Any work around for this?

February 19, 2013, 12:47 AM

Ram Prasad E

yes, you are correct. If you require $ and space with proper alignment, then use FTOA instead of NODATA. Here is the sample code.

TABLE FILE CAR
SUM
SALES
COMPUTE NEW_COL/A80 = IF SALES EQ 0 THEN '$ &|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;-' ELSE FTOA(SALES,'(D20M)','A40');
BY COUNTRY
ON TABLE HOLD AS EXL1 FORMAT HTMTABLE
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=NEW_COL, JUSTIFY=RIGHT, $
ENDSTYLE
END
-RUN
SET HTMLFORMTYPE='XLS'
-RUN
-HTMLFORM BEGIN
<HTML>
<BODY>
!IBI.FIL.EXL1;
</BODY>
</HTML>
-HTMLFORM END

Hope this helps.

Thanks,
Ram

February 19, 2013, 12:50 AM

Ram Prasad E

Alternate method is to use Excel Template. With this you can create your own customized report templates.

February 19, 2013, 07:15 AM

FrankDutch

Ram
If you do it this way I wonder if you still are able to ad the sum to that column, since it now is a string that looks like a value
In excel I am rather sure it won't give you a god result.

The template is the better solution

	*Frank*
	prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7