Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Formatting Numbers in a TABLE
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Formatting Numbers in a TABLE
 Login/Join
 
Platinum Member
posted
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
 
Posts: 167 | Registered: May 19, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 167 | Registered: May 19, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2233 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Master
posted Hide Post
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
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 493 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Formatting Numbers in a TABLE

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.