37
Building a PL/SQL Report

Figure 37-1 PL/SQL report output

Text description of the illustration plsql_fin.gif

In this chapter, you will learn how to use an external PL/SQL library and PL/SQL within a report to modify formatting and calculate the total compensation for each employee. In the figure above, notice the spacing between records (e.g., between the record for Alexander Khoo and the record for Alexis Bull). This space is not due to a break; it is the result of using a PL/SQL procedure in a format trigger.

Concepts

• There are a variety of ways to incorporate PL/SQL into your reports. You've already create formula columns that used simple PL/SQL expressions to compute their values, and format triggers that used PL/SQL to conditionally determine the formatting of mailing labels. Here, you will create external libraries and local functions and procedures.

• External PL/SQL libraries are modules that contain named PL/SQL functions and procedures. They may be stored either in the database or in a file, and can be referenced from not only any report, but from other Oracle products. External libraries eliminate the need to re-enter commonly-used PL/SQL constructs, whether in reports, forms, or graphs. This, in turn, eliminates the problem of maintaining several versions of the same PL/SQL code.

• Local PL/SQL consists of named PL/SQL functions and procedures that are saved in a report definition. Local PL/SQL may be referenced only by objects within the report (e.g., group filters, formula columns, format triggers, etc.). However, the usefulness of storing PL/SQL in a single location still applies.

Data Relationships

• This report uses one query. You will add a function stored in an external library, a report-level function, two formula columns, and a parameter governing the number of records to display before inserting a space.

Layout

• This report uses the tabular layout style, with minor modifications.

Example Scenario

This chapter will show you how to manually create a query in the Data Model view, then modify the layout of the report in the Paper Layout view. You will create formula columns, a summary column, and a format trigger that uses a summary column and a user parameter.

To see a sample PL/SQL report, open the examples folder named plsql, then open the Oracle Reports example named plsql.rdf. For details on how to open it, see "Accessing the example reports" in the Preface.

Table 37-1 Features demonstrated in this example

Feature	Location
Create a new PL/SQL library that you will use in this report.	Section 37.2, "Create a new PL/SQL library"
Manually create a query and add formula columns to your report that calculate bonuses and total compensation based on an external PL/SQL library.	Section 37.3, "Create the report definition"
Use the Report Block Wizard to create a report layout.	Section 37.4, "Create the report layout using the Report Block Wizard"
Create a user parameter, summary column, the Paper Layout view, and a format trigger to add vertical space between a user-determined number of records.	Section 37.5, "Add vertical space between records"

37.1 Prerequisites for this example

To build the example in this chapter, you must have access to the Human Resources portion of the sample schema, which is provided with the Oracle9i database. If you do not have access to this schema, contact your database administrator.

37.2 Create a new PL/SQL library

The steps in this section will show you how to create a new PL/SQL library, then create a function that will live in this library.

To create the library:

1. Launch Reports Builder (or, if already open, choose File > New > Report)

2. In the Welcome or New Report dialog box, select Build a new report manually, then click OK.

3. In the Object Navigator, choose File > New > PL/SQL Library.

   A new library displays in the Object Navigator below your report name, under the PL/SQL Libraries node.

4. If it is not already expanded, expand the node of the new library to show the two subnodes: Program Units and Attached Libraries.

5. Click the Program Units node, then choose Edit > Create.

6. In the New Program Unit dialog box, in the Name field, type BONUS_PAY.

7. Select Function, then click OK to display the PL/SQL Editor.

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

   FUNCTION BONUS_PAY(JOB_ID IN CHAR, SAL IN NUMBER, COMM_PCT IN NUMBER) RETURN 
   NUMBER IS
   BEGIN
     IF JOB_ID != 'SA_REP' THEN
       RETURN (SAL * 0.15);
     ELSE
       IF SAL * COMM_PCT >= 500 THEN
         RETURN ((SAL + SAL * COMM_PCT) * 0.15);
       ELSE
          RETURN ((SAL + SAL * COMM_PCT) * 0.10);
       END IF;
     END IF;
   END;
   

   Note: You can enter this code by copying and pasting it from the provided text file called plsql_code.txt.

9. Click Compile.

10. If there are compilation errors, match your code to the code we've provided (either in the example RDF file or in this chapter), then compile it again.

11. Once there are no compilation errors, click Close.

    Your new function displays in the Object Navigator.

12. Choose File > Save to save your new function.

13. In the Save Library dialog box, type bonus.pll, make sure File System is selected, then click OK.

14. In the Object Navigator, under the MODULE1 report you've created, click the Attached Libraries node. Be sure to select this node, and not the one under the PL/SQL Libraries node.

15. Choose Edit > Create.

16. In the Attach Library dialog box, in the Library field, type bonus.pll.

    Note: If you saved bonus.pll to another directory, you can click Browse to find it on your file system. Just make sure you've selected File System before browsing.

17. When the library name displays in the Library field, click OK to attach the library.

    Note: If you attach a library whose name also includes a path, Reports Builder will inform you that the path names are not portable, and will give you the option of deleting the path. If you choose to continue using a path specification, Reports Builder will only look in that specific location for the library. So, if you move the library, Reports Builder will not be able to find it. If you delete the path, Reports Builder will use a standard search path to locate the library if it is moved.

The objects in your Object Navigator should now look something like this:

Figure 37-2 Object Navigator

Text description of the illustration plsql_on.gif

18. Save your report as plsqlreport_<your initials>.rdf.

37.3 Create the report definition

The steps in this section will show you how to create the query and the formula columns that will define the report and call the code in the bonus.pll external PL/SQL library you created.

We recommend that you create the objects in the order described, as some of the formula columns depend on the functions, etc.

37.3.1 Create a query

The steps in this section will show you how to create the query that will retrieve the data necessary for this report.

To create the query:

1. In the Object Navigator, double-click the view icon next to the Data Model node to display the Data Model view.

2. In the Data Model view, choose Insert > Query to display the Data Wizard.

3. If the Welcome page displays, click Next.

4. On the Query page, leave the default query name, then click Next.

5. On the Data Source page, select SQL Query, then click Next.

6. On the Data page, in the Data Source definition field, enter the following SELECT statement:

   SELECT FIRST_NAME, LAST_NAME, JOB_ID, SALARY,COMMISSION_PCT
   FROM EMPLOYEES
   ORDER BY LAST_NAME
   

   Note: You can enter this query in any of the following ways: Copy and paste the code from the provided text file called plsql_code.txt into the Data Source definition field. Click Query Builder to build the query without entering any code manually. Type the code in the Data Source definition field.

7. Click Next.

   Note: If you are not already connected to a database, you will be prompted to connect to the database when you click Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 37.1, "Prerequisites for this example" describes the sample schema requirements for this example.

8. On the Groups page, click Next.

9. Click Finish to display your first query in the Data Model view. It should look something like this:

Figure 37-3 Data Model view of the PL/SQL report

Text description of the illustration plsql_dm.gif

10. Save your report.

37.3.2 Create a formula column that calculates bonuses

The steps in this section will show you how to create a formula column that will calculate the salary bonus for each employee using the PL/SQL function.

To create the BONUS formula column:

1. In the Data Model view, click group G_FIRST_NAME, then click the bottom resize handle and drag it down to make room in the data model for more columns. Here's an example of what it should look like now:

Figure 37-4 Data Model with expanded G_ENAME

Text description of the illustration plsql_dmx.gif

2. Click the Formula Column tool in the tool palette, then click in the G_FIRST_NAME group to create a formula column.

Figure 37-5 Data Model with unnamed formula column

Text description of the illustration plsql_dmfc.gif

3. Double-click the new formula column object (CF_1) to display the Property Inspector, and set properties:
   • Under General Information, set the Name property to BONUS.

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

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

   function BONUSFormula return Number is
   begin
     return BONUS_PAY(:JOB_ID, :SALARY, :COMMISSION_PCT);
   end;
   

   Note: You can enter this code by copying and pasting it from the provided text file called plsql_code.txt. This code is for the Bonus Formula Column.

5. Click Compile.

   Note: If there are compilation errors, compare your code closely against the code we've provided.

6. When there are no compilation errors, click Close to display the data model for your report in the Data Model view. It should look something like this:

Figure 37-6 Data Model with BONUS formula column

Text description of the illustration plsql_fcbon.gif

7. Save your report.

37.3.3 Create a report-level function that calculates total compensation

The steps in this section will show you how to write a function that returns the total compensation for each sales representative (the values of columns SALARY plus COMM plus BONUS), as well as other employees (SALARY plus BONUS).

1. In the Object Navigator, click the Program Units node, then choose Edit > Create.

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

3. Select Function, then click OK.

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

   FUNCTION FINAL_CALC RETURN NUMBER IS
   BEGIN
     IF :JOB_ID = 'SA_REP' THEN
     	RETURN (:BONUS + :SALARY + :COMMISSION_PCT * :SALARY);
     ELSE
     	RETURN (:BONUS + :SALARY);
     END IF;
   END;
   

   Note: You can enter this code by copying and pasting it from the provided text file called plsql_code.txt. This code is for Final Calc.

5. Click Compile.

6. When the code is compiled without errors, click Close.

   The new function, FINAL_CALC, now displays in the Object Navigator:

Figure 37-7 Object Navigator with FINAL_CALC function

Text description of the illustration plsql_onfunc.gif

7. Save your report.

37.3.4 Create a second formula column for total compensation

The steps in this section will show you how to create another formula column that will calculate the total compensation. The value calculated by the report-level function FINAL_CALC will be assigned to the column TOTAL_COMP. If you are not sure how to create a formula column, refer to Section 37.3.2, "Create a formula column that calculates bonuses".

To create the TOTAL_COMP formula column:

1. In the Data Model view, follow the steps above to create a second formula column below the BONUS formula column.

2. Double-click the new formula column object (CF_1) to display the Property Inspector, and set properties:
   • Under General Information, set the Name property to TOTAL_COMP.

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

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

   function TOTAL_COMPFormula return Number is
   begin
     return FINAL_CALC;
   end;
   

   Note: You can enter this code by copying and pasting it from the provided text file called plsql_code.txt. This code is for the Total Comp Formula Column.

4. Click Compile.

5. When the code is compiled without errors, click Close to display the data model for your report in the Data Model view. It should look something like this:

Figure 37-8 Data Model with BONUS and TOTAL_COMP formula columns

Text description of the illustration plsql_dmfc2.gif

6. Save your report.

37.4 Create the report layout using the Report Block Wizard

Now that you've created the necessary formula columns and functions, you can create the layout for your report.

To create the report layout:

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

2. In the Paper Layout view, choose Insert > Report Block.

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

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

5. On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list. The Displayed Fields list should look like this:

Figure 37-9 Displays Fields list

Text description of the illustration plsql_disfds.gif

Note: If the fields do not display in the correct order, simply click on the field name and drag the field to the correct position in the list.

6. Click Next.

7. On the Labels page, change the labels as follows, then click Next:

   Fields	Labels
   COMMISSION_PCT	Commission
   TOTAL_COMP	Total Compensation

8. On the Template page, click Finish to display your report layout in the Paper Layout view. It should look something like this:

Figure 37-10 Paper Layout view of the PL/SQL example report

Text description of the illustration plsql_lay.gif

9. Click the Run Paper Layout button in the toolbar to run and display your report in the Paper Design view.

10. In the Paper Design view, click the Flex Off button in the toolbar.

11. Delete the Last Name label.

12. Change the text for the First Name label to Name.

13. Adjust the width of the new Name label to span over both the last name and first name columns

14. Adjust the sizes of the first name and last name columns so that one character displays between the columns.

15. In the Object Navigator, double-click the properties icon next to the F_FIRST_NAME field to display the Property Inspector, and set properties:
    • Under General Layout, set the Vertical Elasticity property to Fixed, and set the Horizontal Elasticity property to Variable.

16. Repeat the above step for the F_LAST_NAME field.

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

18. In the Paper Layout view, click the Frame tool in the tool palette.

19. Draw a frame around the two fields: F_FIRST_NAME and F_LAST_NAME.

20. With the frame selected, press F4 on your keyboard to display the Property Inspector, and set properties:
    • Under General Information, set the Name property to M_NAME.

21. In the Paper Layout view, make sure Flex Off is selected in the toolbar.

22. With the frame selected, choose Layout > Move Backward until the frame encloses both the F_FIRST_NAME and F_LAST_NAME fields.

    Tip: You can watch the fields in the Object Navigator as you choose Layout > Move Backward. When you see the two fields are sub-nodes of M_NAME, stop.

When you're done, the Object Navigator should look like this:

Figure 37-11 Object Navigator with M_NAME repeating frame

Text description of the illustration plsql_repfra.gif

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

24. In the Paper Design view, Shift-click to select the following columns:
    • Salary

    • Commission

    • Bonus

    • Total Compensation

25. Click the Currency button in the toolbar to add "$" to the numbers.

26. Click the Add Decimal Place button to add a decimal point to the numbers.

    Your report should now look something like this:

Figure 37-12 Paper Design view of modified report

Text description of the illustration plsql_pdvmod.gif

27. Save your report.

37.5 Add vertical space between records

To make the report more readable, you can add space between a certain number of records. To do so, you first create the parameter that determines the number of records between which the space will display. Then, you create a summary column in the data model that counts the number of records. You will then modify the paper layout of your report so that the vertical elasticity is variable. Finally, you will create a format trigger that will display the space between the user-determined number of records.

37.5.1 Create a user parameter

The parameter you will create in this section will determine how many records are displayed before an extra space is printed. Since this parameter is a user parameter, the user can change this value at runtime.

To create a user parameter:

1. In the Object Navigator, under the Data Model node, click the User Parameters node.

2. Choose Edit > Create to create a new user parameter under the User Parameters node.

3. If the Property Inspector is not already displayed, right-click the new user parameter (P_1), then choose Property Inspector to display the Property Inspector, and set properties:
   • Under General Information, set the Name property to SPACE.

   • Under Parameter, set the Datatype property to Number, and set the Initial Value property to 5.

     Note: By giving the user parameter an initial value, the user can simply run the report without changing the parameters, and a space will display between every five records.

   The user parameter now displays in the Object Navigator:

Figure 37-13 User Parameter in the Object Navigator

Text description of the illustration plsql_onup.gif

4. Save your report.

37.5.2 Create a summary column that counts the number of records

In this section, you will create a summary column in the data model that counts the number of employee records. This information will then be used by the format trigger to determine where to add extra space.

To create a summary column:

1. In the Object Navigator, double-click the view icon next to the Data Model node to display the Data Model view.

2. In the Data Model view, click the Summary Column tool in the tool palette, then click in the G_FIRST_NAME group beneath the TOTAL_COMP formula column to create a new summary column:

Figure 37-14 Data Model with new summary column

Text description of the illustration plsql_dmcs.gif

3. Double-click the new summary column object (CS_1) to display the Property Inspector, and set properties:
   • Under General Information, set the Name property to CNT_COLUMN.

   • Under Summary, set the Function property to Count, and set the Source property to FIRST_NAME.

4. Save your report.

37.5.3 Modify the layout

To allow Reports Builder to insert the vertical spacing, you must modify the layout of your report.

To add vertical elasticity:

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

2. Click the Flex On button in the toolbar.

3. In the Paper Layout view, click the repeating frame associated with G_FIRST_NAME.

   Note: If you can't find the repeating frame in the Paper Layout view, you can click R_G_FIRST_NAME in the Object Navigator. The associated repeating frame will be selected in the Paper Layout view.

4. Click the center handle of the frame and drag the frame downward to create additional space. This additional space should be slightly larger than what you want to see between the sets of records.

5. With the repeating frame selected, press F4 on your keyboard to display the Property Inspector, and set properties:
   • Under General Layout, set the Vertical Elasticity property to Variable.

6. In the Paper Layout view, click the Rectangle tool in the tool palette, and draw a rectangle below the fields in the newly created space.

7. Make sure the new rectangle has no fill and no line so that it is not visible.

   The following image shows the new layout with the invisible rectangle:

Figure 37-15 Layout with added vertical space

Text description of the illustration plsql_vertlay.gif

8. Save your report.

37.5.4 Create a format trigger

Now that you've adjusted the layout, you can create a format trigger based on the new boilerplate rectangle you created in the previous section. This format trigger will display this space after every set number of records, determined by the user parameter.

To create a format trigger on the boilerplate rectangle:

1. While the rectangle is selected in the Paper Layout view, press F11 on your keyboard (or choose Tools > PL/SQL Editor) to display the PL/SQL Editor.

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

   function B_1FormatTrigger return boolean is
   begin
    If :CNT_COLUMN mod :SPACE = 0 then
      return(TRUE);
    else
      return(FALSE);
    end if;
   end;
   

   Note: You can enter this code by copying and pasting it from the provided text file called plsql_code.txt. This code is for the Format Trigger.

3. Click Compile.

4. When the code is compiled without errors, click Close.

5. Save your report.

37.6 Run your report to paper

Now that you've added space and created your format trigger, your report should display with space between every five records (or whatever you specify in the Parameter Form).

To run your report:

• Click the Run Paper Layout button in the toolbar. When the Parameter Form displays, click the Run button in the toolbar.

Your report displays in the Paper Design view, and should look something like this:

Figure 37-16 Final PL/SQL example report

Text description of the illustration plsql_fin.gif

37.7 Summary

Congratulations! You have successfully built a report that uses an external PL/SQL library to calculate employee bonuses, which you can now use in other reports by simply referring to it. You now know how to:

• create and use an external PL/SQL library.

• create a default layout in the Report Wizard.

• add vertical space between a user-determined number of records.

For more information on any of the wizards, views, or properties used in this example, refer to the Reports Builder Online Help, which you can access in two ways:

• From the Oracle Technology Network (http://otn.oracle.com/products/reports/), click Documentation and navigate to the Reports Builder Online Help for the most recent, hosted online help.

• From Reports Builder, choose Help > Help Contents.

  Note: You can replace your Reports Builder online help with the most recent update by downloading the latest online help set available on the Oracle Technology Network (http://otn.oracle.com/docs/products/reports/). Instructions for replacing your help file are included in the readme.txt in the download file.