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,

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,

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!

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

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,