31.3 Create a Formula Column That returns the Spelled-Out Cash Amounts

The steps in this section will show you how to create a formula column that is based on a function. The function you create will return verbal or word value for the numerical value of the check. You will then create a formula column that will spell out the numerical value in the designated currency. In this case, we will use dollars and cents.

31.3.1 Create a PL/SQL function

In this section, you will create a function that simply returns the check amount in word format, such as "twenty-four sixty-five." The formula column you create in the next section will use the information retrieved by this function to spell out the cash amounts on your checks.

To create a PL/SQL function:

In the Object Navigator, under your report name, double-click Program Units.
In the New Program Unit dialog box, type Spell in the Name box.
Select Function, then click OK.

In the PL/SQL Editor, type the following code:

FUNCTION Spell (val number) RETURN CHAR IS
  sp varchar2(100);
BEGIN
  if val > 0 then
         return(initcap(to_char(to_date(val, 'SSSSS'), 'SSSSSSP')));
  else
         return('');
  end if;
END;

Note:

You can enter this code by copying and pasting it from the provided text file called spellcash_code.txt into the PL/SQL Editor.

Click Compile. If you see any errors, compare your code against the code shown in the image below:

Figure 31-3 PL/SQL Editor displaying the SPELL function

Description of "Figure 31-3 PL/SQL Editor displaying the SPELL function"
When your code successfully compiles, click Close.

Your new function now displays in the Object Navigator:

Figure 31-4 Object Navigator with SPELL PL/SQL function

Description of "Figure 31-4 Object Navigator with SPELL PL/SQL function"
Save your report.

31.3.2 Create a formula column in your data model

In this section, you will create a formula column that uses the information retrieved by the Spell function you created in Section 31.3.1, "Create a PL/SQL function". This formula column will use the verbal values of the check amounts and combine the words with the correct currency. For example, the "twenty-four sixty-five" returned by the Spell function will be turned into "twenty-four dollars and sixty-five cents".

To create a formula column:

In the Object Navigator, under your report name, double-click the view icon next to the Data Model node to display the Data Model view.

Your data model should look like this:

Figure 31-5 Data Model view

Description of "Figure 31-5 Data Model view"
Resize the G_ORDER_ID box by clicking at the top, then dragging the bottom center resize handle downwards. Your data model should now look something like this:

Figure 31-6 Resized Data Model view

Description of "Figure 31-6 Resized Data Model view"
Click the Formula Column tool in the tool palette.
Click in the G_ORDER_ID group, in the space you just created, to create a new formula column.
Double-click the new formula column object (CF_1) to display the Property Inspector, and set the following properties:
- Under General Information, set the Name property to SPELLED_AMOUNT.
- Under Column, set the Datatype property to CHARACTER, and the Width property to 100.
- Under Placeholder/Formula, click the PL/SQL Formula property field to display the PL/SQL Editor.

In the PL/SQL Editor, use the template to enter the following PL/SQL code:

function SPELLED_AMOUNTFormula return Char is
cents number;
c_str varchar2(80);
val number;
begin
  val := :order_total;
  cents := (val mod 1) * 100;
  if cents > 0 then --creates string for cents
  c_str := ' and ' || TO_CHAR(cents) || '/100 Dollars******';
   else
  c_str := ' Dollars******';
  end if;
  if val < 1000 and val > 1 then
  return (initcap(spell(floor(val))) || c_str);
  elsif val > 1000 then
   return(initcap(spell(floor(val/1000))) || ' Thousand ' ||
   spell(floor(val mod 1000)) || c_str);
  else
   return('Zero'||c_str);
  end if;
end;

Note:

You can enter this code by copying and pasting it from the provided text file called spellcash_code.txt into the PL/SQL Editor.

Click Compile. If you see any errors, compare your code against the code we have provided.
When your code successfully compiles, click Close.

Your new formula column, called SPELLED_AMOUNT, now displays in the data model.

Figure 31-7 Data Model with SPELLED_AMOUNT formula column

Description of "Figure 31-7 Data Model with SPELLED_AMOUNT formula column"
Save your report.