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

June 12, 2019, 10:05 AM
Shingles
[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...

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


WebFOCUS 8201, SP 0.1, Windows 7, HTML
June 12, 2019, 10:11 AM
BabakNYC
D12.1C or I9C where C stands for comma separated 1000's.

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.

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


WebFOCUS 8206, Unix, Windows
June 12, 2019, 10:19 AM
Shingles
Ah... right... thank you. I have 8201M (looking to upgrade very soon). I can put a hold on that millions formatting.

Thank you very much!


WebFOCUS 8201, SP 0.1, Windows 7, HTML
June 12, 2019, 12:02 PM
MartinY
By the time we get updated to 8205, you can play around using something such as this. This is only one way to do it.
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
Hallway
We are also pre 8205 and dont have that function available. So, we use the FTOA function like below:
  
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

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


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs: