34.3 Create Ranking Logic for Top number of Customers

Now that your report has a data model and layout, you can add the logic to control the number of customers displayed. First, you will create two parameters that the user can enter values for at runtime. Second, you will create a group filter that uses the parameters to control which data is included.

To create parameters and add a group filter:

  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 CUTOFF_CNT.

    • Under Parameter, set the Datatype property to Number, set the Width property to 1, and set the Initial Value property to 3.

  4. Repeat the steps above to create another user parameter, using the following property settings this time:

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

    • Under Parameter, set the Datatype property to Number, set the Width property to 3, and set the Initial Value property to 0.

  5. In the Object Navigator, under the Data Model node, expand the Groups node, then double-click the properties icon next to the G_CNAME group to display the Property Inspector, and set the following properties:

    • Under Group, set the Filter Type property to PL/SQL, then click the PL/SQL Filter property field to display the PL/SQL Editor.

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

    function G_CNAMEGroupFilter return boolean is
    begin
      :incr_cnt:=:incr_cnt+1;
        if :incr_cnt <= :cutoff_cnt 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 rank_code.txt. This code is for the Group Filter.

  7. Click Compile.

  8. Click Close.

    This filter increments the counter by 1 each time a record in G_CNAME is fetched, then compares the counter's value to the specified cutoff. When the counter exceeds the cutoff, no more records are fetched.

    Tip:

    Notice that, if the Paper Design view is still open while you add this logic, the report now returns no records in the Paper Design view. To fix this issue, you should display one of the other views (for example, the Data Model view) and then come back to the Paper Design view. You will be prompted by the Runtime Parameter Form to enter values for the two parameters, INCR_CNT and CUTOFF_CNT.

  9. Click the Run Paper Layout button in the toolbar to display the Runtime Parameter Form, which enables you to change the default values for CUTOFF_CNT and INCR_CNT.

  10. Click the Run Report button to display the report output in the Paper Design view.

  11. Save your report as rank_your_initials.rdf.

    Figure 34-3 Tabular report output restricted to top three customers

    Description of Figure 34-3 follows
    Description of "Figure 34-3 Tabular report output restricted to top three customers"