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 query in any of the following ways:

    • Copy and paste the code from the provided text file called rank_code.txt (SELECT statement for Percentage Calculation) into the SQL Query Statement field.

    • Click Query Builder to build the query without entering any code manually.

    • Type the code in the SQL Query Statement field.

  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 follows
    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 follows
Description of "Figure 34-6 Tabular report output restricted by number and percentage of customers"