Skip Headers

Oracle Reports Building Reports
10g (9.0.4)

Part Number B10602-01
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

9
Building a Summary Report

Figure 9-1 Summary report output

Text description of summaryb_fin.gif follows.

Text description of the illustration summaryb_fin.gif

A summary report contains at least one column whose value or values consist of a summary of other data. A column that totals sales, a column that averages a list of commissions, and a column that shows the maximum amounts found in a series of purchase orders are all examples of summary columns.

Concepts

Example Scenario

Suppose that you want to create a report that displays and summarizes sales data by sales representative. This report would include the following summaries:

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

Table 9-1 Features demonstrated in this example
Feature Location

Create a data model with summaries and a layout

Section 9.2, "Create a data model and a group above layout"

Format fields for monetary values

Section 9.3, "Format fields"

Review the properties of summaries created by the Report Wizard

Section 9.4, "Examine the summary column properties (optional)"

9.1 Prerequisites for this example

To build the example in this chapter, you must have access to the Order Entry schema, which is provided by default with the Oracle9i database. If you don't know if you have access to the sample schema provided with the Oracle9i database, contact your database administrator.

9.2 Create a data model and a group above 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 Group Above, 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 SALES_REP_ID, CUSTOMER_ID, SUM(ORDER_TOTAL) TOTAL 
    FROM ORDERS
    GROUP BY SALES_REP_ID, CUSTOMER_ID
    


    Note:

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

    • Copy and paste the code from the provided text file called summary_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.


  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 Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 9.1, "Prerequisites for this example" describes the sample schema requirements for this example.


  9. On the Groups page, click SALES_REP_ID and click the right arrow (>) to move this field to the Group Fields list, then click Next.

Figure 9-2 Groups page of the Report Wizard

Text description of summaryb_brwiz.gif follows.

Text description of the illustration summaryb_brwiz.gif

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

  2. On the Totals page, click TOTAL in the Available Fields list, then click Sum. Given the data model you are using, this step will create two summary columns for you:

    • SumTOTALPerSALES_REP_ID sums the total of TOTAL for each value of SALES_REP_ID (i.e., for each sales representative).

    • SumTOTALPerReport sums the total of TOTAL for the entire report (i.e., for all sales representatives).

  3. Still on the Totals page, click % of Total. Given the data model you are using, this step will create two columns for you:

    • TotalTOTALPerCUSTOMER_ID calculates dollars for each customer (CUSTOMER_ID) as a percentage of the total dollars for each sales representative (SALES_REP_ID).

    • TotalTOTALPerSALES_REP_ID calculates dollars for each sales representative (SALES_REP_ID) as a percentage of the total dollars in the entire report.

Figure 9-3 Totals page of Report Wizard

Text description of summaryb_sum.gif follows.

Text description of the illustration summaryb_sum.gif

  1. Click Next.

  2. On the Labels page, change the labels as follows, then click Next:

    Fields Labels

    SALES_REP_ID

    Sales Rep

    CUSTOMER_ID

    Customer

    TOTAL

    Dollars

  1. On the Template page, make sure Beige is selected under Predefined Template.

  2. Click Finish to display your report output in the Paper Design view.

9.3 Format fields

In the Paper Design view, notice the Total field. The values are neither aligned nor displayed as monetary amounts. You can quickly rectify this in the Paper Design view.

To assign a format mask to monetary values:

  1. In the Paper Design view, select the first number value underneath the Dollars label. Notice that all of the values are immediately selected, indicating that you can change their properties simultaneously.

    Tip:

    If you are familiar with format mask syntax, you could now right click on the field values, choose Property Inspector, and choose or manually enter a value for the Format Mask property.

  2. Click the Currency button in the toolbar. A currency symbol immediately appears next to all of the values.

  3. Click the Add Decimal Place button twice. Two decimal places are added to the right of the decimal point.

  4. Resize the field by clicking and dragging the rightmost handle of the field approximately 0.5 inches to the left.

  5. Click the Align Right button. All of the values are immediately right aligned.

  6. Select the Dollars label itself.

  7. Click the Align Right button.

  8. Select the number value to the right of the Total: label.

  9. Click the Currency button. A currency symbol immediately appears next to all of the values.

  10. Click the Add Decimal Place button twice. Two decimal places are added to the right of the decimal point.

  11. Click the Align Right button. All of the values are immediately right aligned.

Figure 9-4 Summary report output with monetary values formatted

Text description of summaryb_fin.gif follows.

Text description of the illustration summaryb_fin.gif

  1. Go to the end of the report. The last value on the last page should be a summary labelled Total:, which sums the values of TOTAL across the entire report.

  2. Select the unformatted number to the right of the Total: label.

  3. Click and drag this field until its right edge is aligned with the right edge of the values in the Total column.

  4. Click the Currency button in the toolbar. A currency symbol immediately appears next to all of the values.

  5. Click the Add Decimal Place button twice. Two decimal places are added to the right of the decimal point.

  6. Click the Align Right button. All of the values are immediately right aligned.

  7. Save your report.

Figure 9-5 Summary report with report total formatted

Text description of summaryb_out1.gif follows.

Text description of the illustration summaryb_out1.gif

9.4 Examine the summary column properties (optional)

In this case, the Report Wizard created the summaries according to the requirements of the project. However, in some cases, you may need to manually adjust the settings of summaries to get the exact calculations you wish. By carefully reviewing the summaries created by the Report Wizard, you can gain a better understanding of how summaries work.

In this section, you will not be making any changes to the report, you will simply be checking the summary settings to better understand summaries.

Reviewing summary settings

  1. In the Paper Design view, select the percentage value to the right of the % of Total label.

  2. Double-click the values to display the Property Inspector.

  3. In the Property Inspector, notice the Source property, which indicates the column that is the source of the field.

  4. Repeat steps 1 through 3 for all of the summaries in the report. Note down the name of the columns that are the sources for each of these summary fields.

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

  6. In the Data Model view, double-click TotalTOTALPerSALES_REP_ID in group G_SALES_REP_ID to display the Property Inspector.

  7. In the Property Inspector, notice the values of the properties under Summary:

    • Function is % of Total. Change % of Total to Sum. Note that the Compute At property disappears because it is unnecessary for a Sum calculation. Change Function back to % of Total and Compute At reappears.

    • Source is the TOTAL column, which means that TOTAL is used to compute the summary.

    • Reset At is G_SALES_REP_ID. The value of TotalTOTALPerREPID will reset to zero after each record in G_SALES_REP_ID (i.e., for each sales representative).

    • Compute At is Report. A compute level of Report means that the TotalTOTALPerSALES_REP_ID column will base its percentages on the sum of all of the TOTAL in the entire report.

  8. Repeat steps 6 and 7 for each of the summaries in the data model. Notice the differences in the properties for each of the summaries.

  9. It can also be a useful exercise to return to the Paper Design view and see where fields that correspond to the summaries are placed in the layout. For example, the field that corresponds to TotalTOTALPerSALES_REP_ID is placed inside the master repeating frame, R_G_SALES_REP_ID, but outside of the detail repeating frame, R_G_CUSTOMER_ID.

9.5 Summary

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

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:


Go to previous page Go to next page
Oracle
Copyright © 2002, 2003 Oracle Corporation.

All Rights Reserved.
Go To Table Of Contents
Contents
Go To Index
Index