33
Building a Ranking Report
Figure 33-1 Tabular report output, ranked by number and percentage of customers
Text description of the illustration rank_fin.gif
This report ranks data two different ways: by count and by percentage. The upper portion displays the names and the total purchases of the top three customers; the lower portion displays the names and total purchases of those customers who constitute 75% of all sales. You can set the ranking criteria at runtime, or let them default to previously specified values.
Concepts
This example report ranks the data by comparing it to a user-specified bind parameter. It 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 allows you to parameterize your cutoff values.
Layout
- This report uses a tabular layout style, with some modifications.
Example Scenario
Suppose that you want to create a report that displays the names and total purchases of your top 3 customers and the top 75% of all customers. Furthermore, suppose that you decide it would be better to let the users of the report choose the ranking criteria (i.e., the top x customers and the top x% of all customers).
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.
Table 33-1 Features demonstrated in this example
33.1 Prerequisites for this example
To build the example in this chapter, you must have access to the Summit Sporting Goods schema, which we've provided on the Oracle Technology Network (http://otn.oracle.com/products/reports/). To download the SQL scripts that install the schema, go to the Documentation page on OTN and follow the instructions provided on the Web page.
33.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:
- Launch Reports Builder (or, if already open, choose File > New > Report).
- In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.
- If the Welcome page displays, click Next.
- On the Report Type page, select Create Paper Layout Only, then click Next.
- On the Style page, type a Title for your report, select Tabular, then click Next.
- On the Data Source page, click SQL Query, then click Next.
- 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.
|
Click Next.
Note:
If you are not already connected to a database, you will be prompted to connect to the database when you click Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 33.1, "Prerequisites for this example" describes the sample schema requirements for this example.
|
- On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list, then click Next.
- On the Totals page, click Next.
- On the Labels page, set the labels and field widths as follows, then click Next:
- On the Template page, click Finish to preview your report output in the Paper Design view. It should look something like this:
Figure 33-2 Paper Design view for the ranking report
Text description of the illustration rank_out1.gif
33.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:
- In the Object Navigator, under the Data Model node, click the User Parameters node.
- Choose Edit > Create to create a new user parameter under the User Parameters node.
- 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 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.
- 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.
- 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 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.
- 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.
|
- Click Compile.
- 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 (e.g., 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
|
- Click the Run Paper Layout button in the toolbar to display the report output in the Paper Design view
- Save your report.
Figure 33-3 Tabular report output restricted to top three customers
Text description of the illustration rank_out2.gif
33.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.
- In the Object Navigator, double-click the view icon next to the Paper Layout node to display the Paper Layout view.
- Position the Paper Layout view and Object Navigator side-by-side so that they are visible simultaneously.
- In the Object Navigator, in the Find field, type
M_G_CNAME_GRPFR.
- Click M_G_CNAME_GRPFR in the Object Navigator to select it in the Paper Layout view.
- Click the title bar of the Paper Layout view to make it the active window.
- 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.
|
- Use the down arrow key to move the items down about one inch.
- From the font lists in the toolbar, choose Arial (Western), point size 10.
- Click the Text tool in the tool palette.
- Click directly above the label Customer Name to create a new boilerplate text object and type the following text:
Top &CUTOFF_CNT Customers:
- 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.
- Click the Bold button in the toolbar to make the new text bold.
- Shift-click on the labels Customer Name and Total Purchase so that they are selected simultaneously with the object you just created.
- Click the Underline button in the toolbar.
- Click in an open area of the Paper Layout view to deselect everything.
- Click F_SUM_AMT, then choose Tools > Property Inspector to display the Property Inspector, and set properties:
- Under Field, set the Format Mask property by typing the following:
-$NNN,NN0.00
- Click the Paper Design button in the toolbar to display the Paper Design view. (If the Runtime Parameter Form displays, click the Run Paper Layout button in the toolbar.)
Figure 33-4 Tabular report output with parameter value displayed
Text description of the illustration rank_out3.gif
- Save your report.
33.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:
- Choose Tools > Parameter Form Builder.
- In the Parameter Form Builder dialog box, scroll down the list of parameters until you find INCR_CNT.
- Click INCR_CNT to deselect it.
- Change the label for CUTOFF_CNT to:
# of Top Customers:
- Click OK.
- Click the Run Paper Layout button in the toolbar.
- 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.
|
- Save your report.
33.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:
- In the Object Navigator, double-click the view icon next to the Data Model node to display the Data Model view.
- In the Data Model view, click the SQL Query tool in the tool palette.
- Click in an open area of the Data Model view, somewhere to the right of the first query (Q_1) to display the SQL Query Statement dialog box.
- In the SQL Query Statement dialog box, 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.
|
- Click OK. Your data model should now look like the following image.
Figure 33-5 Data Model view of the Ranking Report
Text description of the illustration rank_dm.gif
- Save your report.
To create a parameter for the percentage cutoff:
- In the Object Navigator, under the Data Model node, click the User Parameters node.
- Choose Edit > Create to create a new user parameter under the User Parameters node.
- 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 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.
- In the Data Model view, click the group object G_CNAME2, then click and drag the bottom handle down about 0.25 inches to make the group bigger.
- Click the Summary Column tool in the tool palette.
- Click in the empty space beneath SUM_AMT2 to create a summary column.
- Double-click the new summary column object (CS_1) to display the Property Inspector, and set 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:
- Click the Paper Layout button in the toolbar to display the Paper Layout view.
- In the Paper Layout view, click the Report Block tool in the tool palette.
- Starting about 1 inch 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.
- On the Style page of the Report Block Wizard, select Tabular, then click Next.
- 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.
- On the Fields page, click the following fields and click the right arrow (>) to move them to the Displayed Fields list, then click Next:
- On the Labels page, change the labels and field widths as follows, then click Next:
- 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.
- From the font lists in the toolbar, choose Arial (Western), point size 10.
- In the Paper Layout view, click the Text tool in the tool palette.
- 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:
- 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.
- If the text is not already bold, click the Bold button in the toolbar to make it bold.
- Shift-click on the labels Customer Name and Total Purchase so that they are selected simultaneously with the object you just created.
- Click the Underline tool in the toolbar.
- Click in an open area of the Paper Layout view to deselect everything.
- Double-click field F_SUM_AMT2 to display the Property Inspector, and set properties:
- Under Field, set the Format Mask property by typing:
-$NNN,NN0.00
To add the logic for the percentage cutoff:
- In the Object Navigator, type
R_G_CNAME2 in the Find field to locate that repeating frame.
- Double-click the properties icon next to R_G_CNAME2 to display the Property Inspector, and set properties:
- Under Advanced Layout, click the Format Trigger property field to display the PL/SQL Editor.
- 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:
- Choose Tools > Parameter Form Builder.
- 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 (%):
- Click OK.
- Click the Run Paper Layout button in the toolbar.
- In the Runtime Parameter Form:
- For # of Top Customers, type
5.
- For Top Percentage (%), type
80.
- Click the Run Report button in the toolbar.
Figure 33-6 Tabular report output restricted by number and percentage of customers
Text description of the illustration rank_fin.gif
33.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 Reports Builder Online Help, which you can access in two ways:
