Oracle® Fusion Middleware Oracle Reports User's Guide to Building Reports 11g Release 1 (11.1.1) B32122-01 |
|
Previous |
Next |
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.
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 calledspellcash_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
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
Save your report.
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:
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:
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 calledspellcash_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
Save your report.