15 Building a Wrapped Field Report

15.1 Prerequisites for this example

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

15.2 Create a query in the Data Model view

When you create a report, you can either use the Report Wizard to assist you or create the report yourself. In this example, you will use the Data Model view to create your two queries, then use the tool palette to create a data link between the two queries to relate the data tables.

To create the query:

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

2. In the Welcome or New Report dialog box, select Build a new report manually, then click OK.

3. 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 to display the SQL Query Statement dialog box.

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

   SELECT ALL CUSTOMERS_A1.CUST_FIRST_NAME, 
     CUSTOMERS_A1.CUST_LAST_NAME, ORDERS.ORDER_ID, ORDERS.ORDER_TOTAL
   FROM CUSTOMERS CUSTOMERS_A1, ORDERS
   WHERE (ORDERS.CUSTOMER_ID = CUSTOMERS_A1.CUSTOMER_ID) 
   ORDER BY CUSTOMERS_A1.CUST_LAST_NAME
   

   
   

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

   
   

5. Click OK to display the data model for your new query in the Data Model view. It should look like this:

   Figure 15-2 Data Model for the query

   
   Description of "Figure 15-2 Data Model for the query"
   
   

6. In the G_CUST_FIRST_NAME group, click CUST_FIRST_NAME and drag it above the group to create another group.

7. Click CUST_LAST_NAME, then drag it into the new group, so that the data model now looks like this:

   Figure 15-3 Data model with groups

   
   Description of "Figure 15-3 Data model with groups"
   
   

8. Double-click the new group (G_1) to display the Property Inspector, and set the following properties:

   • Under General Information, set the Name property to G_Name.

   Your data model should now look like this:

   Figure 15-4 Data model with new G_Name group

   
   Description of "Figure 15-4 Data model with new G_Name group"
   
   

9. Save your report as wrappedbreak_your_initials.rdf.

15.3 Create three summary columns

The steps in this section show you how to use the Summary Column tool in the Data Model view to create three summary columns. These columns will calculate the percentage of each order total that the customer has purchased, the total purchases the customer has made, and the percentage of the total sales of all customers.

To create the summary columns:

1. In the Data Model view, click the Summary Column tool in the tool palette, then click in the G_CUST_FIRST_NAME group to create a summary column.

2. 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 PCT.

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

3. Create a second summary column in group G_NAME with the following properties:

   • Under General Information, set the Name property to CUST_TOTAL.

   • Under Summary, set the Function property to Sum, set the Source property to ORDER_TOTAL, and set the Reset At property to G_NAME.

4. Create a third summary column in group G_NAME with the following properties:

   • Under General Information, set the Name property to PCT_TOTAL_SALES.

   • Under Summary, set the Function property to Sum, set the Source property to PCT, and set the Reset At property to G_NAME.

   Your data model should now look something like this:

   Figure 15-5 Data Model with Three Summary Columns

   
   Description of "Figure 15-5 Data Model with Three Summary Columns"
   
   

5. Save your report as wrappedbreak_your_initials.rdf.

15.4 Create the default layout using the Report Wizard

The steps in this section show you how to use the Report Wizard to create the layout and choose how your data will display in your report. Here, you will choose the style of report you want to create, and choose to display the data across the report (hence creating the across group report).

You can create a default layout using the Report Wizard, which deletes any existing layouts in your report and replaces it with the new one.

To create the default layout:

1. In the Data Model view, right-click on the canvas, then choose Report Wizard.

2. In the Report Wizard, on the Report Type page, select Create Paper Layout only.

3. On the Style page, select Group Above.

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

5. On the Labels page, change the field widths as follows:

   Fields	Width
   CUST_FIRST_NAME	2
   CUST_LAST_NAME	2

   
   

6. Click Finish to display your report output in the Paper Design view. It should look something like this:

Figure 15-6 Paper Design view for the wrapped field report

Description of "Figure 15-6 Paper Design view for the wrapped field report"

Notice how the complete names do not display. The steps in the next section will show you how to quickly correct this issue.

15.5 Modify the layout of the report

In this section, you will change the field to expand vertically if the contents of the break field are longer than the specified width. As you can see in Figure 15-6, the width of the fields are fixed, but the names are incomplete. You will also add space to display between each record in the report.

To modify the layout:

1. In the Object Navigator, double-click the repeating frame icon next to the R_G_NAME node under Paper Layout > Main Section > Body > M_G_Name_GRPFR, to display the Property Inspector.

2. Under Repeating Frame, change the Vert. Space Between Frames to 0.25, then press Enter.

3. Close the Property Inspector.

4. In the Object Navigator, under the Paper Layout node, navigate to Main Section > Body > M_G_NAME_GRPFR > R_G_NAME.

5. Double-click the icon next to the F_CUST_LAST_NAME field to display the Property Inspector.

6. Under General Layout, make sure the Vertical Elasticity property is set to Expand, then close the Property Inspector to accept your changes.

7. Follow steps 5 and 6 for the F_CUST_FIRST_NAME field.

You have now modified the layout of your report to display all the text in the wrapped fields, and added space between the records.

15.6 Run your report to paper

In this section, you will run your report to the Paper Design view so you can see how your report displays.

Click the Paper Design button in the toolbar to display the Paper Design view. Your report runs, then displays in the Paper Design view. It should look like the following:

Figure 15-7 Paper Design view for the wrapped field report

Description of "Figure 15-7 Paper Design view for the wrapped field report"

Save your report as wrappedbreak_your_initials.rdf.

15.7 Summary

Congratulations! You have successfully created a wrapped field paper report. You now know how to:

• manually create a data model.

• create a master/detail (or group above) report using the Report Wizard.

• modify the layout of your report to wrap the text in a field.

• add space between records.

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