34 Building a Ranking Report

In this chapter, you will learn about reports that rank data in different way. By following the steps in this chapter, you can generate the report output shown in Figure 34-1.

Figure 34-1 Tabular report output, ranked by number and percentage of customers

Description of "Figure 34-1 Tabular report output, ranked by number and percentage of customers"

Concepts

You can create a report that ranks data by comparing it to a user-specified bind parameter. This enables you to rank data in different ways in the same report; for example, by count and by percentage. You can set the ranking criteria at runtime, or let them default to previously specified values.

Data Relationships

This report requires one query to fetch the data. To rank the data, you'll create the following objects:

• A bind parameter that gives you the option of setting the cutoff point at runtime.

• A bind parameter that increments by one each time a record is fetched from the database. The value in this parameter will be compared to the cutoff Bind parameter.

• A customized group filter to include only records within the cutoff value. Using a customized filter rather than a packaged filter enables you to parameterize your cutoff values.

Layout

This report uses a tabular layout style, with some modifications.

Example Scenario

In this example, you will create a report that displays the names and total purchases of your top three customers and the top 75% of all customers. Furthermore, you will allow end users of your report to set the ranking criteria (that is, the top x customers and the top y% of all customers) at runtime.

As you build this example report, you will:

• Create a data model and tabular layout.

• Create ranking logic for top number of customers by creating parameters and a group filter to control the ranking criteria.

• Add a layout object for a parameter to display the parameter value in the report output.

• Create a Parameter Form that only displays the parameter you want end users to set.

• Add a percentage ranking.

To see a sample ranking report, open the examples folder named ranking, then open the Oracle Reports example named rank.rdf. For details on how to access it, see "Accessing the Example Reports" in the Preface.

34.1 Prerequisites for this example

To build the example in this chapter, you must have access to the Summit Sporting Goods (SUMMIT) schema. To download the SQL scripts that install the schema, see "Accessing the Data Sources" in the Preface.

34.2 Create a data model and tabular layout

When you are creating a single-query report, such as this one, you can use the Report Wizard to create the data model and layout simultaneously.

To create a data model and layout:

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

2. In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.

3. If the Welcome page displays, click Next.

4. On the Report Type page, select Create Paper Layout Only, then click Next.

5. On the Style page, type a Title for your report, select Tabular, then click Next.

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

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

   SELECT CUSTNAME CNAME, SUM(AMOUNT) SUM_AMT FROM SALES
   GROUP BY CUSTNAME
   ORDER BY SUM(AMOUNT) DESC
   

   
   

   Note: You can enter this query in any of the following ways: Copy and paste the code from the provided text file called rank_code.txt in 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.

   
   

8. 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 34.1, "Prerequisites for this example" describes the sample schema requirements for this example.

   
   

9. On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list, then click Next.

10. On the Totals page, click Next.

11. On the Labels page, set the labels and field widths as follows, then click Next:

    Fields	Labels	Width
    CNAME	Customer Name	35
    SUM_AMT	Total Purchases	15

    
    

12. On the Template page, click Finish to preview your report output in the Paper Design view. It should look something like this:

    Figure 34-2 Paper Design view for the ranking report

    
    Description of "Figure 34-2 Paper Design view for the ranking report"
    
    

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 Tabular report output restricted to top three customers"
    
    

34.4 Add a layout object for a parameter

As a way for users to quickly tell the number of customers displayed in the list, you need to display the value of CUTOFF_CNT in the report. To perform this task, you will create a boilerplate text object that references the parameter.

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

2. Position the Paper Layout view and Object Navigator side-by-side so that they are visible simultaneously.

3. In the Object Navigator, in the Find field, type M_G_CNAME_GRPFR.

4. Click M_G_CNAME_GRPFR in the Object Navigator to select it in the Paper Layout view.

5. Click the title bar of the Paper Layout view to make it the active window.

6. Select M_G_CNAME_GRPFR and all of its contents by press CTRL-A on your keyboard.

   
   

   Note: You can also use the Find field in the Object Navigator to locate specific objects. When you select an item in the Object Navigator while the Paper Layout view is displayed, the corresponding object is selected in the Paper Layout view.

   
   

7. Use the down arrow key to move the items down about 1 inch (2.5 centimeters).

8. From the font lists in the toolbar, choose Arial (Western), point size 10.

9. Click the Text tool in the tool palette.

10. Click directly above the label Customer Name to create a new boilerplate text object and type the following text:

    Top &CUTOFF_CNT Customers:
    

11. Move to an open area of the Paper Layout view and click the mouse button to exit text mode. Notice that the text object you just created is still selected, you can now adjust its positioning with the arrow keys.

12. Click the Bold button in the toolbar to make the new text bold.

13. Shift-click the labels Customer Name and Total Purchase so that they are selected simultaneously with the object you just created.

14. Click the Underline button in the toolbar.

15. Click in an open area of the Paper Layout view to deselect everything.

16. Click F_SUM_AMT, then choose Tools > Property Inspector to display the Property Inspector, and set the following properties:

    • Under Field, set the Format Mask property by typing the following:

      -$NNN,NN0.00

17. Click the Paper Design button in the toolbar to display the Paper Design view. (If the Runtime Parameter Form displays, click the Run Report button in the toolbar.)

    Figure 34-4 Tabular report output with parameter value displayed

    
    Description of "Figure 34-4 Tabular report output with parameter value displayed"
    
    

18. Save your report.

34.5 Create a Parameter Form

By default, both of your user parameters appear in the Runtime Parameter Form. In reality, you only want users to set CUTOFF_CNT. You do not want them to be able to set INCR_CNT (the amount by which your counter is increased for each record). To prevent users from seeing INCR_CNT on the Runtime Parameter Form, you will build your own Parameter Form.

To create a Parameter Form:

1. Choose Tools > Parameter Form Builder.

2. In the Parameter Form Builder dialog box, scroll down the list of parameters until you find INCR_CNT.

3. Click INCR_CNT to deselect it.

4. Change the label for CUTOFF_CNT to:

   # of Top Customers:

5. Click OK.

6. Click the Run Paper Layout button in the toolbar.

7. In the Runtime Parameter Form, type a value for # of Top Customers, then click the Run Report button in the toolbar. You should now see as many records as you asked for in the Runtime Parameter Form and they should be in order from largest total purchases to smallest total purchases.

   
   

   Tip: As an additional exercise, you could now change the Initial Value property of CUTOFF_CNT to see its effect on the Runtime Parameter Form.

   
   

8. Save your report.

34.6 Add a percentage ranking

Another way to rank customers is by percentage of total sales. The idea is the same as ranking by count, but with an important difference. Since you need to fetch all of the data in order to compute a running percent of total summary, you don't want to use a group filter to weed out data. You need to use a format trigger on the repeating frame to compare the running total to your cutoff parameter.

To fetch the data for the percentage calculation:

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

2. In the Data Model view that displays, click the SQL Query tool in the tool palette then click in an open area of the Data Model view, somewhere to the right of query Q_1, to display the SQL Query Statement dialog box.

3. In the SQL Query Statement field, enter the following SELECT statement:

   SELECT CUSTNAME CNAME2, SUM(AMOUNT) SUM_AMT2 
   FROM SALES
   GROUP BY CUSTNAME
   ORDER BY SUM(AMOUNT) DESC
   

   
   

   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 Percentage Calculation.

   
   

4. Click OK. Your data model should now look like the following image.

   Figure 34-5 Data Model view of the Ranking Report

   
   Description of "Figure 34-5 Data Model view of the Ranking Report"
   
   

5. Save your report.

To create a parameter for the percentage cutoff:

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

   • Under Parameter, set the Datatype property to Number, set the Width property to 2, and set the Initial Value property to 75.

4. In the Data Model view, click the group object G_CNAME2, then click and drag the bottom handle down about 0.25 inches (0.5 centimeters) to make the group bigger.

5. Click the Summary Column tool in the tool palette.

6. Click in the empty space beneath SUM_AMT2 to create a summary column.

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

   • Under Column, set the Datatype property to Number, and set the Width property to 10.

   • Under Summary, set the Function property to % of Total, set the Source property to SUM_AMT2, set the Reset At property to Report, and set the Compute At property to Report.

To create a second layout for the list of customers by percentage:

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

2. In the Paper Layout view, click the Report Block tool in the tool palette.

3. Starting about 1 inch (2.5 centimeters) below the existing layout, click and drag a box about the same size as the existing layout to define the size of the second layout and display the Report Block Wizard.

4. On the Style page of the Report Block Wizard, select Tabular, then click Next.

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

6. On the Fields page, click the following fields and click the right arrow (>) to move them to the Displayed Fields list, then click Next:

   • CNAME2

   • SUM_AMT2

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

   Fields	Labels	Width
   CNAME2	Customer Name	35
   SUM_AMT2	Total Purchases	15

   
   

8. On the Template page, select No template. (If you were to select a template, it would override the template of the previous layout), then click Finish to display your report layout in the Paper Layout view.

9. From the font lists in the toolbar, choose Arial (Western), point size 10.

10. In the Paper Layout view, click the Text tool in the tool palette.

11. Click directly above the label Customer Name in the new layout and type the following text in the new boilerplate text object:

    Top &CUTOFF_PCT Percent of Sales:
    

12. Move to an open area of the Paper Layout view and click the mouse button to exit text mode. Notice that the text object you just created is still selected, you can now adjust its positioning with the arrow keys.

13. If the text is not already bold, click the Bold button in the toolbar to make it bold.

14. Shift-click the labels Customer Name and Total Purchases so that they are selected simultaneously with the object you just created.

15. Click the Underline button in the toolbar.

16. Click in an open area of the Paper Layout view to deselect everything.

17. Double-click field F_SUM_AMT2 to display the Property Inspector, and set the following properties:

    • Under Field, set the Format Mask property by typing:

      -$NNN,NN0.00
      

To add the logic for the percentage cutoff:

1. In the Object Navigator, type R_G_CNAME2 in the Find field to locate that repeating frame.

2. Double-click the properties icon next to R_G_CNAME2 to display the Property Inspector, and set the following properties:

   • Under Advanced Layout, double-click the Format Trigger 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 R_G_CNAME2FormatTrigger return boolean is
   begin
   if :r_pct <= :cutoff_pct 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 Percentage Cutoff.

   
   

To update the Parameter Form for the new percentage parameter:

1. Choose Tools > Parameter Form Builder.

2. In the Parameter Form Builder dialog box, scroll down the list of parameters until you find CUTOFF_PCT, and change its label to:

   Top Percentage (%):

3. Click OK.

4. Click the Run Paper Layout button in the toolbar.

5. In the Runtime Parameter Form:

   • For # of Top Customers, type 5.

   • For Top Percentage (%), type 80.

6. Click the Run Report button in the toolbar.

Figure 34-6 Tabular report output restricted by number and percentage of customers

Description of "Figure 34-6 Tabular report output restricted by number and percentage of customers"

34.7 Summary

Congratulations! You have successfully created a ranking report. You now know how to:

• create a data model and a tabular layout.

• create parameters and group filter to control the ranking criteria.

• add a layout object to display the parameter value in the report output.

• create a Parameter Form that only displays the parameter you want users to set.

• add a percentage ranking.

For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 3.1.1, "Using the Oracle Reports online Help".