Focal Point
[SOLVED] Field format from .000 or .00 or .0 to display as 0

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4147084196

August 16, 2019, 07:23 AM
iampg
[SOLVED] Field format from .000 or .00 or .0 to display as 0
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
August 16, 2019, 07:43 AM
MartinY
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
August 16, 2019, 08:12 AM
iampg
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
August 16, 2019, 08:45 AM
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



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 
August 16, 2019, 12:32 PM
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.


Hallway
WF(Prod):8202M1
WF(Test):8202M4
OS/Platform:Win 10
Outputs:All
August 18, 2019, 06:45 AM
iampg
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
August 18, 2019, 07:00 AM
iampg
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
August 19, 2019, 03:08 AM
Tony A
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