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     [SOLVED] Field format from .000 or .00 or .0 to display as 0
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Field format from .000 or .00 or .0 to display as 0
 Login/Join
 
Member
posted
Hi All - When I run the SQL from Oracle SQL developer I see the field value as 0


When I use the same SQL passthrough and print from WebFOCUS I get the values as .00.

Can you please help to dispay as 0 from webfocus ?

Note : datatype of field is NUMBER(12,2)

WebFOCUS 7703,Orcale 11.04

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


WebFOCUS 7.7
Unix
All Formats : Excel,PDF,HTML,Dlimiter,.txt
 
Posts: 4 | Registered: May 30, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You have several ways to fix that.

1- Change master file USAGE field definition. Actually you probably have something such as USAGE=D10.2 defined for your field. Change it for USAGE=D10
But this change will affect all places where this field is used : the decimals will now be hidden and the field will be rounded.

If you only want a specific report display to be affected
2- From a DEFINE
DEFINE FILE xyz
nFld /D10 = myFieldToChangeFmt;
END
TABLE FILE xyz
PRINT (or SUM) nFld
...
END

3- From a dynamic reformat
TABLE FILE xyz
PRINT (or SUM) COMPUTE nFld /D10 = myFieldToChangeFmt;
...
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: 2159 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
Hello Martin,

This was my first post in this forum and thanks for the quick response.

Actually I forgot to mention, I should have other values not modified for example other values are like .10 , .30 and 2.40 these should be displayed as it is.


WebFOCUS 7.7
Unix
All Formats : Excel,PDF,HTML,Dlimiter,.txt
 
Posts: 4 | Registered: May 30, 2017Reply With QuoteReport This Post
Expert
posted Hide Post
Simplest method is to use dynamic reformatting.

Something like this -
TABLE FILE GGSALES
SUM COMPUTE FMT/A8 = IF REGION EQ 'Northeast' THEN 'D15c' ELSE 'D15.2c'; NOPRINT
    DOLLARS/FMT
BY REGION
END


The FMT compute in my example determines what the display format will be for the DOLLARS value. Its format must be "A8" and should be able to contain the format that you want to see in your output.

I have determined the format based on REGION but you should be able to compare it to the value itself.

Good luck and welcome to the forum.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5601 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Guru
posted Hide Post
quote:
Originally posted by Tony A:
Simplest method is to use dynamic reformatting.

Something like this -
TABLE FILE GGSALES
SUM COMPUTE FMT/A8 = IF REGION EQ 'Northeast' THEN 'D15c' ELSE 'D15.2c'; NOPRINT
    DOLLARS/FMT
BY REGION
END


The FMT compute in my example determines what the display format will be for the DOLLARS value. Its format must be "A8" and should be able to contain the format that you want to see in your output.

I have determined the format based on REGION but you should be able to compare it to the value itself.

Good luck and welcome to the forum.

T


That's FANTASTIC!! I had no idea that you can do that.

To take that a little further, I found that you can use the MOD function to evaluate the number and do this on any value that is a whole number:

  

TABLE FILE WF_RETAIL_LITE
PRINT 
    REVENUE_US
    COMPUTE MOD/D12.2 = MOD(REVENUE_US, 1);
    COMPUTE FMT/A8 = IF MOD EQ 0 THEN 'D12M' ELSE 'D12.2M';
    REVENUE_US/FMT
BY  PRODUCT_CATEGORY
BY  TIME_DATE
WHERE PRODUCT_CATEGORY EQ 'Accessories'
WHERE TIME_DATE EQ '20130101'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET CENT-ZERO ON
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/flat.sty,$
ENDSTYLE
END



A little off topic, but if you prefer a leading zero on decimals that are < 1 and > -1 you can use the CENT-ZERO set command like I have in the code above.


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 431 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Originally posted by Tony A:
Simplest method is to use dynamic reformatting.

Something like this -
TABLE FILE GGSALES
SUM COMPUTE FMT/A8 = IF REGION EQ 'Northeast' THEN 'D15c' ELSE 'D15.2c'; NOPRINT
    DOLLARS/FMT
BY REGION
END


The FMT compute in my example determines what the display format will be for the DOLLARS value. Its format must be "A8" and should be able to contain the format that you want to see in your output.

I have determined the format based on REGION but you should be able to compare it to the value itself.

Good luck and welcome to the forum.

T


Thank you so much Tony!
It worked without any issue. Thanks again!


WebFOCUS 7.7
Unix
All Formats : Excel,PDF,HTML,Dlimiter,.txt
 
Posts: 4 | Registered: May 30, 2017Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Originally posted by Hallway:
quote:
Originally posted by Tony A:
Simplest method is to use dynamic reformatting.

Something like this -
TABLE FILE GGSALES
SUM COMPUTE FMT/A8 = IF REGION EQ 'Northeast' THEN 'D15c' ELSE 'D15.2c'; NOPRINT
    DOLLARS/FMT
BY REGION
END


The FMT compute in my example determines what the display format will be for the DOLLARS value. Its format must be "A8" and should be able to contain the format that you want to see in your output.

I have determined the format based on REGION but you should be able to compare it to the value itself.

Good luck and welcome to the forum.

T


That's FANTASTIC!! I had no idea that you can do that.

To take that a little further, I found that you can use the MOD function to evaluate the number and do this on any value that is a whole number:

  

TABLE FILE WF_RETAIL_LITE
PRINT 
    REVENUE_US
    COMPUTE MOD/D12.2 = MOD(REVENUE_US, 1);
    COMPUTE FMT/A8 = IF MOD EQ 0 THEN 'D12M' ELSE 'D12.2M';
    REVENUE_US/FMT
BY  PRODUCT_CATEGORY
BY  TIME_DATE
WHERE PRODUCT_CATEGORY EQ 'Accessories'
WHERE TIME_DATE EQ '20130101'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET CENT-ZERO ON
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/flat.sty,$
ENDSTYLE
END



A little off topic, but if you prefer a leading zero on decimals that are < 1 and > -1 you can use the CENT-ZERO set command like I have in the code above.


Thanks for the response,When i use MOD function i receive below error

ADHOCRQ FOCEXEC *
(FOC263) EXTERNAL FUNCTION OR LOAD MODULE NOT FOUND: MOD
(FOC009) INCOMPLETE REQUEST STATEMENT


WebFOCUS 7.7
Unix
All Formats : Excel,PDF,HTML,Dlimiter,.txt
 
Posts: 4 | Registered: May 30, 2017Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
LOAD MODULE NOT FOUND: MOD

That's because you are on WF 7.7. Check out DMOD, FMOD & IMOD functions.

Glad the dynamic formatting worked for you.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5601 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Field format from .000 or .00 or .0 to display as 0

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