33.4 Create Summary and Formula Columns

To provide the necessary information for the invoice, you need to create summary and formula columns for subtotal amounts, a total amount, and due dates.

To create the invoice summary and formula columns:

  1. Click the Summary Column tool in the tool palette, then click in an open area of the Data Model view to create a summary column.

  2. Double-click the new summary column object to display the Property Inspector, and set the following properties:

    • Under General Information, set the Name property to CS_SUB_TOTAL.

    • Under Summary, set the Source property to O_QUANTITY_O_UNIT_PRICE, and set the Reset At property to Page.

    This summary is used for subtotal amounts on each page of the invoice.

  3. Click the G_ORDER group, and drag the bottom edge handle down to create an empty space at the bottom of the group.

  4. Again, click the Summary Column tool in the tool palette, then click in the empty space in the G_ORDER group to create a second summary column.

  5. Double-click the new summary column object to display the Property Inspector, and set the following properties:

    • Under General Information, set the Name property to CS_TOTAL.

    • Under Summary, set the Source property to O_QUANTITY_O_UNIT_PRICE and set the Reset At property to G_ORDER.

    This summary is used for a total amount of each invoice.

  6. Click the Formula Column tool in the tool palette, then click in an open area of the Data Model view to create a formula column.

  7. Double-click the new formula column object to display the Property Inspector, and set the following properties:

    • Under General Information, set the Name property to CF_DUE_DATE.

    • Under Column, set the Datatype property to Date.

    • Under Placeholder/Formula, click the PL/SQL Formula property field to display the PL/SQL Editor.

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

    function CF_DUE_DATEFormula return Date is 
today date; 
begin 
 select sysdate into today from dual; 
  return today + 30; 
end;

    This formula is used to calculate the due date, which is 30 days after today.

  9. Click Compile.

  10. Click Close.

    Your data model should now look something like this:

    Figure 33-3 Data Model view with data link, summary, and formula columns

    Description of Figure 33-3 follows
    Description of "Figure 33-3 Data Model view with data link, summary, and formula columns"

  11. Save the report as invoice_your_initials.rdf.