Display numbers into words through Business Object Reports

It may happen we need to display the numbers (dollars amounts / values) in words, for instance 1= one, 2000 = two thousand, 1000000 = One Million, etc.

The objective of this post is to provide a way to translate / convert the dollar numbers / values into English words.

You can get the numbers converted into words using Julian Date with some limitation through normal SQL functions. Julian day is the continuous count of days since the beginning of the Julian Period used primarily by astronomers and Julian day number 0 assigned to the day starting at noon on January 1, 4713 BC. For more details on Julian Day, please visit Wiki

For example – SQL

SELECT TO_CHAR (TO_DATE (‘1234’, ‘j’), ‘jsp’) Num2word FROM DUAL;

BO Object:

Number2word1

Result would be “one thousand two hundred thirty-four

If you look into the above Query / object, 1234 is the number which we want to convert into words, ‘j’ or ‘J’ is the Julian value stored in the database (January 1, 4713 BC).

‘JSP’ (JSP=NOW) as a combined element is the Julian numeric value spelled out in words and return all caps letters.
‘Jsp’ (JSP=NOW) as a combined element is the Julian numeric value spelled out in words and return first letter as caps .
‘jsp’ (JSP=NOW) as a combined element is the Julian numeric value spelled out in words and return all are small letters.

In the above example, 1234 starts from 0 (Julian day number 0 assigned to the day starting at noon on January 1, 4713 BC ) and it will returns “one thousand two hundred thirty-four”.

‘DDSP’ – the numeric date of the month spelled in words and in all caps letters. ‘Ddsp’ – returns first letter as caps and ‘ddsp’ – returns all are small letters.

SELECT TO_CHAR (TO_DATE (‘1’, ‘j’), ‘Ddsp’) Num2word FROM DUAL;

This query will return as “Ten”. How it is been working? Julian Date is 1/1/4713 and when you do TO_DATE(‘1’,’j’), it will take from 1/1/4712 and go backward of that date. ‘Ddsp’ – would return the value from 1 to 31 max based on the calendar dates. In the below table, would show how ‘Ddsp’ returns result as numeric date of the month and spelled in words.

Number2word2

Note: Julian date must be between 1 and 5373484

There is a limitation while using Julian dates ,It ranges from 1 to 5373484. That’s why if you enter the values more than 5373484, it will throw you an error as shown below:
ORA-01854: julian date must be between 1 and 5373484

To overcome this issue we can create a DB function (Number2Word) with little workaround we can get the desired result.

CREATE OR REPLACE FUNCTION number2word (Val_number IN NUMBER)
RETURN VARCHAR2
AS
TYPE WordArray IS TABLE OF VARCHAR2 (255);

W_str WordArray
:= WordArray (”,
‘ thousand ‘,
‘ million ‘,
‘ billion ‘,
‘ trillion ‘,
‘ quadrillion ‘,
‘ quintillion ‘,
‘ sextillion ‘,
‘ septillion ‘,
‘ octillion ‘,
‘ nonillion ‘,
‘ decillion ‘,
‘ undecillion ‘,
‘ duodecillion ‘);

W_num VARCHAR2 (50) DEFAULT TRUNC (Val_number);
W_return VARCHAR2 (4000);
BEGIN
FOR i IN 1 .. W_str.COUNT
LOOP
EXIT WHEN W_num IS NULL;

IF (SUBSTR (W_num, LENGTH (W_num) – 2, 3) <> 0)
THEN
W_return :=
TO_CHAR (TO_DATE (SUBSTR (W_num, LENGTH (W_num) – 2, 3), ‘J’),
‘Jsp’)
|| W_str (i)
|| W_return;
END IF;

W_num := SUBSTR (W_num, 1, LENGTH (W_num) – 3);
END LOOP;

RETURN W_return;
END;

After you create the above function in DB you can get the any numbers can be converted into words.

SELECT number2word (987654321012345) FROM DUAL;

Nine Hundred Eighty-Seven trillion Six Hundred Fifty-Four billion Three Hundred Twenty-One million Twelve thousand Three Hundred Forty-Five

You can use this Number2Word function to create BO universe object and then convert any numbers in words.

Number2word3

Below is the report data.

Number2word4

In above report, we can see only the whole numbers are converted into words and decimal numbers are omitted in words and also most of the Business reports would display the sales revenue or amounts in Dollars with cents (decimal value).

Create a universe object as below.

Number2word5

Now the report would display the sale revenues in dollars and cents.

Number2word6

Hope this would help you in your projects to display the values with more clarity.

Advertisements

One thought on “Display numbers into words through Business Object Reports

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s