Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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: 9 | Registered: May 30, 2017Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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: 9 | Registered: May 30, 2017Report 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.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Master
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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report 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: 9 | Registered: May 30, 2017Report 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: 9 | Registered: May 30, 2017Report 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.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders