40.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, and so on.

40.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 40.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 40-3 Data Model view of the PL/SQL report

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

  10. Save your report.

40.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 40-4 Data Model with expanded G_FIRST_NAME

    Description of Figure 40-4 follows
    Description of "Figure 40-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 40-5 Data Model with unnamed formula column

    Description of Figure 40-5 follows
    Description of "Figure 40-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 40-6 Data Model with BONUS formula column

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

  7. Save your report.

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

    Description of Figure 40-7 follows
    Description of "Figure 40-7 Object Navigator with FINAL_CALC function"

  7. Save your report.

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

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

  6. Save your report.