How to convert Amount in Words in cognos report studio

[PallaviPoojari]

Hi,

I want to convert Amount field in Word.

For Eg.

1245.00 --- One Thousand Two Hundred and Forty Five Only

Type of Amount field is decimal.

Cognos Version - 10.2.1
Database - DB2

Thanks in advance.

Regards,
Pallavi

[BigChris]

I don't think there's anything in native Cognos, but you might be able to do something with javascript. A quick look on the web revealed this:
http://javascript.about.com/library/bltoword.htm

I'm afraid I'm about as conversant in javascript as I am in quantum mechanics so I can't offer any further help...but others might be able to.

[cognos810]

Hello Pallavi,
As BigChris suggested, you can go the Javascript route but that will be limited to HTML output only.
Ideally, your DB2 admins should be able to provide you with a function/stored procedure to achieve the same.

Thanks,
Cognos810

[PallaviPoojari]

Hi

Thank you for your response.

They have function in DB2 for converting amt field in word But the field which i need to convert in word that i am calculating in report studio that's why i need this solution.

[Lynn]

You can convert numbers to words using a big ol' ugly SQL expression that is chock full of case statements. I had a requirement to do this and then the Muppet (aka MFGF) improved it so there are only Cognos functions used which should make it database vendor independent.

The attached shows the expression and includes comments to help you understand what it is doing and also how to modify it to use your query item.

HINT: YourQueryItemHere should stand out as the likely place where you want to reference (you guessed it) your query item.

[barrysaab]

Great stuff,Thanks.

[Raghuvir]

You can convert numbers to words using a big ol' ugly SQL expression that is chock full of case statements. I had a requirement to do this and then the Muppet (aka MFGF) improved it so there are only Cognos functions used which should make it database vendor independent.

The attached shows the expression and includes comments to help you understand what it is doing and also how to modify it to use your query item.

HINT: YourQueryItemHere should stand out as the likely place where you want to reference (you guessed it) your query item.

Thanks Lynn for sharing such greeat information. MFGF is the best :)

i used the attached code and got the below output

494,961,122.07 Four Hundred Ninety Four Million Nine Hundred Sixty One Thousand One Hundred Twenty Two AND 7/100

this wont work for decimal values ?

Regards

[BigChris]

What would you want to see for the decimal part?

The part of the code that deals with the decimals is the last part...you might be able to alter it to what you need.

[MFGF]

What would you want to see for the decimal part?

The part of the code that deals with the decimals is the last part...you might be able to alter it to what you need.

Did you look at the expression? The very last part is:

+ '/100'

Couldn't you just change it to + ' cents' ???

Lynn has already done all the heavy lifting here. Her expression has saved you potentially days of work in figuring out how to get the result you require. You're not really going to ask her how to make a trivial change like '/100' to ' cents', are you?? :)

Edit. I see - you want the 7 as 'seven'? In that case, just extend the expression using Lynn's approach for the section before. You're only going to have to accommodate numbers 0 to 99 so it will be even easier.

MF.

[bnlmeyer]

Greetings! I just had this same requirement emerge today - but alas I can't download the very old txt file 'CognosNumbersToWords MF.txt' anymore to kick that report studio code around . . .  anybody have any thoughts? Or the file :)

[Lynn]

Greetings! I just had this same requirement emerge today - but alas I can't download the very old txt file 'CognosNumbersToWords MF.txt' anymore to kick that report studio code around . . .  anybody have any thoughts? Or the file :)

Ah, you are in luck! I have been out of the Cognos world for a few years now but I asked the Muppet and he still has the file. He will post it tomorrow.

[MFGF]

Ah, you are in luck! I have been out of the Cognos world for a few years now but I asked the Muppet and he still has the file. He will post it tomorrow.

The forum doesn't allow attachments these days, so I have emailed you a copy to the address you used when you signed up as a Cognoise user.

Cheers!

MF.

[bnlmeyer]

Ah, I am truly very lucky - I received the file - thank you both for responding and have a great rest of your week!