30 Building a Report with Aggregate Data

In this chapter, you will learn about reports that include aggregate data, based on value ranges. By following the steps in this chapter, you can generate the report output shown in Figure 30-1.

Figure 30-1 Aggregate data report output

Description of "Figure 30-1 Aggregate data report output"

Concepts

In a report that aggregates, or collects, data within ranges, values from the database are retrieved and formatted based on an aggregate range that you define. You can even use parameters to specify the range over which the data should be collected.

Data Relationships

This report uses two "functions" in its SELECT statement to specify the aggregate range. The functions are (FLOOR(SAL/1000))*1000), which calculates the lowest salary, and (CEIL*((SAL+1)/1000) * 1000), which calculates the highest salary. The columns that receive the values of these functions are placed into a break group to produce the control break format of this example report.

Layout

This report uses the Group Left layout style.

Example Scenario

In this example, you will build both a paper and JSP-based Web report that collects and displays names of all employees whose salaries fall within the range of 0 to 999, then collects and displays all employees whose salaries fall within the range of 1000 to 1999, and so on. You will be able to modify this report to display any aggregate range you need.

As you build this example report, you will:

Create a query and the layout using the Report Wizard to create a data model and layout for both the paper and Web reports.
Modify the Web source of your JSP report to generate the JSP-based Web report.

To see a sample report that aggregates data within ranges, open the examples folder named aggregatingdata, then open the Oracle Reports example named aggregatingdata.rdf. For details on how to open it, see "Accessing the Example Reports" in the Preface.

30.1 Prerequisites for this example

To build the example in this chapter, you must have access to the SCOTT sample schema provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.

30.2 Create a query and the layout

The steps in this section will show you how to build a simple data model and report layout in the Report Wizard, which you can then use to generate either a JSP-based Web report or a paper report. In the next section, you will modify the JSP so that the appropriate information displays in your Web report.

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 both Web and Paper Layout, then click Next.
On the Style page, type a Title for your report, select Group Left, 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 (FLOOR(SAL/1000))*1000 BOTTOM,
CEIL((SAL+1)/1000) * 1000 TOP,
ENAME,
DEPTNO
FROM EMP
ORDER BY 1,2, SAL

Note:

You can enter this query in any of the following ways:

Copy and paste the code from the provided text file called aggregatingdata_code.txt into 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 Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 30.1, "Prerequisites for this example" describes the sample schema requirements for this example.

On the Groups page, click the following fields in the Available Fields list and click the right arrow (>) to move them to the Group Fields list, then click Next:
- BOTTOM
- TOP
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, click Next.
On the Template page, select Predefined Template and click Beige, then click Finish to display your report output in the Paper Design view. It should look something like this:

Figure 30-2 Paper Design view for the aggregating data report

Description of "Figure 30-2 Paper Design view for the aggregating data report"
In the Paper Design view, click the text Bottom and change the text to Salary Range.
Delete the text Top.

Note:
You can also modify the other column headers to make the text more meaningful.
Click the Paper Layout button in the toolbar to display the Paper Layout view. The layout currently looks like this:

Figure 30-3 Paper Layout view of the Aggregating Data report

Description of "Figure 30-3 Paper Layout view of the Aggregating Data report"
In the Paper Layout view, click the Flex Off button in the toolbar to set Flex mode off.

Click the right edge of the f_bottom frame and drag it to the left about 0.5 inches (1.5 centimeters) to make room between the f_bottom and f_top fields.

Tip:

To find objects in the Paper Layout view, you can use the Object Navigator. When you click an item name in the Object Navigator, the corresponding object is selected in the Paper Layout view.

Click the Text tool in the tool palette.
Drag a boilerplate text object between f_bottom and f_top, then type "-" in the text box. The layout should now look like this:

Figure 30-4 Paper Layout view with new boilerplate text

Description of "Figure 30-4 Paper Layout view with new boilerplate text"
Now, click the Run Paper Layout button in the toolbar to display your report. It should look something like this.

Figure 30-5 Paper Design view

Description of "Figure 30-5 Paper Design view "

Note:
Notice how the report displays the employee names per salary range, hence aggregating the data.
Save your report as aggregatereport_your_initials.rdf.
Take a look at the data model of your report. You can use this data model to generate either a paper report or a JSP-based Web report. To view the data model, click the Data Model button in the toolbar. Your data model should look something like this:

Figure 30-6 Data Model for the aggregating data report

Description of "Figure 30-6 Data Model for the aggregating data report"

30.3 Modify the Web source of your JSP report

Now that you have created your paper report layout, you will now learn how to take the same report and generate a JSP-based Web report that looks the same as your paper report.

To modify your JSP-based Web report:

Save your report, aggregatereport_your_initials.rdf as a JSP under the same name (aggregatereport_your_initials.jsp).
Click the Web Source button in the toolbar to display the Web Source view.

In the Web Source, you need to change the column titles to match those of the paper report.
In the Web Source view, find the text:
```
<th <rw:id id="HBbottom" asArray="no"/> class="OraColumnHeader"> Bottom </th>
```
Tip:
Choose Edit > Find and Replace, then type "bottom" in the Find what text box.

Change the column header to "Salary" so that the line now looks like this:

<th <rw:id id="HBbottom" asArray="no"/> class="OraColumnHeader"> Salary </th>

The next line of code indicates the header for the "Top" column. Delete the header text so that the line looks like this:
```
<th <rw:id id="HBtop" asArray="no"/> class="OraColumnHeader">  </th>
```

Change the other two columns for Ename and Deptno as desired. For example, we changed them to "Name" and "Dept", like so:

<th <rw:id id="HBename" asArray="no"/> class="OraColumnHeader"> Name </th>
<th <rw:id id="HBdeptno" asArray="no"/> class="OraColumnHeader"> Dept </th>

Save your report as a JSP.

Click the Run Web Layout button in the toolbar to display your new JSP-based Web Report in your browser.

Notes:

If Netscape 7.0 is your default browser, and the browser does not display, set the registry key HKEY_CURRENT_USERS\Software\Oracle\Toolkit\Tkbrowser to the default browser location. Ensure that the BrowserName and the BrowserPath keys reflect the correct values. For example: BrowserName=Netscape 7; BrowserPath=C:\Program Files\Netscape\Netscape\Netscp.exe.
If Run Web Layout fails with an error related to the major.minor version, you will need to update the environment variables CLASSPATH or REPORTS_CLASSPATH, as follows: remove the 9.0.x ORACLE_HOME specification in the oc4j.jar file path (the oc4j.jar file path should specify only the 10g Release 2 (10.1.2) ORACLE_HOME).

The report should look something like this:

Figure 30-7 Aggregating Data JSP-based Web Report

Description of "Figure 30-7 Aggregating Data JSP-based Web Report"

The report displays, in Web format, the aggregate data you specified. Here, you can see which employees fall into the specified salary ranges.

Note:

For information on creating a Parameter Form for a JSP-based Web report, refer to the documented example on the Oracle Technology Network (OTN): on the Oracle Reports 10g page (http://www.oracle.com/technology/products/reports/index.html), click Getting Started to display the Getting Started with Oracle Reports home page. In the list of topic sections on the left, click Index. In the Collateral Type list, choose Examples, and click Search. In the list of examples that displays, click Building a JSP-based Web Report with a Parameter Form.

30.4 Summary

Congratulations! You have successfully built a report that aggregates data, for both paper and Web. You now know how to:

create a report definition that aggregates data.
modify your report for the Web.

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