15
Building a Wrapped Field Report

Figure 15-1 Wrapped field report output

Text description of the illustration orbrwrap_fin.gif

Reports Builder enables you to modify the look of your report in multiple ways. In this example, you will build a break report where the line wraps on word boundaries if it is too long to fit on one line.

Concepts

This report contains a field which is of a fixed horizontal width, but can expand vertically if the contents of the break field are longer than the specified width. The field's contents are not truncated; rather, the contents will wrap within the specified width of the field, and the field will expand vertically.

For more information on break reports, refer to the Reports Builder online help (choose Index, then type "break report" in the box).

Data Relationships

• This is a simple, one-query break report. In addition, you'll add three summary columns.

Layout

• This report is formatted as a master/detail report. You'll modify the layout to add space between sets of information, then change the width of a field so that the break field will wrap.

• Optional: You'll change the format masks of three fields. You'll also use Page Protect in this report. Page Protect causes all objects within a frame or repeating frame to remain together. By specifying Page Protect for the master repeating frame, the customer name and all of its related records will always appear on the same page.

Example Scenario

This example is organized like a simple break report: one query and two groups, one of which is user-created. In addition, you'll create three summary columns.

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

Table 15-1 Features demonstrated in this example

Feature	Location
Manually create a query.	Section 15.2, "Create a query in the Data Model view"
Create three summary columns to calculate various totals and percentages for your report.	Section 15.3, "Create three summary columns"
Create the layout for your paper report.	Section 15.4, "Create the default layout using the Report Wizard"

15.1 Prequisites for this example

To build the example in this chapter, you must have access to the sample schema provided with the Oracle9i database. If you don't know if you have access to this sample schema, contact your database administrator. You should have access to the "Order Entry" portion of the schema to complete this example.

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 dialog box, 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

Text description of the illustration orbrwrap_dm.gif

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

Text description of the illustration orbrwrap_dm.gif

8. Double-click the new group (G_1) the display the Property Inspector, and set 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

Text description of the illustration orbrwrap_dmgpname.gif

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

Text description of the illustration orbrwrap_dmcols.gif

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

Text description of the illustration orbrwrap_layout.gif

Notice how the complete names do not display. The steps in the next section will show you how to quickly and easily 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 16-3, 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, click the R_G_NAME node under Paper Layout > Main Section > Body, then press F4 to display the Property Inspector.

2. Under Repeating Frame, change the Vert. Spacing 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. Click the F_CUST_LAST_NAME field, then press F4 to display the Property Inspector for that field.

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

Text description of the illustration orbrwrap_fin.gif

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 Reports Builder online help, which you can access in two ways:

• From the Oracle Technology Network (http://otn.oracle.com/products/reports/), click Documentation and navigate to the Reports Builder Online Help for the most recent, hosted online help.

• From Reports Builder, choose Help > Help Contents.

  Note: You can replace your Reports Builder online help with the most recent update by downloading the latest online help set available on the Oracle Technology Network (http://otn.oracle.com/docs/products/reports/). Instructions for replacing your help file are included in the readme.txt in the download file.