31 Building a Check Printing Report with Spelled-Out Cash Amounts

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:

1. In the Object Navigator, under your report name, double-click Program Units.

2. In the New Program Unit dialog box, type Spell in the Name box.

3. Select Function, then click OK.

4. 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.

   
   

5. 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"
   
   

6. 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"
   
   

7. 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:

1. 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"
   
   

2. 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"
   
   

3. Click the Formula Column tool in the tool palette.

4. Click in the G_ORDER_ID group, in the space you just created, to create a new formula column.

5. 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.

6. 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.

   
   

7. Click Compile. If you see any errors, compare your code against the code we have provided.

8. 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"
   
   

9. Save your report.

31.5.1 Rearrange the layout objects

Before you can insert the check image, you must first rearrange the layout objects.

To rearrange the layout objects:

1. Click the Paper Layout button in the toolbar to display the Paper Layout view.

   The layout currently looks like this:

   Figure 31-10 Paper Layout view of your report

   
   Description of "Figure 31-10 Paper Layout view of your report"
   
   

2. Click the Edit Margin button in the toolbar.

3. In the Paper Layout view, click the margin frame (the heavy black line) to select it.

4. At the top of the frame, click the center black resizing square and drag it up, so that the margin is 0.25 inches (0.5 centimeters).

   
   

   Tip: When you click the center square, notice that a dotted guideline displays while your mouse button is depressed. You can use these guidelines to help place objects exactly where you want them in the Paper Layout view.

   
   

   When you are done, it should look something like this:

   Figure 31-11 Paper Layout view with resized margin

   
   Description of "Figure 31-11 Paper Layout view with resized margin"
   
   

5. Save your report.

31.5.2 Import the blank check image

In this section, you will import an image of a blank check. You can use any check you like. For this example, we have provided an image called blankcheck.jpg in the spellcash example directory.

To import the blank check image:

1. While the frame is still selected, choose Insert > Image.

2. In the Import Image dialog box, make sure that File is selected.

3. In the text box next to File, type or browse for the location of the image, blankcheck.jpg, then click OK to display the blank check image in the Paper Layout view.

4. Click the Edit Margin button in the toolbar to return to edit mode. Notice that the image no longer displays.

31.5.3 Set up the check printing fields

The steps in this section will show you how to use the various tools in the Paper Layout view to modify the look and feel of your check report. Here, you will learn how to add and modify layout objects and fields according to how you want them to display on the resulting checks. You will create copies of certain fields that you will then use in Section 31.6, "Create a check stub with payment information and order details" to create the check stub.

To set up the check printing fields:

1. In the Paper Layout view, click the Order No. boilerplate text.

2. Click the Select Parent Frame button in the toolbar.

   The repeating frame called R_G_ORDER_ID should now be selected in the Paper Layout view. You can also look in the Object Navigator, under Paper Layout, to make sure R_G_ORDER_ID is selected.

3. While R_G_ORDER_ID is selected, click and drag the bottom center resize handle to 8.75 inches (22 centimeters), or the bottom of the page.

   
   

   Tip: When you click and drag a resize handle in the Paper Layout view, guidelines display along the ruler to help you place your objects. In this case, drag the bottom center resize handle down the page until the horizontal guideline reaches 8.75 inches (22 centimeters).

   
   

4. Click the Flex Off button in the toolbar to set Flex mode off.

5. In the Paper Layout view, click F_ORDER_TOTAL, then press CTRL+C on your keyboard.

6. Click in the area below your layout objects and press CTRL+V.

   You should see a new field called F_ORDER_TOTAL1. If this field displays on top of the other layout objects, click and drag it down below the other layout objects so that your Paper Layout view now looks something like this:

   Figure 31-12 Partial Paper Layout view with F_ORDER_TOTAL1

   
   Description of "Figure 31-12 Partial Paper Layout view with F_ORDER_TOTAL1"
   
   

7. Copy the F_ORDER_DATE field and paste it below the other fields, so that your layout now looks like this:

   Figure 31-13 Partial Paper Layout view with F_ORDER_DATE1

   
   Description of "Figure 31-13 Partial Paper Layout view with F_ORDER_DATE1"
   
   
   
   

   Note: When you copy and paste a field, Reports Builder maintains the size of the field. So, when you copy and paste F_ORDER_DATE, you may not see the full name of the field. While the field is selected, you can click and drag the right border of the field to the right so that you can see the full name of the field, F_ORDER_DATE1.

   
   

8. Copy F_CUSTOMER_NAME and paste the field below the other layout objects, so that your layout looks like this:

   Figure 31-14 Partial Paper Layout view with F_CUSTOMER_NAME1

   
   Description of "Figure 31-14 Partial Paper Layout view with F_CUSTOMER_NAME1"
   
   

9. Make two copies of F_CHECK_NO by pressing CTRL+C on your keyboard once, then CTRL+V twice.

   
   

   Note: Since this field is small, it may be difficult to find. You can use the Object Navigator to find and select the field. When you click a field in the Object Navigator, under Paper Layout, the corresponding field is also selected in the Paper Layout view.

   
   

10. Position these two new fields below the other layout objects, so that your layout looks like this:

    Figure 31-15 Partial Paper Layout view with F_CHECK_NO1 and F_CHECK_NO2

    
    Description of "Figure 31-15 Partial Paper Layout view with F_CHECK_NO1 and F_CHECK_NO2"
    
    
    
    

    Note: We have expanded the size of the two new fields (F_CHECK_NO1 and F_CHECK_NO2) to make them easier to see, but you do not need to resize these fields.

    
    

11. Click the Field tool in the tool palette.

12. Draw a field below the other layout objects about 5 inches (12.5 centimeters) long, so that your layout now looks like this:

    Figure 31-16 Partial Paper Layout view with F_1 field

    
    Description of "Figure 31-16 Partial Paper Layout view with F_1 field"
    
    

13. While the new field, F_1, is still selected, click the Fill Color tool in the tool palette, and choose No Fill.

14. Click the Line Color tool in the tool palette, and choose No Line.

15. Double-click the field F_1 display the Property Inspector.

16. Set the Name property to F_SPELLED_AMOUNT.

17. Under Field, set the Source property to SPELLED_AMOUNT (the formula column you created in Section 31.3.2, "Create a formula column in your data model").

18. Save your report.

31.5.4 Rearrange the new fields according to the blank check image

The steps in this section will show you how to use the Paper Design view to rearrange the fields you just created. You will use the blank check image we have provided as a guide.

1. Click the Paper Design button in the toolbar to display your report in the Paper Design view.

   
   

   Note: When you click the Paper Design button while you are in another view (that is, Paper Layout view or Data Model view), Reports Builder runs your query and your layout. If you receive any error messages when you click the Paper Design button, go back to your original view to verify your changes. You can always compare your report against the sample report we have provided, called spellcash.rdf.

   
   

   You will see your layout objects, as well as the blank check image:

   Figure 31-17 Partial Paper Design view of the check report

   
   Description of "Figure 31-17 Partial Paper Design view of the check report"
   
   
   
   

   Note: You will see that the preview does not quite look like a check report. The steps in this section will show you how to move your fields so that your report looks like a proper check.

   
   

2. In the Paper Design view, position the F_ORDER_TOTAL field in the "Amount" box in the blank check image.

   
   

   Note: If you closed Reports Builder for any reason and are returning to building this report, make sure you click the Flex Off button in the toolbar so that you can move the fields around in the Paper Design view.

   
   

3. Click the Align Right button in the toolbar, so that this portion of the check looks like the following:

   Figure 31-18 Amount area on blank check

   
   Description of "Figure 31-18 Amount area on blank check"
   
   

4. Position the F_CHECK_NO field in the "Check No." box on the blank check, and center-align the text.

5. Position the F_ORDER_DATE field in the "Date" box on the blank check, and center-align the text.

6. Position the F_CHECK_NO2 field in the upper right-hand corner of the check image, and right-align the text.

7. Position the F_SPELLED_AMOUNT field next to the "Pay Exactly:" text on the blank check image.

8. Position the F_CUSTOMER_NAME field next to the "Pay To The Order Of:" text on the blank check image.

   The check should now look like this:

   Figure 31-19 Check image with fields positioned

   
   Description of "Figure 31-19 Check image with fields positioned"
   
   

9. Save your report.

31.5.5 Modify the look and feel of the check

The steps in this section will show you how to change the font, alignment, and formatting of the data on your check.

To modify the look and feel of the check:

1. In the Paper Design view, choose Edit > Select All (or press CTRL+A).

2. Choose Format > Font.

3. In the Font dialog box, choose Arial font, Regular style, Size 10, then click OK.

4. In the Paper Design view, click anywhere to deselect all the objects.

   
   

   Tip: If you have trouble deselecting the objects, go to the Object Navigator and click any item. The objects that were selected in the Object Navigator should display as deselected.

   
   

5. In the Paper Design view, click F_CHECK_NO2 (the check number in the upper right-hand corner of the check) and change the font to Arial, Bold, 12 point.

6. Click the F_ORDER_TOTAL field (the number in the Amount box on the check).

7. Click the Currency button in the toolbar, then click the Commas button.

8. While F_ORDER_TOTAL is still selected, click the Add Decimal Place button in the toolbar twice, so that the amount now displays like this: $46,257.00.

9. Choose Tools > Property Inspector to display the Property Inspector for F_ORDER_TOTAL.

10. Under Field, next to the Format Mask property, add to the beginning of the existing format mask "****", then close the Property Inspector.

    The amount should now display like this: ****$46,257.00.

11. Select the F_CUSTOMER_NAME and F_SPELLED_AMOUNT fields by Shift-clicking them, then change the font to Times New Roman, 12 point.

    Your report should now look something like this:

    Figure 31-20 Formatted Check

    
    Description of "Figure 31-20 Formatted Check"
    
    
    
    

    Note: You will notice that the other boilerplate text is still overlaying the image. We will rearrange this text later in the chapter.

    
    

12. Save your report.

31.6.1 Create a check stub in the Paper Design view

The steps in this section will show you how to use the tools in the Paper Design view to create a check stub for your check report. You will rearrange the fields you created in the previous section so that every time you run the report, a corresponding check stub will display for every check.

You will also create guidelines in the Paper Design view to help you align the check stub information with the data in the check report.

To create a check stub:

1. Click the Flex Off button in the toolbar.

2. Click in the left, vertical ruler and drag your mouse to the right, so that the line is flush with the text "Pay Exactly."

3. When you release your mouse button, you will notice that a guideline displays, like the one in the following image:

   Figure 31-21 Partial Paper Design view of the check report with horizontal guideline

   
   Description of "Figure 31-21 Partial Paper Design view of the check report with horizontal guideline"
   
   
   
   

   Note: The guideline is a broken black line spanning across this image, to the right of "Pay Exactly."

   
   

4. Click the Order No. boilerplate object (located in the upper left-hand corner of the check) and position it along the new guideline, and below the check image at 3.25 inches (8.5 centimeters), like this:

   Figure 31-22 Partial Paper Design view of the check with Order No. boilerplate text

   
   Description of "Figure 31-22 Partial Paper Design view of the check with Order No. boilerplate text"
   
   

5. Create another vertical guideline that is aligned next to the first one.

6. Click the F_ORDER_ID field (in the above image, it is the field with "2354") and position it next to the Order No. field, like this:

   Figure 31-23 Order No. and F_ORDER_ID fields in the Paper Design view

   
   Description of "Figure 31-23 Order No. and F_ORDER_ID fields in the Paper Design view"
   
   
   
   

   Note: You can always select your fields in the Object Navigator, under Paper Layout > Main Section > Body, and these fields will appear selected in the Paper Design view.

   
   

7. Now, click the Order Date boilerplate text and position it directly below "Order No.", like this:

   Figure 31-24 Order Date field in the Paper Design view

   
   Description of "Figure 31-24 Order Date field in the Paper Design view"
   
   

8. Click F_ORDER_DATE1 (the copy you made of the F_ORDER_DATE field), which you can locate using the Object Navigator, and position the field next to the Order Date boilerplate text, like this:

   Figure 31-25 F_ORDER_DATE1 field next to Order Date text

   
   Description of "Figure 31-25 F_ORDER_DATE1 field next to Order Date text"
   
   

9. Position the Order Total boilerplate text directly below the Order Date boilerplate text.

10. Position the F_ORDER_TOTAL1 field next to the Order Total boilerplate text.

11. Position the Check No. boilerplate text directly below the Order Total boilerplate text.

12. Position F_CHECK_NO1 next to the Check No. boilerplate text.

    The check stub should now look like this:

    Figure 31-26 Partial check stub in Paper Design view

    
    Description of "Figure 31-26 Partial check stub in Paper Design view"
    
    

13. Create another pair of vertical guidelines that are aligned with the box containing the date, amount, and check number. The guidelines should appear here:

    Figure 31-27 Second vertical guideline in the Paper Design view

    
    Description of "Figure 31-27 Second vertical guideline in the Paper Design view"
    
    
    
    

    Note: To create this guideline, click in the left-hand ruler, then drag your mouse until the line is aligned to the left of box containing Date, Check No., and Amount.

    
    

14. Click the Customer boilerplate text, and position it so that it is vertically aligned with the second guideline, and horizontally aligned with the Order No. text (at 3.25 inches (8.5 centimeters)), like this:

    Figure 31-28 Customer field in the Paper Design view

    
    Description of "Figure 31-28 Customer field in the Paper Design view"
    
    

15. Position the F_CUSTOMER_NAME1 field next to the Customer boilerplate text so that these layout objects look like this:

    Figure 31-29 Customer and F_CUSTOMER_NAME1 fields

    
    Description of "Figure 31-29 Customer and F_CUSTOMER_NAME1 fields"
    
    
    
    

    Tip: If you have difficulty fitting the F_CUSTOMER_NAME1 field into the provided space, you may need to resize the field. To do so, select the field, then drag the right middle resize handle to the left, making sure that there is still enough space for the customer name (Harrison Sutherland).

    
    

16. Position the F_C_STREET_ADDRESS field directly below F_CUSTOMER_NAME1.

17. Position F_C_CITY directly below F_C_STREET_ADDRESS.

18. Position the F_C_STATE_PROVINCE field next to F_C_CITY.

19. Position the F_C_POSTAL_CODE field next to F_C_STATE_PROVINCE.

20. Position the F_C_COUNTRY_ID field next to F_C_POSTAL_CODE.

21. Position the F_CUSTOMER_ID field directly below the F_C_CITY field.

22. Position the Customer No. boilerplate text (B_CUSTOMER_ID) directly to the left of the F_CUSTOMER_ID field, so that this portion of the check stub should now look like this:

    Figure 31-30 Partial check stub in the Paper Design view

    
    Description of "Figure 31-30 Partial check stub in the Paper Design view"
    
    

23. Now, let us format the information you have just added. Select the following boilerplate text objects in the check stub:

    • Order No.

    • Order Date

    • Order Total

    • Check No.

    • Customer

    • Customer No.

24. Change the font to Bold, then right-align the text.

25. Click F_ORDER_TOTAL1, then click the Currency button and the Commas button in the toolbar.

26. Click the Add Decimals button in the toolbar twice.

    Your check stub should now look like this:

    Figure 31-31 Formatted check stub

    
    Description of "Figure 31-31 Formatted check stub"
    
    

27. Save your report.

31.6.2 Add order details to the check stub

In the previous section, you created a check stub that displays the payment information, that is a copy of the information on the check itself. The steps in this section will show you how to add the details of the order to the check stub by creating a simple tabular report. You will use the Report Block Wizard to create the simple tabular report, then format the data in the Paper Design view.

To add order details:

1. In the Paper Layout view, click the Report Block tool in the tool palette.

2. Drag a rectangular area for the new layout to display the Report Block Wizard.

3. In the Report Block Wizard, on the Style page, select Tabular, then click Next.

4. On the Groups page, click G_LINE_ITEM_ID and click Down in the Available Groups list to specify the Print Direction and move this group to the Displayed Groups list, then click Next.

5. On the Fields page, click each of the following fields in the Available Fields list, then click the right arrow (>) to move them to the Displayed Fields list, then click Next:

   • LINE_ITEM_ID

   • PRODUCT_ID

   • PRODUCT_NAME

   • UNIT_PRICE

   • QUANTITY

6. On the Labels page, change the labels and field widths as follows, then click Next:

   Fields	Labels	Width
   LINE_ITEM_ID	Line	2
   PRODUCT_ID	Product ID	4
   PRODUCT_NAME	Product Name	40
   UNIT_PRICE	Unit Price	4
   QUANTITY	Quantity	4

   
   

7. On the Template page, select No template, then click Finish to display your report layout in the Paper Layout view, with the new fields and layout objects displaying below the original layout objects.

8. In the Paper Layout view, make sure the new fields and layout objects are located within the R_G_ORDER_ID repeating frame, and that all the layout objects fit on a single page (within 8.75 inches (22 centimeters)).

9. Click the Paper Design button in the toolbar to run and display your report in the Paper Design view. It should look something like this:

   Figure 31-32 Check printing report with non-formatted order details

   
   Description of "Figure 31-32 Check printing report with non-formatted order details"
   
   

   Since the new report block is not formatted, let us format the data to look like the rest of the report.

10. Shift-click the following new labels, then click the Bold button in the toolbar and change the font to Arial, 10 point:

    • Line

    • Product ID

    • Product Name

    • Unit Price

    • Quantity

11. Click the data under Line (F_LINE_ITEM_ID), then click the Align Center button in the toolbar.

12. Click the data under Unit Price (F_UNIT_PRICE), then click the Align Right button in the toolbar.

13. While the data is still selected, click the Currency button, Commas button, then click the Add Decimal button twice in the toolbar.

14. Click the right resize handle and align the right edge of the data field with the Unit Price label.

15. Click the data under Quantity (F_QUANTITY), then click the Align Right button in the toolbar, then resize the field so that the right edge of the data field is aligned with the Quantity label.

16. Save your report.

    Your report should now look like this:

    Figure 31-33 Final check report with spelled-out cash amounts and stub

    
    Description of "Figure 31-33 Final check report with spelled-out cash amounts and stub"
    
    
    
    

    Note: You can also compare your results with the example report we have provided, called spellcash.rdf.