Focal Point

[CLOSED] Formatting Numbers in a TABLE

This topic can be found at:

http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6437063196

http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6437063196

June 12, 2019, 10:05 AM

[CLOSED] Formatting Numbers in a TABLE

Hi Folks,I'm almost sorry to ask this cause I'm sure its somewhere in the great wide web... but I can't find it.

I am displaying some numbers in a report (a TABLE). I would like to show numbers like 9234.4 as 9,234 (thousands separator was added and the decimal was dropped) and 123456789 as 123M.

I have found several example how to do this using a chart (a GRAPH request), but I can't seem to use the same syntax in a TABLE request. Can anyone help me out here?

The data is D12.1 format. I've tried I12 but then the thousands separator is dropped.

Like I said, I'm sure this sort of thing has been asked before, and I'm cool if you just direct me to those threads, but I've spent about 90 minutes on this, and that seems just too long for formatting.

Thank you...

WebFOCUS 8201, SP 0.1, Windows 7, HTML

June 12, 2019, 10:11 AM

Additionally, in 8205, https://techsupport.informatio...tech/wbf/8205snf.pdf IB has added a feature called COMPACTFORMAT. See page 134 for details to find out how you can reformat long numbers with Thousands, Millions, Billions and Trillions.

WebFOCUS 8206, Unix, Windows

June 12, 2019, 10:19 AM

Thank you very much!

WebFOCUS 8201, SP 0.1, Windows 7, HTML

June 12, 2019, 12:02 PM

I remember that someone has shared a DEFINED FUNCTION for number display, but can't find it.

I have intentionally keep value below 10K as is and above as ##K. So, I'm just dividing by 1000 those values.

TABLE FILE CAR SUM RETAIL_COST COMPUTE NDEC /P10C = IF RETAIL_COST LT 10000 THEN RETAIL_COST ELSE IF RETAIL_COST GE 10000 AND RETAIL_COST LT 1000000 THEN RETAIL_COST / 1000 ELSE RETAIL_COST / 1000000; NOPRINT COMPUTE NFMT /A15V = IF RETAIL_COST LT 10000 THEN FPRINT(NDEC, 'P10C', 'A14') ELSE IF RETAIL_COST GE 10000 AND RETAIL_COST LT 1000000 THEN FPRINT(NDEC, 'P10C', 'A14') || 'K' ELSE FPRINT(NDEC, 'P10C', 'A14') || 'M'; AS 'New Fmt' BY COUNTRY BY CAR BY MODEL END

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

June 12, 2019, 03:04 PM

TABLE FILE CAR SUM COMPUTE MONEY/D20 = IF LST.MONEY EQ 0 THEN 1 ELSE LST.MONEY * 10; COMPUTE COMPACT/A11V = IF MONEY GE 1000000000000 THEN FTOA(MONEY/1000000000000, '(D7.1M)',COMPACT)||'T' ELSE IF MONEY GE 1000000000 THEN FTOA(MONEY/1000000000, '(D7.1M)',COMPACT)||'B' ELSE IF MONEY GE 1000000 THEN FTOA(MONEY/1000000, '(D7.1M)',COMPACT)||'M' ELSE IF MONEY GE 1000 THEN FTOA(MONEY/1000, '(D7.1M)',COMPACT)||'K' ELSE FTOA(MONEY, '(D7M)',COMPACT); BY MODEL NOPRINT WHERE RECORDLIMIT EQ 15 ON TABLE SET PAGE-NUM NOPAGE ON TABLE SET CENT-ZERO ON ON TABLE SET STYLE * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/warm.sty,$ TYPE=DATA, COLUMN=COMPACT, JUSTIFY=RIGHT, $ ENDSTYLE END