21.3 Create a Formula Column and a Summary Column

The steps in this section will show you how to add a formula column to the Q_EMP (or detail) query you built in the previous section that will return a value every time a detail record is returned. You will then add a summary column to the Q_DEPT (or master) query that will count the number of times this formula column returns a value.

Before you create either of these columns, you may want to expand the size of your groups for better visibility. To do so, select the yellow group box. Click the bottom frame, then drag it down about 0.25 inches (0.5 centimeters). If you do this for both groups, you will have enough room to add your new columns.

21.3.1 Create a formula column in the detail query

This section will show you how to create a formula column that will return a single value.

To create a formula column:

  1. In the Data Model view, click the Formula Column tool in the tool palette.

  2. Click in the Q_EMP query, under the JOB column to create a 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 DETAIL_VAL.

    • 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 DETAIL_VALFormula return Number is
    begin
         return(1);
    end;
    
  5. Click Compile.

    Tip:

    If you receive errors when compiling, compare your code against the code provided. You can also simply copy and paste the code from suppresslabels_code.txt.

  6. When the code is compiled, click Close.

21.3.2 Create a summary column in the master query

The steps in this section will show you how to create and customize a summary column in the master query that will depend on the information returned by the formula column.

To create a summary column:

  1. In the Data Model view, click the Summary Column tool in the tool palette.

  2. Click in the Q_DEPT query, under the LOC column to create a 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 COUNT_DETAIL.

    • Under Summary, set the Function property to Count, set the Source property to DETAIL_VAL, and set the Reset At property to G_DEPTNO.

    Your data model now contains both the formula and summary columns, and should look like this:

    Figure 21-3 Data Model view with formula and summary columns

    Description of Figure 21-3 follows
    Description of "Figure 21-3 Data Model view with formula and summary columns"

  4. Save your report as suppresslabels_your_initials.rdf.