How to convert decimal value to roundoff numeric value.

[Former Member]

Hai ,

I have an reqirement as to convert decimal value to numeric value and to display the numeric value in words.

eg., i have Amount = 1225.75

i need to get output as 1226 and to display as

One thousand two hundred and twenty six.

I tried finding solution for this in forums, i got answer for converting in words but for conversion of numeric i dint get solution.

Please help me for this requirement.

[Former Member]

Hi Prabuabap,

CASE 2

If you need to Print a INDIAN STYLE Amount in Words.

Try this,

->> Create 1 UDF in Header on Require Documents (ex. Marketing Documents).

->> Create 3 Function in MS SQL Server Management.

->> Create 1 FMS in Query Generator and save as Query Manager then Assign to UDF for Amount in Words.

for example:

Create UDF in Header on Marketing Documents.

->> Choose Tools on Top menu.

->> User - Defined Fields. -> User Manage Fields.

->> Open the User Manage Fields Window.

->> Marketing Documents. -> Title.

->> Select Title and Click Add button in Bottom on User Manage Fields Window.

->> Create Amount in Words UDF(Code, Description and Type - Character) and Add the UDF.

Create Function in MS SQL Server Management.

Check this Link, (have 3 Functions in Link).

http://techcreeze.blogspot.com/2008/11/convert-amount-into-words-according-to_15.html

1st Function - to Convert one Digit Number to words

2nd Function - to convert 2 digit number to words.

3rd Function - to convert amt in numbers to words.

->> Open the MS SQL Server Management Window.

->> Choose your Company database and Create NEW Query.

->> Create 3 Function Queries one by one.

->> Create 3 NEW Query Tab and 1st one put the 1st Function then Run the Function. and

2nd New Query tab put the 2nd Function then Run the Function.

3rd New Query tab put the 3rd Function then Run the Function.

Create FMS in Query Generator and Save as Query Manager.

->> Administration.

->> Reports. -> Query Generator.

->> Open the Query Generator and put the below FMS query.

for example : Purchase Order Doc. Total ROUNDING(IN WORDS).

declare @Doc_total numeric (19,6)
set @Doc_total=$[OPOR.DocTotal]
select  dbo.fNumToWords(Round(@Doc_total,0))

Else

Try this Simple FMS

SELECT dbo.fNumToWords(Round($[OPOR.DocTotal],0))

->> Assign the FMS in UDF on Purchase Order.

->> Auto Refresh of Document Total.

Ex.

1. Goto the UDF and Clcik ShiftAltF2.

2. Select the SEARCH BY SAVED QUERY.

3. Assign the FMS Query.

4. Select the AUTO REFRESH WHEN FIELD CHANGES.

5. Select Document Total.

6. Check the Display Saved Values.

IF you want extra some other details check this thread.

CLOSE THE THREAD, IF ISSUE SOLVED

Regards,

Madhan.

[Former Member]

thank you very much

[Former Member]

Hi Prabuabap,

CASE 1

for example.,

you have DocTotal(Amount) = 1225.75

If you need to get output as 1226 and to display as

One thousand two hundred and twenty six.

Try this

->> Open the Require PLD.

->> Create 1 Formula Field in PLD and assign the formula for Rounding Amount.

Example:

DocTotal - Field ID ->> Field_XXX

(if you don't need to display this field value in print,

put the Tick mark in Visible in General Tab on properties Window)

Formula:

Formula Field ID ->> Field_YYY

Round(Field_XXX,0)

->> put the Tick Mark of Sum in Words in Format Tab on Properties Window.

Save it and run the Print preview.

Regards,

Madhan.

[Former Member]

Hi!

Where do u want this in Query or PLD ?

in PLD use Round(field no,0) syntax.

in Query use Round(fieldname,0) syntax