39 Building a Report that Includes PL/SQL

39.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, enter the following SELECT statement in the Data Source definition field:

   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 Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 39.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 39-3 Data Model view of the PL/SQL report

   
   Description of "Figure 39-3 Data Model view of the PL/SQL report"
   
   

10. Save your report.

39.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 group for more columns. Here is an example of what it should look like now:

   Figure 39-4 Data Model with expanded G_FIRST_NAME

   
   Description of "Figure 39-4 Data Model with expanded G_FIRST_NAME"
   
   

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

   Figure 39-5 Data Model with unnamed formula column

   
   Description of "Figure 39-5 Data Model with unnamed formula column"
   
   

3. 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 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 have 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 39-6 Data Model with BONUS formula column

   
   Description of "Figure 39-6 Data Model with BONUS formula column"
   
   

7. Save your report.

39.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 39-7 Object Navigator with FINAL_CALC function

   
   Description of "Figure 39-7 Object Navigator with FINAL_CALC function"
   
   

7. Save your report.

39.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 39.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 the following 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 39-8 Data Model with BONUS and TOTAL_COMP formula columns

   
   Description of "Figure 39-8 Data Model with BONUS and TOTAL_COMP formula columns"
   
   

6. Save your report.

39.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 the following 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 39-13 User Parameter in the Object Navigator

   
   Description of "Figure 39-13 User Parameter in the Object Navigator"
   
   

4. Save your report.

39.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 39-14 Data Model with new summary column

   
   Description of "Figure 39-14 Data Model with new summary column"
   
   

3. Double-click the new summary column object (CS_1) to display the Property Inspector, and set the following 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.

39.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 cannot 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, choose Tools > Property Inspector to display the Property Inspector, and set the following 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 39-15 Layout with added vertical space

   
   Description of "Figure 39-15 Layout with added vertical space"
   
   

8. Save your report.

39.5.4 Create a format trigger

Now that you have 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.