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,