Oracle9i Reports Developer Volume 1

Oracle9i Reports Developer: Build Reports Volume 2 • Instructor Guide .......................................................................................

D12997GC10 Production 1.0 June 2002 D34781

Author

Copyright  Oracle Corporation, 2002. All rights reserved.

Ellen Gravina

Cem Ayberkin

This documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable:

Christian Bauwens

Restricted Rights Legend

Alena Bugarova

Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c) (1) (ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988).

Technical Contributors and Reviewers

Laurent Dereac Warren Lord Marcelo Manzano Darren McBurney Paul Narth Daphne Nougier Miyuki Ohsato Ligia Jasmin Robayo Jim Safcik Rie Saitoh Sarah Spicer Martijn van der Bruggen Vanessa Wang Philipp Weckerle Ted Witiuk

Publisher

John B Dawson

This material or any portion of it may not be copied in any form or by any means without the express prior written permission of Oracle Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties. If this documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with “Restricted Rights,” as defined in FAR 52.227-14, Rights in Data-General, including Alternate III (June 1987). The information in this document is subject to change without notice. If you find any problems in the documentation, please report them in writing to Education Products, Oracle Corporation, 500 Oracle Parkway, Box SB-6, Redwood Shores, CA 94065. Oracle Corporation does not warrant that this document is error-free. Oracle is a registered trademark and all references to Oracle and Oracle products are trademarks or registered trademarks of Oracle Corporation. All other products or company names are used for identification purposes only and may be trademarks of their respective owners.

Contents .....................................................................................................................................................

Contents Preface Profile xvi Resources xvii Typographic Conventions xviii Introduction Course Objectives I-3 Course Content I-5 Lesson 1: Introduction to Oracle9i Reports Developer Overview 1-3 What Is Business Intelligence? 1-5 Enterprise Reporting 1-7 Web Publishing and Paper Publishing 1-11 Oracle9i Reports Developer 1-13 Oracle9i Products 1-15 What Is Oracle9i Reports Developer? 1-21 Summary 1-25 Lesson 2: Designing and Running Reports Overview 2-3 Designing Reports 2-5 Design Considerations 2-9 Retrieving and Sharing Data 2-17 Running a Report 2-19 Previewing Reports 2-21 Summary 2-27 Practice Session Overview: Lesson 2 2-29 Practice Session: Lesson 2 2-30 Lesson 3: Working in Oracle9i Reports Developer Overview 3-3 Reports Developer Executables 3-5 Reports Builder 3-9

..................................................................................................................................................... Oracle9i Reports Developer: Build Reports iii

Contents .....................................................................................................................................................

Reports Builder Components 3-13 Navigating Around the Oracle9i Reports Main Menu Object Categories 3-23 Customizing Your Oracle9i Reports Session 3-33 Oracle9i Reports Environment Variables 3-35 Reports Builder Help Options 3-37 Summary 3-39 Practice Session Overview: Lesson 3 3-41 Practice Session: Lesson 3 3-42

3-15

Lesson 4: Creating a Paper Report Overview 4-3 Invoking Reports Builder and the Report Wizard 4-7 Creating a Tabular Report Using the Wizard 4-9 Creating Other Report Styles 4-27 Previewing a Paper Report in a Web Browser 4-37 Summary 4-39 Practice Session Overview: Lesson 4 4-41 Practice Session: Lesson 4 4-42 Lesson 5: Enhancing a Basic Paper Report Overview 5-3 What Is the Paper Design View? 5-5 Modifying a Report in the Paper Design View 5-7 Summary 5-23 Practice Session Overview: Lesson 5 5-25 Practice Session: Lesson 5 5-26 Lesson 6: Managing Report Templates Overview 6-3 Using the Report Templates 6-5 Modifying a Template 6-7 Adding Web Links to a Template for Report HTML Output 6-13

..................................................................................................................................................... iv Oracle9i Reports Developer: Build Reports

Contents .....................................................................................................................................................

Predefining Your Own Templates 6-15 Summary 6-19 Practice Session Overview: Lesson 6 6-21 Practice Session: Lesson 6 6-22 Lesson 7: Creating a Web Report Overview 7-3 JavaServer Page (JSP) Technology 7-5 Creating a Web Report Using the Report Wizard Web Source View 7-11 Generating Output 7-17 Summary 7-19 Practice Session Overview: Lesson 7 7-21 Practice Session: Lesson 7 7-22

7-9

Lesson 8: Enhancing Reports Using the Data Model: Queries and Groups Overview 8-3 Describing the Report Data Model 8-5 Modifying Properties of a Query 8-7 Changing the Group Structure 8-15 The Group Hierarchy 8-17 Ordering Data in a Group Hierarchy 8-19 Filtering Data in a Group 8-23 Summary 8-25 Practice Session Overview: Lesson 8 8-27 Practice Session: Lesson 8 8-28 Lesson 9: Enhancing Reports Using the Data Model: Data Sources Overview 9-3 The Data Source Types 9-5 Pluggable Data Sources 9-7 Using XML as a Data Source 9-9 XML Example 9-11 Adding New Data 9-13

..................................................................................................................................................... Oracle9i Reports Developer: Build Reports v

Contents .....................................................................................................................................................

Using REF Cursor Queries 9-15 Using Data Links to Structure Data 9-19 Efficiency Issues: Multiple- or Single-Query Hierarchy Summary 9-27 Practice Session Overview: Lesson 9 9-29 Practice Session: Lesson 9 9-30

9-25

Lesson 10: Enhancing Reports Using the Data Model: Creating Columns Overview 10-3 The Data Model Column Types 10-5 Maintaining Data Source Columns 10-7 Using a Data Source Column to Output Contents of a File 10-9 Creating a Column 10-11 Creating Summary Columns 10-13 Example Report Displaying Subtotals 10-15 Example Report Displaying Percentages 10-17 Creating Formula Columns 10-21 Creating Placeholder Columns 10-23 Example Report to Populate a Placeholder 10-25 Summary 10-27 Practice Session Overview: Lesson 10 10-29 Practice Session: Lesson 10 10-30 Lesson 11: Enhancing Reports Using the Paper Layout Overview 11-3 Viewing the Paper Layout 11-5 Designing Multipanel Reports 11-9 Recognizing Layout Objects 11-13 The Paper Layout Layers 11-15 Creating Layout Objects 11-21 The Paper Layout Tools 11-23 Report Bursting 11-25 Creating Variable Length Lines 11-35 Summary 11-37 Practice Session Overview: Lesson 11 11-39 Practice Session: Lesson 11 11-40

..................................................................................................................................................... vi Oracle9i Reports Developer: Build Reports

Contents .....................................................................................................................................................

Lesson 12: Controlling the Paper Layout: Common Properties Overview 12-3 Modifying Paper Layout Object Properties 12-5 Modifying Common Layout Properties 12-7 Sizing Objects 12-9 Anchors 12-11 Layout Object Relationships 12-13 Using Format Triggers 12-25 Layout Object Properties for Web Support 12-27 Summary 12-29 Practice Session Overview: Lesson 12 12-31 Practice Session: Lesson 12 12-32 Lesson 13: Controlling the Paper Layout: Specific Properties Overview 13-3 Modifying Properties of a Repeating Frame 13-5 Print Direction 13-7 Minimum Widow Records 13-13 Column Mode 13-15 Modifying Properties of a Field 13-17 Displaying File Contents with a File Link 13-25 Summary 13-31 Practice Session Overview: Lesson 13 13-33 Practice Session: Lesson 13 13-34 Lesson 14: Web Reporting Overview 14-3 High Quality Web Publishing 14-5 Creating Reports for the Web 14-7 Adding Dynamic Content to a Web Page 14-9 Creating a Report Block 14-11 Examining the Web Source Code 14-13 Customizing Reports JSPs 14-17 Summary 14-23 Practice Session Overview: Lesson 14 14-25 Practice Session: Lesson 14 14-26

..................................................................................................................................................... Oracle9i Reports Developer: Build Reports vii

Contents .....................................................................................................................................................

Lesson 15: Extending Functionality Using XML Overview 15-3 Why Use XML Report Definitions? 15-5 Creating and Using XML Report Definitions 15-7 Running XML Report Definitions 15-17 Debugging XML Report Definitions 15-21 Summary 15-23 Practice Session Overview: Lesson 15 15-25 Practice Session: Lesson 15 15-26 Lesson 16: Creating and Using Report Parameters Overview 16-3 Creating User Parameters 16-5 Referencing Parameters in a Report Query 16-7 Hints and Tips for Referencing Parameters 16-13 Creating a List of Values 16-15 Referencing System Parameters 16-19 Building a Paper Parameter Form 16-23 Using Parameter Form HTML Extensions 16-27 Summary 16-31 Practice Session Overview: Lesson 16 16-33 Practice Session: Lesson 16 16-34 Lesson 17: Embedding a Graph in a Report Overview 17-3 Creating and Embedding a Graph in a Report 17-5 Using a Graph Hyperlink 17-13 Summary 17-15 Practice Session Overview: Lesson 17 17-17 Practice Session: Lesson 17 17-18 Lesson 18: Enhancing Matrix Reports Overview 18-3 The Matrix Data Model 18-5 The Matrix Paper Layout 18-7 Creating Matrix Summaries 18-9

..................................................................................................................................................... viii Oracle9i Reports Developer: Build Reports

Contents .....................................................................................................................................................

Creating the Simple Matrix Report Manually 18-11 Building a Matrix with Group Report 18-13 Building a Nested Matrix 18-15 Summary 18-23 Practice Session Overview: Lesson 18 18-25 Practice Session: Lesson 18 18-26 Lesson 19: Coding PL/SQL Triggers Overview 19-3 Types of Triggers in Reports 19-5 Using Report Triggers 19-7 Using Data Model Triggers 19-11 Using Layout Triggers 19-15 Writing Common Code 19-25 Publish Reports Based on Database Events 19-27 Summary 19-31 Practice Session Overview: Lesson 19 19-33 Practice Session: Lesson 19 19-34 Lesson 20: Extending Functionality Using the SRW Package Overview 20-3 Contents of the Reports Builder Built-in Package 20-5 Outputting Messages 20-7 Executing a Nested Report 20-9 Restricting Data 20-13 Initializing Fields 20-15 Performing DDL Statements 20-17 Setting Format Attributes 20-19 Summary 20-25 Practice Session Overview: Lesson 20 20-27 Practice Session: Lesson 20 20-28 Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services Overview 21-3 Why Use Oracle9iAS Reports Services? 21-5 Oracle9iAS Reports Services Architecture 21-9

..................................................................................................................................................... Oracle9i Reports Developer: Build Reports ix

Contents .....................................................................................................................................................

Oracle9iAS Reports Services Runtime Architecture 21-13 Making a Request to Run a Report 21-15 Managing and Monitoring Oracle9iAS Reports Services 21-23 Summary 21-31 Practice Session Overview: Lesson 21 21-33 Practice Session: Lesson 21 21-34 Lesson 22: Building Reports: Efficiency Guidelines Overview 22-3 Tuning Oracle9i Reports 22-5 Different Development and Run-Time Environments 22-19 Developing Reports to Run in Different GUIs 22-21 Building Reports to Run in Different Languages 22-23 Summary 22-27 Practice Session Overview: Lesson 22 22-29 Practice Session: Lesson 22 22-30 Appendix A: Solutions Practice Session Overview: Lesson 2 Practice Session Solutions: Lesson 2 Practice Session Overview: Lesson 3 Practice Session Solutions: Lesson 3 Practice Session Overview: Lesson 4 Practice Session Solutions: Lesson 4 Practice Session Overview: Lesson 5 Practice Session Solutions: Lesson 5 Practice Session Overview: Lesson 6 Practice Session Solutions: Lesson 6 Practice Session Overview: Lesson 7 Practice Session Solutions: Lesson 7 Practice Session Overview: Lesson 8 Practice Session Solutions: Lesson 8 Practice Session Overview: Lesson 9 Practice Session Overview: Lesson 10 Practice Session Solutions: Lesson 10

A-3 A-4 A-8 A-9 A-11 A-12 A-18 A-19 A-23 A-24 A-27 A-28 A-30 A-31 A-33 A-40 A-41

..................................................................................................................................................... x Oracle9i Reports Developer: Build Reports

Contents .....................................................................................................................................................

Practice Session Overview: Lesson 11 Practice Session Solutions: Lesson 11 Practice Session Overview: Lesson 12 Practice Session Solutions: Lesson 12 Practice Session Overview: Lesson 13 Practice Session Solutions: Lesson 13 Practice Session Overview: Lesson 14 Practice Session Solutions: Lesson 14 Practice Session Overview: Lesson 15 Practice Session Solutions: Lesson 15 Practice Session Overview: Lesson 16 Practice Session Solutions: Lesson 16 Practice Session Overview: Lesson 17 Practice Session Solutions: Lesson 17 Practice Session Overview: Lesson 18 Practice Session Solutions: Lesson 18 Practice Session Overview: Lesson 19 Practice Session Solutions: Lesson 19 Practice Session Overview: Lesson 20 Practice Session Solutions: Lesson 20 Practice Session Overview: Lesson 21 Practice Session Solutions: Lesson 21 Practice Session Overview: Lesson 22 Practice Session Solutions: Lesson 22

A-45 A-46 A-52 A-53 A-55 A-56 A-58 A-59 A-61 A-62 A-63 A-64 A-67 A-68 A-72 A-73 A-77 A-78 A-83 A-84 A-93 A-94 A-95 A-96

Appendix B: Table Descriptions Glossary

..................................................................................................................................................... Oracle9i Reports Developer: Build Reports xi

Contents .....................................................................................................................................................

..................................................................................................................................................... xii Oracle9i Reports Developer: Build Reports

14 ................................

Web Reporting

Lesson 14: Web Reporting .....................................................................................................................................................

Objectives

After completing this lesson, you should be able to do the following: • Identify the attributes of a high quality Web report • • • • •

Describe the JavaServer Page run-time environment Describe static and dynamic Web reporting Add dynamic content to a Web page Identify Reports custom JSP tags Customize Web reports

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Topic Timing Lecture 45 minutes Practice 30 minutes Total 75 minutes

...................................................................................................................................................... 14-2 Oracle9i Reports Developer: Build Reports

Overview .....................................................................................................................................................

Overview Introduction Using Oracle9i Reports, you can easily publish data from any source, in any format, with high fidelity to the Web. Earlier in this course, you learned how to build a simple Web report using the Report Wizard and a standard HTML template supplied by Oracle9i Reports. You can also use your favorite HTML authoring tool to build the static portion of your HTML page and then add dynamic content to the Web page using the Report Block Wizard in Oracle9i Reports. In this lesson, you learn more about building reports for the Web and how to customize and enhance your reports using style sheets and the Web Source editor. Objectives After completing this lesson, you should be able to do the following: • Identify the attributes of a high quality Web report • Describe the JavaServer Page run-time environment • Describe static and dynamic Web reporting • Add dynamic content to an HTML page using the Report Block Wizard • Identify the Reports custom JSP tags • Customize Web reports using style sheets • Customize Web reports using the Web Source editor

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 14-3

Lesson 14: Web Reporting .....................................................................................................................................................

High Quality Web Publishing

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Point out some of the features in the report above: • Alternating row colors • Format masks • Use of a Web service to dynamically generate the values for the Local Currency column If asked, this functionality was implemented by creating a Java class and importing it into the report. Specifically, the Java class was creating using JDeveloper. The class called the Web service and returned the desired result. The class was then imported into the report using the Java Importer. For more information see OTN. • Web headings with icons (Mail and Help) • Navigation tab on the left

...................................................................................................................................................... 14-4 Oracle9i Reports Developer: Build Reports

High Quality Web Publishing .....................................................................................................................................................

High Quality Web Publishing Introduction HTML is the de facto standard for publishing information on the Web. The organization of content for the Web must be more visually appealing than paper, and often includes animated GIF images, JavaScript code, and Java applets. We are concerned with publishing information from corporate data sources and the tool we use must be capable of combining static as well as dynamic information in a single page. A number of Web authoring tools exist today for designing and developing high quality Web pages. These products address static publishing, and in order to include dynamic content, you often must resort to programming. Web designers and developers are challenged to define and generate complex, dynamic content for an enterprise information portal or corporate Web page. Oracle9i Reports is a powerful enterprise reporting tool for building and publishing high quality, dynamically generated Web reports. Through data-driven tables of contents, hyperlinks, and drill-down chart hyperlinks, Oracle9i Reports provides users with an easy path to the information they require. JSP-Based Runtime Environment JavaServer Pages (JSP) technology allows Web designers and developers to rapidly develop and easily maintain information-rich, dynamic Web pages that leverage existing business systems. Oracle9i Reports introduced JSPs as the underlying technology with which you build and publish dynamic reports for the Internet. You can extend your Web pages by embedding Oracle9i Reports custom JSP tags and your own data-driven Java components into an HTML document. You can integrate Reports custom JSP tags with Reports data model objects and other Reports tags to add dynamic report output to your Web page. These tags use the data model, stored in XML, to produce dynamic output, or invoke JavaBeans that use the data model to create their results. For example, the three-dimensional (3-D) graphing component uses a custom tag that passes Reports data to the BI Graph Bean, which creates an image of the graph. The 3-D graphics custom tag then returns HTML that references the created image. Reusable components and the open architecture of Oracle9i Reports makes previously difficult tasks simple to implement. The ability to incorporate existing Java components into a report using JSP and servlet technology provides openness. You can also share business components between Oracle9i Reports and other applications within your organization.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 14-5

Lesson 14: Web Reporting .....................................................................................................................................................

Comparing Static and Dynamic Reporting

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 14-6 Oracle9i Reports Developer: Build Reports

Creating Reports for the Web .....................................................................................................................................................

Creating Reports for the Web The two categories of Web reports are static reports and dynamic reports. Static Reports The word static describes something that is fixed and unchanging. A static report is one that displays the same data each time the report is invoked. The data is fetched once and displayed on a Web page. Consider a paper report that has been pushed to the Web. If you do not execute the paper report (RDF) again, and generate the Web output (HTML or PDF) using the Web Wizard or File > Generate to File, the output remains the same, that is, it is static. Static reporting is sufficient for certain types of applications. A report that displays data that does not change, such as historical data, does not require frequent refreshing. Dynamic Reports If your requirement is to publish the most recent data available when the information is requested, a static report will not meet your needs. Your report must be dynamic. The word dynamic describes something marked with continuous and productive activity or change. Pertaining to software applications, dynamic refers to operations performed on the fly that are based on decisions made while a program is executing, instead of beforehand. To achieve this in Oracle9i Reports, you embed a report block within a Web page. Each time the report is executed, the data is fetched from the data source and the Web page displays the most current information. In this lesson, you will add dynamic content to a Web page.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 14-7

Lesson 14: Web Reporting .....................................................................................................................................................

Adding Dynamic Content

Report Block Wizard Graph Wizard

Data Model Web Source

JavaServer Page (JSP)

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Demonstration Open the emprev.htm page, in order to add report data. Define the Data Model to retrieve department managers and their salaries. 1 Display the Data Model view and invoke the Data Wizard using the right-mousebutton menu. 2 Define the query using Query Builder: DEPARTMENTS: department_name EMPLOYEES: first_name, last_name, job_id, salary 3 Group by DEPARTMENT_ID. Finish the Wizard. Now create the Report Block and add it to the Web page: 4 Display the Web Source view. Locate the “Your report block goes here” comment. 5 Select Insert > Report Block. Select Group Left, display both groups (down direction), display all fields, and select the Beige template. Click Finish. 6 Run the Web layout. Display the Web source again and show the students the code Reports Builder created.

...................................................................................................................................................... 14-8 Oracle9i Reports Developer: Build Reports

Adding Dynamic Content to a Web Page .....................................................................................................................................................

Adding Dynamic Content to a Web Page Introduction In a previous lesson, you created Web reports using one of the HTML templates supplied by Oracle9i Reports and the Report Wizard. As an alternative, you can open an existing Web page (an HTML document) in Reports Builder and embed your report data into the page. Your HTML document can be a corporate standard or you can design your own Web page using your favorite HTML authoring tool. In this section, you learn how to add dynamic content to an existing Web page. Defining the Data Model Remember that every report has a data model and a layout. In Oracle9i Reports, each report definition can have a paper layout and a Web layout. Although the layouts are distinct objects, the data model is shared by both layouts. Open the existing HTML file in Reports Builder. Follow these steps to define a data model for the Web page: 1 In the Object Navigator, double-click the Data Model node to display the Data Model view of the Report Editor. 2 Right-mouse-click in the Data Model painter area and select Data Wizard from the menu. 3 Name the query as required. 4 Select the Query type. 5 Enter the query as you would using the Report Wizard. 6 Create additional groups as required. 7 Create summaries as required. Saving Your Report as a JSP File 1 With your report selected in the Object Navigator, select File > Save As. 2 Make sure the file type is Reports JSP (*.jsp), modify the report name if desired, and click Save.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 14-9

Lesson 14: Web Reporting .....................................................................................................................................................

Creating a Report Block

Copyright © Oracle Corporation, 2002. All rights reserved.

Report Block Wizard

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 14-10 Oracle9i Reports Developer: Build Reports

Creating a Report Block .....................................................................................................................................................

Creating a Report Block Introduction Now that you have defined the data for your report, you are ready to create a report block and add it to your Web page. The report block automatically inserts the data model, business logic, and Web layout for the report. Each time the report is run, the report block will dynamically pull the data defined by the data model into your Web page. Modifying the Web Source Code In order to insert a report block into your Web page, open the Web Source view for your report. The report block must go between the and tags. These tags are standard HTML code and delimit the start and end of the body of the Web page, respectively. Organizations often have a team of Web designers to create visually appealing and effective Web pages for publishing. The designers can manually code the HTML or use any HTML authoring tool. Web designers will typically include a placeholder in the HTML code to indicate where you, as the reports developer, must add the report data. This is the location in the code where you position your cursor to insert the report block. Example:

Note: < and > represent the less than and greater than signs, respectively. Reports Builder encodes them in the Web Source to ensure that a browser will not misinterpret the symbols as HTML elements. The line of code would actually read:

Invoking the Report Block Wizard 1 Position your cursor in the desired location in the Web source code. 2 Select Insert > Report Block from the menu. 3 Specify a title for your report and select a report style. 4 Select the group or groups as desired. 5 Select the fields you want to display in your report. 6 Modify the labels as necessary. 7 Select a template and click Finish. Note: The template affects the report block only and will not alter the existing Web page.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 14-11

Lesson 14: Web Reporting .....................................................................................................................................................

Structure of a Reports JSP

<%@ taglib uri="/WEB -INF/lib/reports_tld.jar" prefix="rw" %> <%@ page language="java" import="java.io.*" errorPage="rwerror.j sp" %>

Data Model & Business Logic (XML) Paper Layout (XML) Web Layout (JSP) Graph (XML) Copyright © Oracle Corporation, 2002. All rights reserved.

Examining the Web Source Code

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 14-12 Oracle9i Reports Developer: Build Reports

Examining the Web Source Code .....................................................................................................................................................

Examining the Web Source Code Introduction In this section, you will examine the code created for the report block, and learn more about the Reports custom JSP tags as well as some HTML tags. The Structure of a Reports JSP Earlier in the course, you were introduced to the structure of a Reports JSP. The first line of the JSP is a directive that identifies the Reports custom tag library. The page directive on the second line of code identifies the scriptlet language, the Java classes to import, and the JSP to display any unhandled errors. The entire report resides within the body of the rw:report tag. The Data Model and business logic reside within the rw:objects tag and are coded in XML. If your report definition also contains a Paper Layout, it is located within the rw:objects tag as well. Remember that the details of the Data Model and Paper Layout are hidden when you are viewing the Web Source in Reports Builder. Use a text editor to view the complete code. The Web Layout for your report is located within the HTML tag, which is the focus of this section. The Header Tag The header tag is an HTML tag that contains a comment to help you locate the heading information for your report: . Just below the header tag, you will find and tags. The code within these tags defines the column headers for your report. For example: class="OraColumnHeader"> Employee ID This code identifies the column heading Last Name. Note the tag. The rw:id tag generates a unique Document Object Model (DOM), or HTML ID value for row and column headers to produce HTML that provides access using assistive technology, such as screen readers.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 14-13

Lesson 14: Web Reporting .....................................................................................................................................................

rw:foreach Tag

... . . . . . . ...

Copyright © Oracle Corporation, 2002. All rights reserved.

rw:field Tag

... class="OraCellNumber"> F_EMPLOYEE_ID ...

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 14-14 Oracle9i Reports Developer: Build Reports

Examining the Web Source Code .....................................................................................................................................................

The rw:foreach Tag The rw:foreach tag loops through the specified data source group. The tag creates a cursor for the data source group and moves the cursor to the next instance for each iteration. Consider this example: R_G_EMPLOPYEE_ID_1 is a unique identifier for the loop. G_EMPLOYEE_ID identifies the group in the data model for which the loop repeats. The rw:field Tag The rw:field tag returns a value from a column, system parameter, or user parameter, and outputs the value in HTML. For example: F_EMPLOYEE_ID F_EMPLPOYEE_ID is a unique identifier for this field. This tag returns a value from the EMPLOYEE_ID column in the data model, as identified by the source (src) attribute. If the tag returns null, a space will result in the output, as specified by the nullValue attribute. The   value is the code for “non-breaking space.” It is called non-breaking so that the browser does not ignore the space, which browsers usually do otherwise.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 14-15

Lesson 14: Web Reporting .....................................................................................................................................................

Customizing Reports JSPs

• Modify the classes in the style sheet • Use HTML tags and attributes • Use Reports custom tags

Copyright © Oracle Corporation, 2002. All rights reserved.

Customizing Reports JSPs Using Style Sheets … class="OraCellNumber"> F_EMPLOYEE_ID

.OraCellNumber { color: #000000; background-color: #f7f7e7; font-family: Arial, Helvetica, sans-serif; font-size: 10px ; textalign: right}

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 14-16 Oracle9i Reports Developer: Build Reports

Customizing Reports JSPs .....................................................................................................................................................

Customizing Reports JSPs Introduction You can customize your Reports JSPs by: • Modifying the classes in the style sheet file • Using standard HTML tags and attributes • Using Oracle9i Reports custom tags Using Style Sheets When you select a template supplied by Oracle9i Reports, the headings and data in your report block inherit the characteristics defined for the template through the template’s style sheet. A style sheet is a file that is used to store margins, tabs, fonts, headers, footers, and other layout settings for a particular category of document. Style sheets (or cascading style sheets) refer to HTML extensions to provide powerful formatting flexibility. When a style sheet is selected, its format settings are applied to all the documents created with it. This saves the page designer or programmer from redefining the same settings over again for each page. Example: In the Reports JSP source code shown, the Oracle9i Reports template rwbeige is referenced. Specifically, the style sheet referenced is rwbeige.css. The EMPLOYEE_ID field in the report uses the OraCellNumber class format. To learn the attributes of a particular class format, you can view the .css file in a text editor. In this example, the OraCellNumber class format is: .OraCellNumber {color: #000000; background-color: #f7f7e7; font-family: Arial, Helvetica, sans-serif; font-size: 10px ; text-align: right} The attributes defined are the color of the field text, background color of the field, font, font size, and text alignment. To customize the appearance of a class in all your reports, edit the class format settings in the stylesheet.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 14-17

Lesson 14: Web Reporting ..................................................................................................................................................... U\

Customizing Reports JSPs Using HTML Tags and Attributes

class="OraColumnHeader"> Employee Id … align="right" bgcolor="yellow"> Salary

Copyright © Oracle Corporation, 2002. All rights reserved.

Customizing Reports JSPs Using Reports Custom Tags Tag attributes provide additional formatting functionality. Example: rw:field tag optional attributes: • • • • • •

breakLevel breakValue nullValue containsHTML formatMask formatTrigger

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 14-18 Oracle9i Reports Developer: Build Reports

Customizing Reports JSPs .....................................................................................................................................................

Customizing Particular Instances of a Class If you want to customize particular instances of a class in a report, you can edit the Web source code directly and use standard HTML tags and attributes or Reports custom tags to obtain the desired results. Using HTML Tags and Attributes If you are familiar with standard HTML tags and attributes, you can edit your Web source and customize your report this way. In the top slide displayed, the column headings in the report block use the OraColumnHeader class. Note the reference for the Employee Id header. The header for the Salary column is customized. The OraColumnHeader class reference was deleted from the Web source code, and replaced with standard HTML format attributes for alignment and background color. In this example, the Salary column header is aligned right and the header text is displayed with a background color of yellow. Using Reports Custom Tags Each Reports custom tag is defined with a list of attributes. While some attributes are mandatory, others are optional. You can edit the Web source and include optional attributes to customize your Reports JSP. The optional attributes provide additional formatting for the referenced object. For example, the rw:field tag can be defined with the following optional attributes: Attribute breakLevel breakValue nullValue containsHTML

formatMask

formatTrigger

Description The frequency at which the field should be repeated. It is the name of an enclosing rw:foreach tag. The string value to use when the field is not repeated at the specified breakLevel. The default value is  . This sets the value to be used if the field’s value is null. The default value is  . A value that indicates whether the field’s value contains HTML. Accepted values are Yes and No. If the attribute is set to No, HTML reserved characters are converted to their HTML character entities, for example, “<” is converted to “<.” The default value is No. Contains the Reports format mask to be applied to the field’s value. This attribute is only valid for fields containing number or date data types. Contains the name of a format trigger program unit. The executing program unit sets the field’s attributes in the Reports backend structure and the tag handler picks up those attributes and converts them to HTML.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 14-19

Lesson 14: Web Reporting .....................................................................................................................................................

Customizing Reports JSPs Using Reports Custom Tags … align=“right"> F_SALARY …

Copyright © Oracle Corporation, 2002. All rights reserved.

Customizing Reports JSPs

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Continue with the same demonstration. Add a format mask for the salary column. Locate the rw:field tag for F_SALARY and add the : formatMask="$999,999.00" attribute. Run the Web layout to test.

...................................................................................................................................................... 14-20 Oracle9i Reports Developer: Build Reports

Customizing Reports JSPs .....................................................................................................................................................

Using Reports Custom Tags (continued) The slide opposite shows an example of using Reports custom tag attributes to modify the appearance of a field in a report. A format mask is included to display the values returned from the salary column. Use standard Reports format tokens to create format masks for fields containing numbers and dates. Another implementation of customizing Reports JSPs using custom tag attributes is to set your report parameters in the rw:report tag. For example: This rw:report tag references the parameters attribute. The parameter list specifies the name of the server, user name, password, and database connect string, destination type, and destination format. You can run this report using Oracle9iAS Reports Services without providing the parameters again. However, the values specified in the parameters attribute can be overridden by providing new values in the URL when the report is run. The settings in the parameters attribute have the lowest precedence.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 14-21

Lesson 14: Web Reporting .....................................................................................................................................................

Summary

In this lesson, you should have learned how to: • Create a Web report using an existing HTML page and the Report Block Wizard • Describe the structure of a Reports JSP and identify key custom JSP tags • Use style sheets to customize Web reports • Use HTML attributes and Reports custom tag attributes to customize Web reports

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 14-22 Oracle9i Reports Developer: Build Reports

Summary .....................................................................................................................................................

Summary Adding Dynamic Content to a Web Page With Oracle9i Reports, you can open an existing Web page in Reports Builder and add dynamic report data to the page. • Use the Data Wizard to define a data model. • Create a report block at the desired location in the Web page using the Web Source editor and the Report Block Wizard. • Save your report as a Reports JSP. Reports JSPs The Web layout for your report definition is delimited by the HTML tags in the source code. Reports JSP custom tags in this section include rw:foreach, rw:id, and rw:field. Customizing Web Reports Modify the classes in a style sheet file to apply changes to layout settings for all reports created with that style sheet. Using the Web Source editor, customize the appearance of selected classes in a report by using standard HTML tags and attributes as well as Reports custom tag attributes.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 14-23

Lesson 14: Web Reporting .....................................................................................................................................................

Practice 14 Overview

• Adding dynamic content to an existing Web page • Customizing a Web report using JSP tag attributes

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 14-24 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 14 .....................................................................................................................................................

Practice Session Overview: Lesson 14 This practice session contains: • Adding dynamic content to an existing Web page to create a Reports JSP • Customizing a Web report using JSP tag attributes Introduction In this practice, you will create a Web report by adding dynamic report data to an existing HTML page. You will also customize the Web report by using JSP tag attributes.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 14-25

Lesson 14: Web Reporting .....................................................................................................................................................

Practice Session: Lesson 14 1 Create a new Web report by adding dynamic content to an existing Web page.

Open the mywebpage.htm file in Reports Builder. b Define a data model using the Data Wizard. Import the p14q1.sql query. Use DEPARTMENT_ID and DEPARTMENT_NAME as the group fields. Include a sum of the salaries. c Save the report as p14q1.jsp. d Create a report block and add it to the Reports JSP. The Web source code has a place holder for your report block. Choose the Group Left style and include the “Salaries by Department” title. Include all groups and display in the down direction. Display all fields. Use a label of Name for DEPARTMENT_NAME and Title for JOB_ID. Select the predefined Beige template. e Run the Web layout to test. Save the report. 2 Customize the p14q1.jsp report using JSP tag attributes. a Display the salary and summary values with an appropriate currency format. b Run the Web layout to test. Did you remember to format the report summary? Save the report as p14q2.jsp. a

...................................................................................................................................................... 14-26 Oracle9i Reports Developer: Build Reports

15 ................................

Extending Functionality Using XML

Lesson 15: Extending Functionality Using XML .....................................................................................................................................................

Objectives

After completing this lesson, you should be able to do the following: • Describe the benefits of XML definitions • Create XML report definitions • Apply XML report definitions • Debug XML report definitions

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Topic Timing Lecture 40 minutes Practice 15 minutes Total 55 minutes

...................................................................................................................................................... 15-2 Oracle9i Reports Developer: Build Reports

Overview .....................................................................................................................................................

Overview Introduction In an earlier lesson, you were introduced to Extensible Markup Language, or XML. Recall that XML enables business-to-business exchanges of data across applications and platforms. You learned how to use XML as a data source in a report. It is also possible to build a report definition using XML tags. This XML report definition can be run by itself or can be applied to another report at run time to customize the output for a specific audience. In this lesson, you will learn how to build a report using XML tags, apply customizations to a report at runtime, and apply batch update to existing reports. Objectives After completing this lesson, you should be able to do the following: • Describe the benefits of using XML report definitions • Create XML report definitions • Apply XML report definitions to an existing report • Debug XML report definitions

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 15-3

Lesson 15: Extending Functionality Using XML .....................................................................................................................................................

Why Use XML Report Definitions?

• • • •

Apply run-time customizations Perform batch updates Build fully functional reports Product openness

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note A customization file can be applied to an existing .RDF or XML file. For an XML report to run by itself, it must contain a full report definition comprising a full Data Model and layout. File xmlresult.xml contains the XML output for xmlresult.rdf. You may want to generate the XML output yourself (File > Generate to File > XML). The XML file contains the report structure as well as the queried data. It is driven from the Data Model and has no dependency on any defined layout. You can apply an XML customization file to this XML output that would define a visual layout. Technical Note For more information see the section “Customizing Reports with XML” in the Oracle9iAS Reports Services Publishing Reports manual.

...................................................................................................................................................... 15-4 Oracle9i Reports Developer: Build Reports

Why Use XML Report Definitions? .....................................................................................................................................................

Why Use XML Report Definitions? Introduction Using XML tags, you can build a full or partial report that can serve as either a customization file for an existing report or a completely self-contained report. XML customizations enable you to modify reports at runtime without changing the original report. With the addition of the CUSTOMIZE command to your runtime command line, you can call a customization file to add to or change a report's layout or data model. You can even use Reports XML to build a report data model for inclusion in a custom JSP-based report. This functionality opens many possibilities for handling the output of data, making Oracle9i Reports even more flexible and open. Run-Time Customizations You can apply customizations to change the look and feel of a report at run time without changing the original report. This allows for a report to be different for different audiences. For example, labels can be designed according to geography, or colors and format masks can be designed on a per user or user group basis. Furthermore, Oracle9i Reports extends the possible types of Reports XML customizations by enabling you to create an entire Data Model in XML. This includes the creation of multiple data sources, linking between data sources, and group hierarchies within each data source. Data Model support through Reports XML customization means that any Data Model that can be created with Reports Builder can now be created by specifying XML. You can also set properties for Data Model objects using XML. Batch Updates Using an XML report definition, you can apply batch updates to existing reports. You have the option of saving the combined definition to a file. The advantage is that you can quickly update a large number of reports without having to open each file in Reports Builder to make the changes. Building Fully Functional Reports If you can generate XML tags, you can create a fully self-contained report definition that can be run by Oracle9i Reports. Using an XML report definition, you can build reports on the fly without using Reports Builder. Product Openess XML support in Oracle9i Reports provides a mechanism for exchanging data with third-party applications. It also offers a way to furnish data to tools that accept XML output for data analysis, and XML is easily displayed on the Web.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 15-5

Lesson 15: Extending Functionality Using XML .....................................................................................................................................................

Creating XML Report Definitions

• XML tags • Partial definition • Full definition

. . . . . .

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note The Document Type Definition (DTD) files are what give XML tags life and meaning. Oracle9i Reports Developer includes a DTD file that defines the tags that can be used in a report definition. For more information on XML tags, refer to the Reports Builder Reference online Help system. File fullxml.xml contains a full XML report definition. Use the Reports Builder to run this report. Note: A full XML definition contains a data model and a layout. A partial XML definition contains a layout only.

...................................................................................................................................................... 15-6 Oracle9i Reports Developer: Build Reports

Creating and Using XML Report Definitions .....................................................................................................................................................

Creating and Using XML Report Definitions Introduction The steps below briefly outline the process of building and using XML report definitions: 1 Create a full or partial report definition using XML tags. (You can do this manually with an editor or programmatically.) 2 Store the XML report definition in a location accessible to Oracle9iAS Reports Services. 3 Apply the XML definition to another report or run the report by itself. Example The following is a sample of a partial report definition:

Every XML report definition, whether full or partial, must contain the required tag pair, . The tag indicates the beginning of the report customization, its name, and the version of the Document Type Definition (DTD) file that is being used with this XML customization. The tag indicates the end of the report customization. A full report requires both a data model and layout and therefore requires the and tags and their contents. The report name attribute in the tag can be any name you wish, and can be different than the name of the .rdf file. The name attributes in the and

tags match the names of the fields and sections. As a result, the attributes will be applied to the fields and sections listed.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 15-7

Lesson 15: Extending Functionality Using XML .....................................................................................................................................................

Format Modification Example

...

• Empty tag • Use of • Use of

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 15-8 Oracle9i Reports Developer: Build Reports

Creating and Using XML Report Definitions .....................................................................................................................................................

Partial Report Definitions One of the main uses of XML report definitions is to make modifications to an existing report at run time. The XML report definition enables you to easily change the Data Model or report formatting without permanently affecting the original report. To help you understand the kind of modifications that are possible, take a look at some examples: • Formatting modification example:

This XML definition does not add data because the tag is empty. The purpose is to apply the formatting attributes mentioned in the tags for the fields in the report’s main section.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 15-9

Lesson 15: Extending Functionality Using XML .....................................................................................................................................................

Format Exception Example

• Empty tag • Use of • Use of

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 15-10 Oracle9i Reports Developer: Build Reports

Creating and Using XML Report Definitions .....................................................................................................................................................

Partial Report Definitions (continued) • Formatting exception example:

This XML definition adds formatting exceptions to the F_SAL and F_BONUS fields. Notice the use of the tag. However, the formatting change will only be applied when the criteria defined in the tag is met.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 15-11

Lesson 15: Extending Functionality Using XML .....................................................................................................................................................

Data Model Modification Example

...

• • • •

Empty tag Use of Column aliases Bind references



Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 15-12 Oracle9i Reports Developer: Build Reports

Creating and Using XML Report Definitions .....................................................................................................................................................

Full Report Definition • Data model modification example: select l.city loc_name,sum(e.salary) totsal from locations l,departments d,employees e where l.location_id = d.location_id and d.department_id = e.department_id and l.location_id > :loc_nr group by l.city,d.department_name

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 15-13

Lesson 15: Extending Functionality Using XML .....................................................................................................................................................

Data Model Modification Example

...

• • • •

Empty tag Use of Column aliases Bind references



Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 15-14 Oracle9i Reports Developer: Build Reports

Creating and Using XML Report Definitions .....................................................................................................................................................

Full Report Definition (continued) Several things happen in this XML definition file: – First of all, this XML report definition can run by itself because it has both a Data Model and a complete paper layout specification. The purpose of this example is to illustrate how you can add a new query to the Data Model and also add a header section. – Notice the use of the tag. This tag delimits the query in the Data Model and must be nested within the tag. In Oracle9i Reports, the dataSource tag supports the creation of multiple data sources as well as the new pluggable data sources. – The tag delimits the SELECT statement and must be nested within the tag. – Note the presence of a bind reference in the SELECT statement. This will create a user parameter on your behalf. – The query uses column aliases. If you do not use an alias, the name of the column is assigned by default and could be something you do not expect. This is important for the tag, where you must specify the correct name and source of the column. – Notice the presence of the tag. This tag defines the formatting attributes for field labels. Because it is used outside the tag, it is global and pertains to all labels in this tabular layout. Be aware that if there is both a global and local , the local one overrides the global one. – Notice the use of the SUM function in the query. You could use the

tag to define a summary column in the Data Model of the report definition if you wanted. This is a performance issue. If calculations are included in SQL, they are performed before the data is retrieved rather than performed on the retrieved data by Oracle9i Reports.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 15-15

Lesson 15: Extending Functionality Using XML .....................................................................................................................................................

Running XML Report Definitions

• Apply to an .rdf or .xml file rwclient userid=... report=... customize=....xml server=... destype=...

desformat=...

• One or multiple definitions

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Use datamod.xml —which is the example from the previous page— to customize l15cus.rdf using the RWRUN command with the customize option. Note that the query Q_sal_summary contains a bind reference, :loc_nr. Make sure to run l15cus.rdf first so that you can compare the results. Send the ouput to a PDF file.

...................................................................................................................................................... 15-16 Oracle9i Reports Developer: Build Reports

Running XML Report Definitions .....................................................................................................................................................

Running XML Report Definitions Once you have created your Reports XML customization file, you can use it in the following ways: • Apply an XML report definition to a .rdf or .xml file at run time by using the CUSTOMIZE command line argument or the SRW.APPLY_DEFINITION builtin. • Run the XML report by itself by using the REPORT (or MODULE) command line argument. • Perform a batch update by using the RWCONVERTER executable with the CUSTOMIZE argument. Applying One XML Report Definition The following command line sends a job request to Oracle9iAS Reports Services and applies the XML report definition emp.xml to the employee.rdf file. rwclient userid=scott/tiger@db1 report=employee.rdf customize=d:\rep9i\custom\emp.xml destype=file desname=emp.pdf desformat=PDF server=repsrv1

The equivalent Reports Runtime command line would be: rwrun userid=scott/tiger@db1 report=employee.rdf customize=d:\rep9i\custom\emp.xml destype=file desname=emp.pdf desformat=PDF

Applying Multiple XML Report Definitions You can apply multiple XML report definitions to a report at run time by providing an argument list with the CUSTOMIZE keyword. The following command line sends a job request to Oracle9iAS Reports Services and applies two XML report definitions, emp0.xml and emp1.xml, to the .rdf file employee.rdf: rwclient userid=scott/tiger@db1 report=employee.rdf customize="(d:\rep9i\custom\emp0.xml, d:\rep9i\custom\emp1.xml)" destype=file desname=emp.pdf desformat=PDF server=repsrv1

Multiple files are applied in the order in which they are listed in the CUSTOMIZE argument. The last definition applied overrides any similar modifications made by a prior definition.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 15-17

Lesson 15: Extending Functionality Using XML .....................................................................................................................................................

Running XML Report Definitions

• Before Form or After Form trigger SRW.APPLY_DEFINITION(....xml);

• Run by itself • Batch updates rwconverter source="(...)" dest=... customize=... batch=yes

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note For applying XML definitions stored in the database, you may need to spread out the XML report definition over several columns because of the upper limit on the size of VARCHAR2 columns. In this case, you will have to code the built-in SRW.ADD_DEFINITION several times in the report trigger.

...................................................................................................................................................... 15-18 Oracle9i Reports Developer: Build Reports

Running XML Report Definitions .....................................................................................................................................................

Applying an XML Definition in PL/SQL To apply an XML report definition to an .rdf file in PL/SQL, you can use the SRW.APPLY_DEFINITION and SRW.ADD_DEFINITION built-ins in the BeforeForm or AfterForm trigger. When you run the report, the trigger executes and the specifed XML file will be applied. The following command line applies XML that is stored in the file system to a report: SRW.APPLY_DEFINITION(‘D:\rep9i\custom\emp.xml’);

To create an XML report definition in memory, you must first add the definition to the document buffer and then apply it. The following command line illustrates this: SRW.ADD_DEFINITION(:xmlcol); SRW.APPLY_DEFINITION;

Running the XML Report Definition by Itself To run an XML report definition by itself, simply submit a job request with an XML file specified in the MODULE (or REPORT) argument. Ensure that the file extension is .xml. Note that you could also apply an XML customization file to this report at the same time by using the CUSTOMIZE argument. Performing Batch Updates If you have a large number of files that must be updated, you can use the CUSTOMIZE command line argument with the RWCONVERTER command. Batch modifications are particularly useful when you must make a repetitive change to a large number of reports (for example, changing a field's format mask). Rather than opening each report and manually making the change in Reports Builder, you can run RWCONVERTER once and make the same change to a large number of reports at the same time. The following command line applies two XML report definitions to three different reports and saves the new definitions in the file system: rwconverter userid=scott/tiger@db1 stype=rdffile source="(emp1.rdf,emp2.rdf,staff.rdf)" dtype=rdffile dest="(emp1_new.rdf,emp2_new.rdf,staff_new.rdf)" customize="(d:\rep9i\custom\labels.xml, d:\rep9i\custom\format_mask.xml)" batch=yes

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 15-19

Lesson 15: Extending Functionality Using XML .....................................................................................................................................................

Debugging XML Report Definitions

• XML parser • Tracing option • Using Reports Builder

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Run a trace on XML file fullxml.xml and show the result.

...................................................................................................................................................... 15-20 Oracle9i Reports Developer: Build Reports

Debugging XML Report Definitions .....................................................................................................................................................

Debugging XML Report Definitions The XML Parser The XML parser is part of Oracle's XML Development Kit (XDK), which is delivered with the core Oracle Database release. The XML parser is a Java package that checks the validity of XML syntax. The JAR files that contain the XML parser are automatically configured on installation and are available to Oracle9i Reports. The XML parser catches most syntax errors and displays error messages. The error message contains the line number in the XML file where a problem was found, as well as a brief description of the problem. The Trace Option For testing purposes, you may want to activate the trace facility when you run your report. The example below shows a command line that has three trace options. rwrun scott/tiger@db1 report=D:\rep9i\custom\emp.xml tracefile=emp.log tracemode=trace_replace traceopt=trace_app

Using Reports Builder It is sometimes useful to open an XML report definition in Reports Builder while you are still working on it. This will allow you to quickly determine if all objects are created as expected. For example, if you are creating summaries in an XML report definition, then opening the definition in Reports Builder enables you to quickly determine if the summaries are being placed in the appropriate group in the Data Model. Technical Note For more information on the XML parser, refer to the Oracle Technology Network (http://otn.oracle.com).

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 15-21

Lesson 15: Extending Functionality Using XML .....................................................................................................................................................

Summary

In this lesson you should have learned how to: • Use XML report definitions: – Run-time customizations – Batch updates

• Apply XML definitions • Debug XML definitions

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 15-22 Oracle9i Reports Developer: Build Reports

Summary .....................................................................................................................................................

Summary •

•

•

Using XML report definitions: – Run-time customizations – Batch updates – Stand-alone reports Applying XML report definitions: – From the command line – From PL/SQL Debugging XML report definitions: – The XML parser – The trace option – Using Reports Builder

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 15-23

Lesson 15: Extending Functionality Using XML .....................................................................................................................................................

Practice 15 Overview

• Creating an XML report definition • Applying a run-time customization • Debugging an XML definition

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 15-24 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 15 .....................................................................................................................................................

Practice Session Overview: Lesson 15 This practice session contains: • Running a report using an XML definition • Applying an XML report definition to an existing .rdf file Introduction Using XML you can build fully functional reports or partial reports that can serve as a customization file. You will run an XML file and apply an XML definition to an existing report.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 15-25

Lesson 15: Extending Functionality Using XML .....................................................................................................................................................

Practice Session: Lesson 15 1 Run an XML report definition.

Open p15q1.xml using Wordpad. Examine the code. What type of report definition is this? What goes on in this file? b Execute p15q1.xml using the Run Paper Layout option in Reports Builder. 2 Apply run-time customizations. a Open p15q2.rdf. Run the paper layout. b What type of report is this? Investigate the data model, the paper layout, and the report sections. c Open p15q2.xml. Examine the code. What goes on in this XML definition file? d Apply p15q2.xml to p15q2.rdf using the RWRUN command and send the output to a PDF file. e Examine the newly created report output. What do you see? a

...................................................................................................................................................... 15-26 Oracle9i Reports Developer: Build Reports

16 ................................

Creating and Using Report Parameters

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

Objectives

After completing this lesson, you should be able to do the following: • Create and reference a parameter • Create a list of values for parameter input • Use and modify a system parameter • Build a Paper Parameter Form layout • Customize a Paper Parameter Form layout • Use HTML parameter form extensions

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Topic Timing Lecture 50 minutes Practice 30 minutes Total 80 minutes

...................................................................................................................................................... 16-2 Oracle9i Reports Developer: Build Reports

Overview .....................................................................................................................................................

Overview Introduction Parameters enable you to develop dynamic report documents that produce variable report output depending on the parameter value that you enter at run time. This lesson shows you how to create and reference user and system parameters and how to customize a Paper Parameter Form to display parameters at run time. Objectives After completing this lesson, you should be able to do the following: • Control report output by creating and referencing a parameter • Control parameter input using a list of values • Manipulate reports at run time using system parameters • Provide for user input of parameters by building a Paper Parameter Form • Customize a Paper Parameter Form layout • Use HTML Parameter Form extensions

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-3

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

User Parameters

• Restrict values in a WHERE clause SELECT CUST_LAST_NAME, ACCOUNT_MGR_ID FROM CUSTOMERS WHERE ID =

• Substitute any part of a SELECT statement SELECT CUST_LAST_NAME, ACCOUNT_MGR_ID FROM CUSTOMERS

• Substitute a single column or expression SELECT FROM CUSTOMERS

Copyright © Oracle Corporation, 2002. All rights reserved.

Creating a User Parameter

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Briefly mention that user parameters can be used with pluggable data sources in Oracle9i Reports. This lesson covers parameters in a SQL query. Technical Note For more information on using parameters with pluggable data sources, see the eClass Oracle9i Reports: Integrate Pluggable Data Sources.

...................................................................................................................................................... 16-4 Oracle9i Reports Developer: Build Reports

Creating User Parameters .....................................................................................................................................................

Creating User Parameters Introduction You can create your own parameters and use them to change the SELECT statement of your query at run time. Parameters can also be used with pluggable data sources: XML, JDBC, Text, and Express Server queries. What Is a User Parameter? A user parameter is a Data Model object that you create to hold a value that users can change at run time. You can reference a parameter anywhere in a query. For example: • Restrict values in the WHERE clause of the SELECT statement • Substitute any part of the SELECT statement, including the entire statement • Substitute a single column or expression in the SELECT list You can display the contents of a parameter in your report by creating a paper layout field and entering the parameter name in the Field Source property. How to Create a User Parameter in the Object Navigator 1 In the Object Navigator, click the User Parameter node, and then select the Create tool. Note: If this is the first parameter, you can create it by double-clicking the User Parameter node. 2 Rename the parameter and open the Property Inspector. 3 Verify the data type and width. Enter an initial value if required. Parameter Properties Property Datatype Width Input Mask Initial Value Validation Trigger List of Values Type of Pluggable Data Source

Description Specify whether parameter value is Character, Number, or Date; default is Number when created in the Object Navigator. Specify maximum allowable width of parameter value; maximum is 64 KB. Allow users to enter a numeric or date value using a specific format. Specify the value to use. You can override this value at run time from the command line or parameter form. Validate the parameter value. Enter a PL/SQL function. Create a list of values from which users select a valid value at run time. Used by the PDS for acquiring single sign-on information. It identifies that parameter as containing sign-on information for that PDS connection.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-5

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

Referencing Parameters in a Report Query • Bind reference replaces a value: – :parameter_name – Parameter object can be created by default

• Lexical reference replaces a clause: – ¶meter_name – Parameter object can be created by default

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Explain the differences between a bind and a lexical reference. Note that substitution for a bind reference happens after the SELECT statement is parsed; whereas, for a lexical reference, this happens before the SELECT statement is parsed. Be brief; there are examples on the next pages.

...................................................................................................................................................... 16-6 Oracle9i Reports Developer: Build Reports

Referencing Parameters in a Report Query .....................................................................................................................................................

Referencing Parameters in a Report Query Introduction There are two ways to reference parameters in a query: • •

Use a bind reference Use a lexical reference

What Is a Bind Reference? A bind reference replaces a single value or expression. To create a bind reference in a query, prefix the parameter name with a colon (:). If the parameter object does not exist, Reports Builder automatically creates it for you and displays a message. In this case, the parameter default datatype is CHARACTER, not NUMBER. What Is a Lexical Reference? A lexical reference is a text string and can replace any part of a SELECT statement, such as column names, the FROM clause, the WHERE clause, or the ORDER BY clause. To create a lexical reference in a query, prefix the parameter name with an ampersand (&). A lexical reference for a column or table must be created explicitly before you can use it in a query. For other clauses in the SELECT statement, if the parameter object does not exist, Reports Builder automatically creates it for you and displays a message. In this case, the parameter default datatype is CHARACTER, not NUMBER. Comparing Bind and Lexical Reference Types Type Bind

Prefix :

Lexical

&

Use to Replace Single value or expression in the following clauses: WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, START WITH Any part of a SELECT statement

Parameter Created by Default? Yes, if it does not already exist. Reports Builder displays a warning message and adds the parameter to User Parameters in the Object Navigator.

Provided it is not used for a column or table, yes: if it does not already exist. Reports Builder displays a warning message and adds the parameter to User Parameters in the Object Navigator. Datatype must always be Character.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-7

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

Using Bind References

• Restrict values in a WHERE clause SELECT CUST_LAST_NAME, ACCOUNT_MGR_ID FROM CUSTOMERS WHERE CUSTOMER_ID > :P_CUST

• Substitute a single value or expression in the select statement SELECT CUST_LAST_NAME, ACCOUNT_MGR_ID FROM CUSTOMERS ORDER BY DECODE( :SORT, 1, CUST_LAST_NAME, 2, NLS_TERRITORY, ACCOUNT_MGR_ID)

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Run L16BIND.JSP. You want to restrict the Order Mode. Add to the query: AND O.ORDER_MODE = :P_ORDERMODE Notice the message telling you that the parameter is being created. In the Object Navigator, show the user parameters. In the parameter Property Inspector, show that the default data type is CHARACTER. Change the following: Width=8, Initial Value=direct. Run the paper layout. Now run the Web layout. Show the other method of creating a parameter. In the Object Navigator, create a parameter called P_CUST and show the properties. Point out that the default data type is NUMBER, so be careful of the data type when you create parameters. Set the initial value to 101. Add to the query: AND O.CUSTOMER_ID > :P_CUST

...................................................................................................................................................... 16-8 Oracle9i Reports Developer: Build Reports

Referencing Parameters in a Report Query .....................................................................................................................................................

Using Bind References Use a bind reference anywhere in a query where you can use a single literal value, such as a character string, number, or date. Examples SELECT CUST_LAST_NAME, ACCOUNT_MGR_ID FROM CUSTOMERS WHERE CUSTOMER_ID = :P_CUST

This statement enables you to enter a specific customer number at run time. For example, if you enter 102, the WHERE clause uses the value 102 to restrict data fetched and to fetch the one customer that has ID 102. SELECT CUST_LAST_NAME, ACCOUNT_MGR_ID FROM CUSTOMERS ORDER BY DECODE(:SORT, 1, CUST_LAST_NAME, 2, NLS_TERRITORY, ACCOUNT_MGR_ID)

This statement enables you to output different versions of the report: • Enter 1 for the parameter SORT to display the output ordered by customer name. • Enter 2 to display the output ordered by NLS_territory. Where Can You Not Use Bind References? You cannot use a bind reference to: •

Replace a column name in the SELECT clause, although you can reference a value, such as the contents of a parameter: SELECT LAST_NAME, SALARY * :P_RATE FROM EMPLOYEES

•

Replace any part of a FROM clause

•

Replace reserved words or clauses

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-9

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

Using Lexical References

• Use to substitute any part of the query. SELECT CUST_LAST_NAME, ACCOUNT_MGR_ID FROM CUSTOMERS &P_WHERE_CLAUSE &P_ORD_CLAUSE SELECT CUST_LAST_NAME, ACCOUNT_MGR_ID FROM CUSTOMERS &P_WHERE_ORD_CLAUSE SELECT FROM

&P_CUSTNAME CUST, &P_ACCTMGR MGR &P_TABLE

• Make sure that the number and data types match at run time. Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Demonstration: Add a lexical parameter to alter the order of output. Add to query: ORDER BY &P_ORDCLAUSE . Show the error. The parameter is not created by default, so the syntax ORDER BY is not valid. Create the lexical parameter in the Object Navigator. Show the properties. Point out the default data type and size and modify as required: CHARACTER, 50. Set an initial value. This time, include the keywords ORDER BY C.CUST_LAST_NAME DESC, so that you can leave the clause blank at run time. Now add the reference to the end of the query: &P_ORDCLAUSE. Run with different values, including NULL. Continue the demonstration to add a dynamic list of values to the bind parameter and a static list to the lexical parameter, or teach the next page and then demonstrate. Run the report using the Paper Design and then run the report in a browser.

...................................................................................................................................................... 16-10 Oracle9i Reports Developer: Build Reports

Referencing Parameters in a Report Query .....................................................................................................................................................

Using Lexical References Use a lexical reference to replace any clause in a SELECT statement, or even to replace the entire statement. Examples The following statements use lexical references to substitute parts of the query at run time: •

To specify a WHERE clause, ORDER BY clause, or both at run time (as two separate parameters): SELECT CUST_LAST_NAME, ACCOUNT_MGR_ID FROM CUSTOMERS &P_WHERE_CLAUSE &P_ORD_CLAUSE

•

To specify a WHERE clause, ORDER BY clause, or both at run time (as one parameter): SELECT CUST_LAST_NAME, ACCOUNT_MGR_ID FROM CUSTOMERS &P_WHERE_ORD_CLAUSE

•

To specify two column names and the table names at run time: SELECT &P_CUSTNAME CUST, &P_ACCTMGR MGR FROM &P_TABLE

Note: When you use lexical references in the SELECT list, you must, at run time, specify the same number of items of the correct data type, as defined in the Data Model.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-11

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

Hints and Tips When Referencing Parameters Always do the following: • Specify column aliases when substituting column names • Create lexical parameters explicitly in the Object Navigator • Enter an initial value for parameters that affect query validation when NULL

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Use the whiteboard to work through a SQL example of the first and third points shown in the slide.

...................................................................................................................................................... 16-12 Oracle9i Reports Developer: Build Reports

Hints and Tips for Referencing Parameters .....................................................................................................................................................

Hints and Tips for Referencing Parameters Specifying Column Aliases If you substitute the name of a column or expression in the SELECT list with a lexical reference in the SELECT list, always add a column alias after the reference. The alias serves as a place holder in the Data Model and can help to prevent confusion that may arise when the column name in the SELECT list does not match the report column object. Referencing Nonexistent and Null Parameters Remember that Reports Builder automatically creates a parameter when you make a reference to a nonexistent parameter. When you create a lexical parameter, you may need to include an initial value before referencing the parameter in the query, because depending on your use of the parameter, a NULL value can cause a syntax failure. Example The following statement does not cause a syntax error when P_ORDER_CLAUSE is NULL. SELECT...FROM... &P_ORDER_CLAUSE

The following statement does cause a syntax error when P_ORDER_CLAUSE is NULL. SELECT... FROM... ORDER BY &P_ORDER_CLAUSE

Remember to define an initial value for lexical parameters that affect the validity of the statement, and to validate those that you allow a user to enter at run time. Initial values are required for lexical parameters used for column and table names. Note: Always use column aliases when substituting column names with lexical references.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-13

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

Static List of Values

1 2

3

4 5

Copyright © Oracle Corporation, 2002. All rights reserved.

1

Choose Static Values.

4

2

Enter a value and click Add.

5

3

Repeat for each value to build the list of values.

To remove a value; select a value and click Remove. Select OK to accept the list and exit.

Instructor Note Use l16bind.jsp to demonstrate a static list of values for payment type, or by creating a number of different ORDER BY clauses. Examples: ORDER BY C.CUST_LAST_NAME DESC (our initial value) ORDER BY C.CUST_LAST_NAME ORDER BY O.ORDER_ID DESC

Make sure the Restrict List to Predetermined Values check box is clear to allow for other values. Run the report. Select one of the ORDER BY clauses from the list of values. Run the report again. Select ORDER BY O.ORDER_ID, but delete DESC. Suggest that students can use column aliases to make the ORDER BY clause more readable.

...................................................................................................................................................... 16-14 Oracle9i Reports Developer: Build Reports

Creating a List of Values .....................................................................................................................................................

Creating a List of Values Introduction You can create a list of values from which users select a valid value at run time. You can restrict users to only those values in the list, or allow them to enter a different value. For bind parameters, the list can be a static list of values or a dynamic list that selects values from the database at run time. For lexical parameters, you can enter a static list of values. How to Create a Static List of Values 1 In the Parameter Property Inspector, Select the List of Values property. The Static Values option button is selected by default. 2 Enter a value in the Value field and click Add. 3 Repeat for each value you want in the list. Note: To remove a value, select the value in the list and click Remove. Restricting Users to Values in the List Set the Restrict List to Predetermined Values property to determine whether to prevent users from entering any value not included in your list. By default, this property is Yes. If you set the property to No, users can still select a value from the list, but they can also enter other values or leave the value blank.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-15

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

Dynamic List of Values

1

3 4 2 5

Copyright © Oracle Corporation, 2002. All rights reserved.

1 2 3

Choose SELECT Statement. Enter a valid query.

4 5

Hide the first column from the list. Click OK to accept the query and exit.

Select or clear the Restrict List to Predetermined Values check box.

Instructor Note Demonstrate a dynamic list of values by selecting a list of customer IDs and names from the CUSTOMERS table. Select the Restrict List to Predetermined Values check box. Demonstrate the Hide First Column check box. Explain to the students that they can select more than one column in the SELECT statement so that they can display a more descriptive list to the user. The first column should be the value that restricts the query. Run the report and show that this time you cannot edit or enter your own value.

...................................................................................................................................................... 16-16 Oracle9i Reports Developer: Build Reports

Creating a List of Values .....................................................................................................................................................

How to Create a Dynamic List of Values 1 In the Parameter Property Inspector, select the List of Values property. 2 Choose SELECT Statement. The SQL Query Statement field is displayed. 3 Enter a query to populate the list of values. You can include more than one column. The parameter takes its value from the first column in the list and the column values appear concatenated in the list at run time. 4 Set the Restrict List to Predetermined Values property, as required. Displaying Meaningful Values Instead of Codes To see a more meaningful list of descriptive values, instead of the primary or foreign key column that you reference in the query, select Hide First Column. Make sure that the primary or foreign key column is first in the SQL query statement, because this is still the value that the parameter object contains at run time.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-17

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

System Parameters

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Demonstration: Show the system parameters in the Object Navigator. Explain the DESNAME, DESFORMAT, and DESTYPE parameters briefly. COPIES: Only if destination is a printer CURRENCY, THOUSANDS, DECIMAL: Only if symbols are used in format masks Remind the students that these are also arguments that they can include on the command line for RWBUILDER or RWRUN. However, the following command line options have been deprecated in Oracle9i Reports: Command Line Option BACKGROUND CURRENCY DECIMAL THOUSANDS

Option to use instead Use the BATCH command line option. Set the NLS_CURRENCY evnironment variable. Set the NLS_NUMERIC_CHARACTERS environment variable. Set the NLS_NUMERIC_CHARACTERS environment variable.

Run the report with DESTYPE set to File, DESNAME set to my_rep.rtf, and DESFORMAT set to .rtf.

...................................................................................................................................................... 16-18 Oracle9i Reports Developer: Build Reports

Referencing System Parameters .....................................................................................................................................................

Referencing System Parameters Introduction Reports Builder provides system parameters to allow you to modify standard run-time settings for each report definition. There are currently eleven system parameters. You cannot delete them. Each parameter has a default value that you can modify. Parameter BACKGROUND COPIES CURRENCY

Use to specify Whether report is to run in background Number of copies if destype=printer Symbol for currency indicator

DECIMAL

Symbol for decimal indicator

DESFORMAT

Output device definition; ignored if type=screen/preview

DESNAME

Destination name, such as filename, printer name, mail userid; ignored if type=screen/preview Destination type for output

DESTYPE

MODE ORIENTATION PRINTJOB

THOUSANDS

Whether report executes in bitmapped or character mode The print direction of printer output Whether print job dialog box is displayed at run time, if destype=file or printer Symbol for thousands indicator

Values Yes, No

Default No

Any integer Maximum 4 characters Any single character Examples: dflt, pslan80, pdf, HTML

1

dflt

.lis

File, printer, mail, screen, preview Bitmap, character Portrait, landscape Yes, No

Screen

Default Portrait Yes

Any single character

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-19

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

Displaying a Currency Symbol for Monetary Values Build: report:Parameter - CURRENCY Initial Value

£

report:Layout Field - F_Money Format Mask $NNN0

£1000 £6000 £5000 €1000 €6000 €5000

Runtime: RWRUN report=fred.rep...currency=€

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note The diagram above shows the method described opposite. Mention the alternative: Setting NLS_LANG and using the L, G, and D symbols (which are used by the currency symbols in the stylebar). NLS is discussed later in the course. Demonstration: Alter the format of F_ORDER_TOTAL to $N,NNN,NN0.00 (there are some credit orders that are more than $1 million). Change CURRENCY initial value to FF (or something else). Run the report. You might want to point out that the currency parameter does not appear on the current default run-time parameter form (if nobody mentions it before you). You can show it later, if you want, when you create a parameter form. If you have time: Create a program item for the demonstration report (you can save time by creating this before the lesson). The command line should be: \bin\rwrun report=reportname userid= currency=FF

Show that this command overrides the initial value of the parameter.

...................................................................................................................................................... 16-20 Oracle9i Reports Developer: Build Reports

Referencing System Parameters .....................................................................................................................................................

Displaying a Currency Symbol for Monetary Values A common requirement is to format currency values preceded by a currency symbol. You may require the symbol to be £ for pounds sterling, $ for U.S. dollars, ¥ for the Japanese yen, and so on. You can set different initial values in different reports. How to Display a Different Currency Symbol 1 Enter the currency symbol ($) in the format mask of one or more layout fields. 2 From the Object Navigator, open the Property Inspector for the currency system parameter and enter a maximum of four characters in the initial value. Note: Currency, thousands, and decimal symbols appear in the report output only if you use them in the format mask of a layout field. Altering the Parameter Value at Run Time You can modify the parameter value at run time in one of two ways: • Include the currency parameter on the run-time command line • Enable users to alter the parameter value on the paper parameter form If the currency symbol you choose is several characters in length, remember to make the relevant fields wide enough to include it. Priority of Parameter Values RWRUN: Run-time parameter form overrides command line. Command line overrides initial value. RWBUILDER: Run-time parameter form overrides initial value. Initial value overrides command line. Note: When running a report in Reports Builder, an initial value for the current report takes priority over a default that you specify in the RWBUILDER command line. Use Edit > Preferences > Runtime Values and Runtime Settings for testing.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-21

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

Building a Paper Parameter Form

+ Report properties: • Width • Height • Number of Pages

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Demonstrate how to display the Paper Parameter Form in the Report Editor: • Show the Paper Parameter Form icon in the top left corner of the Report editor. • Show the menu option View > Change View > Paper Parameter Form. • Show the Paper Parameter Form node and icon in the Object Navigator. The Paper Parameter Form layout is currently blank; no parameter form is defined. You may want to show that there is no File Link tool in the Paper Parameter Form toolbar. Point out that although you can create your parameter form from scratch, it is usually easier to let Reports Builder do some of the work by creating a Default Paper Parameter Form and then modifying it. This is discussed on the next page.

...................................................................................................................................................... 16-22 Oracle9i Reports Developer: Build Reports

Building a Paper Parameter Form .....................................................................................................................................................

Building a Paper Parameter Form Introduction The paper reports that you created in previous lessons ran successfully without a parameter form. However, when you created parameters, a default run-time parameter form appeared to enable you to change the values at run time. You can build your own parameter form, select the parameters that you want to display, and then customize the appearance of the parameter form in the Report editor. How to Build a Parameter Form 1 Select menu item Tools > Parameter Form Builder. The Parameter Form Builder dialog box appears. 2 If desired, modify the title, hint, and status line text. These lines are displayed at the top of the parameter form at run time. 3 Select or deselect parameters in the parameter section as necessary. Note: Selected parameters are displayed in the dialog box as highlighted on a black background. Deselected parameters do not appear in the parameter form. 4 Modify label text of the selected parameters, if you wish. 5 Click OK to create the parameter form. If you create many parameters in your report, the run-time parameter form can spread over more than one page. You can view subsequent pages at run time by clicking Next. Setting Report Properties for the Parameter Form There are three properties in the Report Property Inspector, under the Parameter Form Window node, that specifically apply to the parameter form. Report Property Width Height Number of Pages

Use to Define the width of the parameter form that the user sees at run time Define the height of the parameter form that the user sees at run time Define the number of physical pages that make up the parameter form

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-23

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

Customizing a Paper Parameter Form

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Demonstration: Continue with the previous demonstration report that contains a parameter. Build a parameter form. Insert p2q40.gif or some other graphics. Save the report and run it in a browser. Show the Property Inspector for a field and for some boilerplate text. Point out the default naming convention, PF and PB. You can alter parameter properties from the field as well as in the Data Model parameter object.

...................................................................................................................................................... 16-24 Oracle9i Reports Developer: Build Reports

Building a Paper Parameter Form .....................................................................................................................................................

Customizing the Parameter Form Layout You can customize the parameter form layout in a similar way to customizing the paper report layout. You can modify colors and fill patterns (except in fields), move or delete existing objects, create additional objects, import file contents such as a company logo, and so on. Creating Parameter Fields A parameter field is a placeholder for a parameter value on the run-time parameter form, in the same way that a field is a placeholder for a column value in the layout. You cannot modify the color or fill patterns of a parameter field. However, the Property Inspector enables you to modify all parameter properties. Reports Builder creates one field for each parameter that you select in the Parameter Form Builder. If a parameter in your Data Model does not have an associated field, it does not appear in the run-time parameter form because it does not have any display attributes. You can create additional fields by using the field tool in the parameter form toolbar and sourcing the field to an existing parameter. Creating Parameter Boilerplate Objects Parameter boilerplate is text or graphics that either you create or the Parameter Form Builder creates by default. The Parameter Form Builder creates parameter boilerplate objects for the labels of each parameter field as well as for the title, hint, and status lines. You can create additional boilerplate objects to customize your parameter form, for example, create report heading pages, modify default labeling, insert images, and so on. Note: You cannot create a file link in the parameter form; however, you can insert file contents, as you can in the layout.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-25

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

Using Parameter Form HTML Extensions

• • • •

Boilerplate text with HTML tags Parameter fields with JavaScript Parameter Form header Parameter Form footer

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 16-26 Oracle9i Reports Developer: Build Reports

Using Parameter Form HTML Extensions .....................................................................................................................................................

Using Parameter Form HTML Extensions HTML Parameter Form extensions enable you to enhance your run-time paper parameter form with HTML tags and JavaScript. Using HTML, you can create boilerplate text. Incorporating JavaScript extends your parameter form to support client-side validation eliminating network roundtrips. You can do the following: Extension Create boilerplate text with HTML tags Insert parameter fields with Javascript Create a Paper Parameter Form header Create a Paper Parameter Form footer

Purpose Add hyperlinks or HTML tagged text Define input or select events such as validation or raising errors Place a logo or standard links in the header of the HTML Parameter Form Place a logo or standard links in the footer of the HTML Parameter Form

Creating a Boilerplate Text Object for HTML Tags 1 Create a boilerplate text object using the Text tool. 2 Enter or import the desired HTML code. 3 Open the Property Inspector of the text object and set the Contains HTML Tags property to Yes. Note: The text only shows for HTML output formats. Creating HTML Parameter Form Fields with Input or Select Events 1 Create or edit a Parameter Form field. 2 Open the Property Inspector of the Parameter Form field and set the Additional Attributes(HTML) property to a valid JavaScript event handler. Note: In some cases, such as raising messages, the JavaScript code may have to be entered in the Before Form report properties. To insert the JavaScript code in the Before Form report properties: – Open the report Property Inspector. – Set the Before Form Type property to Text if you enter the JavaScript code, or to File if you will import the code from a file. – Set the Before Form Value property by clicking the ... button to either enter the JavaScript, or select the HTML file with the JavaScript.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-27

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

Parameter Form Header and Footer

Header

Footer

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 16-28 Oracle9i Reports Developer: Build Reports

Using Parameter Form HTML Extensions .....................................................................................................................................................

Creating an HTML Parameter Form Header 1 Open the Property Inspector of the report. 2 Set the Before Form Type property to Text if you will enter the header, or to File if you will import the header from a file. 3 Set the Before Form Value property by clicking the ... button to either enter the HTML code or select an HTML file to import. Creating an HTML Parameter Form Footer 1 Open the Property Inspector of the report. 2 Set the After Form Type property to Text if you will enter the footer, or to File if you will import the footer from a file. 3 Set the After Form Value property by clicking the ... button to either enter the HTML code, or select an HTML file to import.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-29

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

Summary

In this lesson, you should have learned how to: • Define and use bind and lexical parameters • Create a list of values • Reference system parameters • Modify parameter values at run time, using: – Command line arguments – Run-time parameter form

• Create a run-time paper parameter form: – Build it using the Parameter Form Builder – Customize it using the Report Editor, HTML

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 16-30 Oracle9i Reports Developer: Build Reports

Summary .....................................................................................................................................................

Summary •

•

•

Parameter types: – User parameters: References: bind or lexical List of values: dynamic (bind only) or static – System parameters Modifying parameter values at run time: – Pass the parameter on the RWRUN command line – Allow users to change values in the run-time parameter form Creating a run-time parameter form: – Build a parameter form with the Parameter Form Builder – Customize the paper parameter form layout in the Report editor – Evaluate using HTML Parameter Form extensions

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-31

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

Practice 16 Overview

• • • • •

Modifying an existing report to use a bind parameter Adding a dynamic list of values Adding a lexical parameter Adding a static list of values Creating a customized parameter form

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 16-32 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 16 .....................................................................................................................................................

Practice Session Overview: Lesson 16 This practice session contains: • Modifying an existing report to use a bind parameter • Adding a dynamic list of values • Adding a lexical parameter • Adding a static list of values • Creating a customized parameter form Introduction In this practice session, you create bind and lexical parameters to restrict records in the report. You add lists of values to select valid values at run time. You then create your own default parameter form and customize it to include extra text and graphics.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-33

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

Practice Session: Lesson 16 1 Open report p13q3.rdf. a

Modify the data model and add the following columns from the CUSTOMERS table. Create a new group for the customer information above the G_ORDER_ID group.

Column cust_first_name ||’ ‘|| cust_last_name cust_email

Label Name Email

Width 30 20

Create a reference to a bind parameter P_CUSTOMER in the query to restrict orders to the parameter value you input at run time. Note how Reports creates the parameter automatically. b Run and test the report with valid customer IDs. c Save the report as p16q1.rdf. Do not close it 2 Continue with report p16q1.rdf. a Add a list of values to select any valid CUSTOMER_ID in the CUSTOMERS table that has an order inteh ORDERS table. Do not allow users to enter any other value. The list should display customer names, not CUSTOMER_ID numbers.Ensure that each customer name appear only once in the list, even if the customer has several orders. b Save the report as p16q2.rdf. Do not close it for the moment. c Run the paper layout to test the report. Run the Web layout as well. 3 Open report p5q3.rdf. a Create a lexical parameter to enable users to order the data by different columns (or not at all) at run time. Include an ORDER BY clause in the query with a lexical reference to the parameter. Enter an initial value for the parameter to order by the employee’s last name. b Add a static list of values. Code two or three alternative ORDER BY clauses. Allow any clause to be entered at run time. c Run the paper layout to test. Select an ORDER BY clause from the list. Enter your own ORDER BY clause. Delete the initial parameter value. d Save the report as p16q3.rdf and close it.

...................................................................................................................................................... 16-34 Oracle9i Reports Developer: Build Reports

Practice Session: Lesson 16 .....................................................................................................................................................

4 Continue with report p16q2.rdf.

Create a default parameter form. Include a Title message but not a Hint message. Display the parameters CURRENCY and P_CUSTOMER. b Use your imagination to customize the form in the Parameter Form editor. Make sure that the parameter P_CUSTOMER is placed in a prominent position for user entry. Add some graphics; modify colors and fill patterns. c Add a format mask to the field displaying line_total. d Run the paper layout to test. Provide a value for the currency symbol and select a customer from the list. e Save the report as p16q4.rdf and close it. a

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 16-35

Lesson 16: Creating and Using Report Parameters .....................................................................................................................................................

...................................................................................................................................................... 16-36 Oracle9i Reports Developer: Build Reports

17 ................................

Embedding a Graph in a Report

Lesson 17: Embedding a Graph in a Report .....................................................................................................................................................

Objectives

After completing this lesson, you should be able to do the following: • Create and display a simple graph • Customize a graph in a Web report • Modify graph data dynamically • Use graph hyperlinks

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Topic Timing Lecture 30 minutes Practice 30 minutes Total 60 minutes

...................................................................................................................................................... 17-2 Oracle9i Reports Developer: Build Reports

Overview .....................................................................................................................................................

Overview Introduction In this lesson, you learn to display a graph in a report at run time. Objectives After completing this lesson, you should be able to do the following: • Create and display a graph in a paper report using the Graph Wizard • Create and display a graph in a Web report using the Graph Wizard • Edit the Web source to customize a graph in a Web report • Use graph hyperlinks

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 17-3

Lesson 17: Embedding a Graph in a Report .....................................................................................................................................................

Adding a Graph to a Paper Report

Copyright © Oracle Corporation, 2002. All rights reserved.

Adding a Graph to a Web Report

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 17-4 Oracle9i Reports Developer: Build Reports

Creating and Embedding a Graph in a Report .....................................................................................................................................................

Creating and Embedding a Graph in a Report Introduction As in many Oracle products, Oracle9i Reports uses the BI Graph Bean for graph development. The BI Graph Bean offers 52 different types of graphs, including three dimensional (3D) graphs. There is no separate graphics tool. Reports Builder provides an easy-to-use Graph Wizard in to enable you to easily produce a graph for your paper and Web reports. Adding a Graph to a Paper Report In Reports Builder, you can invoke the Graph Wizard from the Paper Layout view and the Paper Design view. From the Paper Layout view: 1 Click the Graph tool in the vertical toolbar. 2 Drag a square in the painter area where you want your graph to appear. The Graph Wizard displays. 3 Step through the Wizard to create the desired graph and position it in your report. Note: In the Paper Layout, the graph is represented as a bar graph even if the graph is of another type. From the Paper Design view: 1 Select Insert > Graph from the menu. The Graph Wizard displays. 2 Step through the Wizard pages to create the desired graph and position it in your report. To reenter the Graph Wizard, do either of the follwoing: • Right-mouse-click and select Graph Wizard • Click the graph and select Edit > Settings from the menu. Adding a Graph to a Web Report 1 Open the Web Source view. 2 Position your cursor in the section of the source code where you want to add a graph. 3 Select Insert > Graph from the menu. 4 Step through the Wizard pages to create the desired graph. To reenter the Graph Wizard, position your cursor anywhere within the rw:graph tag and select Edit > Settings. You will learn more about the rw:graph tag later in this lesson.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 17-5

Lesson 17: Embedding a Graph in a Report .....................................................................................................................................................

Selecting the Graph Type

Copyright © Oracle Corporation, 2002. All rights reserved.

Selecting the Graph Data

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Suggested demonstration: Open l17pie.jsp and create a pie graph. Category: DEPARTMENT_NAME. Data: sumSALARYPerDEPARTMENT_NAME. Position the graph at the beginning of the report. Note: On the layout page, be sure that the category column is in the ‘Slices’ area. You may need to drag DEPARTMENT_NAME from the Groups area to the Slices area.

...................................................................................................................................................... 17-6 Oracle9i Reports Developer: Build Reports

Creating and Embedding a Graph in a Report .....................................................................................................................................................

Selecting the Graph Type Select the type of graph that you require. The Wizard provides a preview of what the graph will look like as well as recommendations for when different types of graphs are useful and appropriate. Select the graph subtype that you require, using the subtype pictures as a guide. If desired, select the 3D effect check box. Click Next to continue. Positioning the Graph in the Report Output The next page of the Graph Wizard enables you to position the graph at the beginning or end of the report output, or to display the graph once for each occurrence of a group. Selecting the Graph Data The next two wizard pages allow you to define the data in the graph: 1 Select the field that you want to appear as the categories of your graph. Category columns are used for the x-axis. 2 Select the field that you want to appear as the data values of your graph. Data values columns are used for the y-axis and must be numeric.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 17-7

Lesson 17: Embedding a Graph in a Report .....................................................................................................................................................

Adding Options to the Graph

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 17-8 Oracle9i Reports Developer: Build Reports

Creating and Embedding a Graph in a Report .....................................................................................................................................................

Adding Options to the Graph The remaining pages in the Graph Wizard allow you to add optional characteristics to your graph. • Use the Layout page to rearrange the data in a graph. The arrangement of data determines the way a graph is displayed. On the Layout page, you can rearrange place holders that represent dimensions, such as Product, Time, Geography, and so on. When these changes are applied, then actual dimension members are displayed in the positions that you specified. • Use the Title page to specify text and font attributes for the title, subtitle, and footnote of a graph. • Use the Legend page to select options for the legend of a graph. The legend area is a place on a graph that provides a key to the information that is shown in the graph. It contains legend markers, each corresponding to a series of data, and legend text, which describes the data in that series. For example, you might have different cities represented by data markers of different colors. Markers represent data on a graph. If red markers in your graph represent New York, then there is a corresponding red legend marker in the legend area, with legend text reading "New York." • Use the X-Axis page to specify options for the x-axis of your graph. Options include the text and font of the axis title, and axis line style and color. You can insert parameters into the axis labels by clicking the Insert button and selecting the desired parameter. • Similarly, use the Y-Axis page to specify options for the y-axis of your graph. • Use the Plot Area page of the Graph Wizard to specify how the plot area of the graph should be displayed. Options include gridlines, the line color and style for gridlines, data lables, and data tips (tooltips displayed when the mouse is moved over a bar, pie slice, etc.).

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 17-9

Lesson 17: Embedding a Graph in a Report .....................................................................................................................................................

The rw:graph Tag

• Defines a graph • Attributes: – – – – – – – –

id src groups dataValues series width height graphHyperlink



Copyright © Oracle Corporation, 2002. All rights reserved.

Customizing Web Graphs

Graph XML

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Continue with l17pie.jsp. Insert a graph into the Web source. 1 Open the Web Source view. Continue with the steps on the opposite page.

...................................................................................................................................................... 17-10 Oracle9i Reports Developer: Build Reports

Creating and Embedding a Graph in a Report .....................................................................................................................................................

Customizing Web Graphs Earlier in the course, you learned how to customize your Web reports by editing the Web source code and modifying the attributes for the Reports custom JSP tags. You can customize your Web graphs by modifying the attributes of the rw:graph tag. The rw:graph Tag The rw:graph tag defines a graph in a Web report. The rw:graph tag requires a data source and must be enclosed in the rw:report tag.The tag body is required and contains the definition of the graph in XML. The attributes that define the rw:graph tag are: Attribute id src groups dataValues series width height graphHyperlink

Description Unique identifier in the page scope Name of the data source group List of data source columns that are used to generate a row-key in the XML graph definition. List of numeric datasource columns that provide data for the graph List of datasource columns that are used to generate a columnkey in the XML graph definition; optional Width of the graph in pixels; optional Height of the graph in pixels; optional Defines a link to another source such as a URL or drill down report; optional

Use the Web Source editor to modify or add attributes. In the slide opposite, the width and height attributes were included to customize the size of the graph in the Web report. The graph hyperlink will invoke a drill down report on a specific employee. You can also customize a graph by modifying the graph XML. Instructor Note 2 Scroll down in the code and locate the comment Insert Graph Here. 3 Select Insert > Graph to invoke the Graph Wizard. 4 Graph type: Bar 5 Category: DEPARTMENT_NAME 6 Data Columns: SumSalaryPerDEPARTMENT_NAME 7 Finish the Wizard. 8 Run the Web layout to test.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 17-11

Lesson 17: Embedding a Graph in a Report .....................................................................................................................................................

Using Graph Hyperlinks

• Document on same machine • Any destination • Any URL

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Explain briefly what the last example on the opposite page means. You will look into this in detail in a later chapter and provide a more elegant solution. This is the URL syntax for a JSP-based report. In the URL specification, wlord-pc is the name of the Web server, and us.oracle.com is the domain name. The port is 7777. The rest of the URL specification contains the report module to be executed along with various parameters. Note: Oracle9iAS Reports Services is needed to execute a report using graph hyperlinks. You cannot run such a report with Reports Builder.

...................................................................................................................................................... 17-12 Oracle9i Reports Developer: Build Reports

Using a Graph Hyperlink .....................................................................................................................................................

Using a Graph Hyperlink Oracle9i Reports supports the ability to add a hyperlink to a graph embedded in a report. More specifically, the hyperlink can be different for each section of the graph. For example, a user may click on a particular slice of a pie graph, and drill down to another report to see detailed information. Usage Notes • You can set another report as the graph hyperlink. This second report will run from your first report output. • The Additional Attributes (HTML) property for Parameter Form fields cannot be used to add additional HTML attributes to a link defined by a graph hyperlink. • You cannot set a graph hyperlink by using PL/SQL. • You must set the Hyperlink Destination property for a graph hyperlink that specifies another layout object as its destination. Examples • A document on the same machine: file:///C|corp_policy/car/lease.pdf

•

A destination within the current report: #sales_rep&

•

Any URL: http://wlord-pc.us.oracle.com:7777/myexamples/ emp.jsp?server=rep1-wlord&userid=hr/hr@wl81& destype=cache&desformat=htmlcss&emp_id=&EMPLOYEE_ID

Technical Note When delimiting parameters in a URL, you can use the ampersand (&) or the plus sign (+). The ampersand is more commonly used and recommended. The ampersand immediately preceding EMPLOYEE_ID indicates substitution.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 17-13

Lesson 17: Embedding a Graph in a Report .....................................................................................................................................................

Summary

In this lesson, you should have learned how to: • Build a new graph by invoking the Graph Wizard to build and embed a graph in a paper report • Customize a graph in a Web report using the rw:graph tag • Use a graph hyperlink for drilling down

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 17-14 Oracle9i Reports Developer: Build Reports

Summary .....................................................................................................................................................

Summary • • • •

Use the Graph Wizard to create and embed a simple graph in your report. Reenter the Graph Wizard to make changes to the graph object, such as its position in the report. Customize a graph in a Web report by editing the attributes of the Reports JSP custom tag, rw:graph. Use the graph hyperlink property to implement drill-down functionality.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 17-15

Lesson 17: Embedding a Graph in a Report .....................................................................................................................................................

Practice 17 Overview

• Using the Graph Wizard to create a graph • Altering the graph in the Graph Wizard • Creating graphs that print once for each record of a group • Adding a graph to a Web layout • Customizing a graph in a Web report

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 17-16 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 17 .....................................................................................................................................................

Practice Session Overview: Lesson 17 This practice session contains: • Using the Graph Wizard to create a graph in an existing report • Altering the graph with the Graph Wizard • Creating graphs in an existing report that will be printed once for each record of a group • Creating a graph for a Web report • Customizing a graph for a Web report Introduction In this practice session you open existing reports to add graphs using the Graph Wizard. You reenter the Graph Wizard to change the characteristics of a graph. You will also add and customize a graph in a Web report.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 17-17

Lesson 17: Embedding a Graph in a Report .....................................................................................................................................................

Practice Session: Lesson 17 1 Open report p17q1a.jsp.

Run the paper layout to become familiar with the report. b Rename SumCust_totalPersales_rep_id to Total_Sales (a shorter, more meaningful name looks better in a chart). c Use the Graph Wizard to create a line chart. a

Graph Type Graph Subtype Position Category Value

Bar Bar at the end of the report sales_rep_id total_sales

Run the paper layout to test. Resize the graph in the Paper Layout, if necessary. e Save report as p17q1.jsp. 2 Reenter the Graph Wizard to change the characteristics of the graph created in the previous exercise. a Give the graph the following characteristics: d

Title Graph Type Graph Subtype

Order Total per Sales Rep Horizontal Bar Bar

Run the paper layout to test. c Save report to p17q2.jsp. 3 Continue with report p17q2.jsp. a Run the Web layout. What do you see? b Update the Web source with the correct column name. c Run the Web layout again to test. d Add a graph to the Web source. Position the graph at the beginning of the report. b

Graph Type Graph Subtype Category Value Title e

Bar Bar sales_rep_id total_sales Order Total per Sales Rep

Run the Web layout to test. Save the report as p17q3.jsp.

...................................................................................................................................................... 17-18 Oracle9i Reports Developer: Build Reports

Practice Session: Lesson 17 .....................................................................................................................................................

4 Open report p9q3.jsp. a

Use the Graph Wizard to create a pie chart for the data group G_PRODUCT. Invoke the Graph Wizard with the focus on the Paper Layout.

Graph Type Graph Subtype Data Group Category Value Graph Frequency

Pie Pie G_PRODUCT product_id prod_total Once per Name

In the Paper Layout, ensure that the graph is postitioned beneath the product group. c Run the paper layout and test the report. d Save the report as p17q4.jsp. 5 Continue with report p17q4.jsp. a Add a bar graph to the Web source for the data group G_PRODUCT. Use the same data group, category, and value settings as in the previous exercise. Hint: You want to insert the graph just before the termination of the rw:foreach tag for the group G_NAME. b Run the Web layout to test. Ensure that a graph displays for each sales respresentative. c Resize the graph to a width of 800 and a height of 450. d Save the report as p17q5.jsp. Run the Web layout to test. b

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 17-19

Lesson 17: Embedding a Graph in a Report .....................................................................................................................................................

...................................................................................................................................................... 17-20 Oracle9i Reports Developer: Build Reports

18 ................................

Enhancing Matrix Reports

Lesson 18: Enhancing Matrix Reports .....................................................................................................................................................

Objectives

After completing this lesson, you should be able to do the following: • Design a matrix Data Model • Design a matrix Paper Layout • Modify a matrix structure • Create and modify matrix summaries • Create a matrix with group report • Create a nested matrix report

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Topic Timing Lecture 50 minutes Practice 45 minutes Total 95 minutes

...................................................................................................................................................... 18-2 Oracle9i Reports Developer: Build Reports

Overview .....................................................................................................................................................

Overview Introduction Matrix or cross product reports show cells of information that relate to values appearing across the top and down the left side of the report. In an earlier lesson, you created a matrix report using the Report Wizard. In this lesson, you modify the Data Model and layout objects to enhance your matrix reports. Objectives After completing this lesson, you should be able to do the following: • Design a matrix Data Model • Design a matrix Paper Layout • Modify a matrix structure • Create additional matrix summaries • Create a matrix with group report • Create a nested matrix report

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 18-3

Lesson 18: Enhancing Matrix Reports .....................................................................................................................................................

The Matrix Data Model

1

4

2

3

Copyright © Oracle Corporation, 2002. All rights reserved.

1 2

Cross product group Row and column groups

3 4

Cell group Cell column is the source of a cross product summary that becomes the cell content

Instructor Note Demonstration: l18mat.rdf. Mention that the Wizard creates a single-query matrix. You can build multiple-query matrices, but it is not often necessary unless you have very complex data with large queries and outer joins that are difficult to combine in one query. This course discusses single-query models only. Step through the Wizard and explain each of the groups and matrix summaries above. Carefully explain the Salary column and that this is used to calculate the SumSalary value in the cross product group. SumSalary becomes the cell, not Salary itself. For large reports, it is more efficient to calculate the summary in the query if you do not require the individual salary records. However, you can still create the cell with a summary as it has some useful properties, such as being able to use Value if Null, which is explained later.

...................................................................................................................................................... 18-4 Oracle9i Reports Developer: Build Reports

The Matrix Data Model .....................................................................................................................................................

The Matrix Data Model Introduction To maintain and enhance the structure of a matrix report, you must understand the Data Model objects and their interdependency. What Does the Wizard Create? The Data Model opposite is the matrix report created in an earlier lesson. It demonstrates the structure of a simple Matrix report. Simple Matrix Style The Report Wizard creates three basic data groups: • One row of record values (Department_Name) • One column of record values (Job_ID) • One group of cells, where each cell contains data related to each row and column combination. There is an extra group that surrounds the row and column groups. It is called the cross product group and contains the summary column that forms each cell value, SumSalary, as well as the summaries to total the row and column values.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 18-5

Lesson 18: Enhancing Matrix Reports .....................................................................................................................................................

The Matrix Paper Layout

Job Title

F_job_id

Dept Name

Sumsalary

F_department_na

F_Sumsalary

1

2

3

Copyright © Oracle Corporation, 2002. All rights reserved.

1

Repeating frame for rows (Down direction)

2

Repeating frame for columns (Across)

3

Matrix object, the intersection of the two repeating frames

Instructor Note Demonstration: Continue with l18mat.rdf. Show the Paper Layout. This matrix report was created with a special template that includes additional spacing. No extra objects have been added, but the layout has been adjusted so that it is easier to explain and understand the objects and layers in the Paper Layout. As well as the repeating frames, point out the position of the summaries. Details for creating the matrix object are also discussed later in this lesson, with the nested matrix layout.

...................................................................................................................................................... 18-6 Oracle9i Reports Developer: Build Reports

The Matrix Paper Layout .....................................................................................................................................................

The Matrix Paper Layout Introduction Although the supplied templates create good-looking, professional report output, they do not help to explain the paper layout objects in a classroom environment. The number of objects and their interdependency are more complex in matrix reports than in the other styles covered so far. This section explains the objects by showing an expanded paper layout where each outer object is slightly larger than its contents. What Does the Wizard Create? The paper layout for a matrix report is more complex than other styles because of the special relationship between the row and column groups and the cell values. Simple Matrix Style The Report Wizard creates the following objects: • One repeating frame with print direction of Down to hold the row values • One repeating frame with print direction of Across to hold the column values • A field for the summary of the cell values; the source is SumSalary, which resides in the cross product group • Fields for the row and column values, as well as for the row, column, and report summaries if applicable • A matrix object that corresponds to the cross product group; this object intersects the row and column repeating frames, and contains the cell field

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 18-7

Lesson 18: Enhancing Matrix Reports .....................................................................................................................................................

Creating Matrix Summaries

F_CITY

Job Title

2

F_job_id

Dept Name

Sumsalary

F_name

F_Sumsalary

F_SumsalaryPerDEPT

F_SumsalaryPerJOB_I

F_SumsalaryPerCITY

1

F_SumsalaryPer

3

Copyright © Oracle Corporation, 2002. All rights reserved.

1 2

Product Order = G_DEPARTMENT_NAME Product Order = G_JOB_ID

3

Product Order = G_DEPARTMENT_NAME G_JOB_ID

Instructor Note Remember that you can set column properties from either the column or the paper layout field’s Property Inspector. When demonstrating, use the field, because all necessary properties are available. Regarding Product Order for the matrix cell: The default value for this property is , for example G_DEPARTMENT_NAME G_JOB_ID. The print direction for the group G_DEPARTMENT_NAME is down, and the print direction for the group G_JOB_ID is across.

...................................................................................................................................................... 18-8 Oracle9i Reports Developer: Build Reports

Creating Matrix Summaries .....................................................................................................................................................

Creating Matrix Summaries Introduction The Report Wizard allows you to create the summary for the cell values and the row or column summaries. These summaries are inside the cross product group (but outside the row and column groups). This section discusses an extra property that applies only to cross product summaries, the Product Order property. Setting the Product Order The Wizard sets a sensible default for this property. You do not need to alter the setting, but you need to understand this property if you create complex matrix reports, or add columns to an existing matrix report. The product order defines the order in which Reports Builder evaluates the groups in the cross product for a summary. Product Order also defines the frequency of a summary, formula, or placeholder column in a cross product group. That is, the column has one value for each combination of values in its Product Order groups. The Product Order list of values provides all possible group combinations. In the matrix report: • F_SumSalPerDEPARTMENT_NAME has one value for each department (G_DEPARTMENT_NAME). • F_SumSalPerJOB_ID has one value for each job ID (G_JOB_ID). • F_SumSALARY, the matrix cell, has one value for each combination of G_DEPARTMENT_NAME and G_JOB_ID, so the Product Order is G_DEPARTMENT_NAME G_JOB_ID. Source Column F_SumSALPerDEPARTMEN_ NAME F_Sum_SALPerJOB_ID F_SumSALARY

Reset At G_DEPARTMENT_ NAME G_JOB_ID G_JOB_ID

Product Order G_DEPARTMENT_NAME G_JOB_ID G_DEPARTMENT_NAME G_JOB_ID

The Reset At option has the same effect as in other report styles. When you create summaries with the Wizard, each summary resets for each occurrence of the group. For example, the department summary resets for each department. If you want the summary values to accumulate throughout the report, change Reset At to a higher level, such as Report.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 18-9

Lesson 18: Enhancing Matrix Reports .....................................................................................................................................................

Creating the Matrix Manually

• Using the Data Wizard: – Select the Matrix query check box – Step through the wizard

• Entirely manual: – Select the appropriate query tool – Create extra groups – Create the cross product group

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 18-10 Oracle9i Reports Developer: Build Reports

Creating the Simple Matrix Report Manually .....................................................................................................................................................

Creating the Simple Matrix Report Manually You may want to create the matrix report manually. You can use the Data Wizard or you could do everything entirely manually. Using the Data Wizard 1 In the Object Navigator, create a new report and select Build a new report manually from the dialog box. 2 In the Data Model view, right mouse click and select Data Wizard from the pop-up menu. 3 On the Query Name page, select the Matrix query check box. 4 Enter the query. 5 Step through the Wizard to determine the columns for the row, column, cell, and totals. 6 Invoke the Report Wizard to create the paper layout. Building Entirely Manually 1 In the Object Navigator, create a new report and select Build a new report manually from the dialog box. 2 In the Data Model, click theappropriate tool and move the cursor in the Data Model editor. 3 Enter the query. 4 Drag the title bar of the single group object down to create some space. 5 Drag the “row” column up left into the space you just created. 6 Drag the “column” column up right into the space you just created. 7 Click the vertical toolbar, click the Cross Product tool, and drag a box around the two groups you want to include in the cross product group. 8 Invoke the Report Wizard to create the paper layout.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 18-11

Lesson 18: Enhancing Matrix Reports .....................................................................................................................................................

The Matrix with Group Data Model

1

4 5

2

3

Copyright © Oracle Corporation, 2002. All rights reserved.

The Matrix with Group Layout

Region:

F_CITY

Job Title Dept Name F_department

F_job_id Sumsalary Total: F_Sumsalary F_SumsalaryPerTITLE

Total:

F _SumsalaryPerDEPA

F _SumsalaryPerCITY

F_SumsalaryPer

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Demonstration: l18matgrp.rdf.

...................................................................................................................................................... 18-12 Oracle9i Reports Developer: Build Reports

Building a Matrix with Group Report .....................................................................................................................................................

Building a Matrix with Group Report A matrix with group report consists of a break group that contains a simple matrix structure. For each break value, different row and column values might apply. In a single query Data Model, each matrix contains only the applicable rows and columns for the break value. The Data Model The Data Model is identical to the simple matrix, except for the addition of a break group, similar to a master-detail hierarchy. The break group exists one level above, and outside, the matrix group. The entire cross product group repeats for each record in the break group. The Paper Layout The Matrix with Group style is identical to the matrix style except for the addition of a repeating frame that surrounds the entire matrix layout and corresponds to the break group in the Data Model.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 18-13

Lesson 18: Enhancing Matrix Reports .....................................................................................................................................................

Building a Nested Matrix

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Demonstration: l18nest.rdf. This matrix was also built using the template with additional spaces. Remind students that Oracle9i Reports Developer supports single-query, nonsparse nested matrix reports (suppressing unnecessary blank rows and columns). To achieve this, set the Child Dimension group property to Yes, indicating that a group is a child dimension group in the nested matrix Data Model. Note that the lines between groups inside the cross product group are now solid instead of dotted.

...................................................................................................................................................... 18-14 Oracle9i Reports Developer: Build Reports

Building a Nested Matrix .....................................................................................................................................................

Building a Nested Matrix Introduction The nested matrix style, which you create with the Wizard by adding an extra level in the Rows tab, has some specific features. This section briefly describes the Data Model and Paper Layout, pointing out the differences from the Matrix with Group style. It also explains how summaries evaluate the extra group level of a nested matrix. What is in the Nested Matrix Data Model? The Wizard creates the same objects as for a simple matrix structure. In addition, it creates an additional break group for each level of nesting. The cross product group encloses the additional break groups as well as the two original dimension groups of the rows and columns. Creating the Cross Product Group If you enhance the existing Data Model to include an additional level of nesting, the existing cross product group does not recognize any newly created group. You must create the cross product group after you create all the groups that you want in the nested matrix. How to Re-create the Cross Product Group 1 Delete the existing cross product group. Note: The cross product summaries are automatically deleted. 2 Create the new groups that you need. 3 Re-create the cross product group. Use the cross product tool (AxB) to click and drag a region surrounding the break groups and the two dimension groups (rows and columns). 4 Use the reenterable Wizard to re-create the summaries. Differences Between a Nested Matrix and a Matrix with Group • Matrix with Group Data Model: The cross product group is the child of a parent break group at a level above and outside the cross product. • Nested Matrix Data Model: The cross product contains three or more groups, one or more levels of break group and two dimensional groups to form rows and columns. Note: You can mix these styles. That is, you can create a Matrix with Group report where the matrix has several nested levels within it.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 18-15

Lesson 18: Enhancing Matrix Reports .....................................................................................................................................................

Nested Matrix Layout

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Demonstration: Continue with l18nest.rdf, and explain the extra repeating frame. Use this report to explain the matrix object in more detail, and how to create it. Show the matrix nodes in the Object Navigator. Delete one of the matrix objects. The Dimension and Frequency nodes disappear. Re-create the matrix object. The nodes reappear, but there is no explicit matrix node for the matrix object name. In the paper layout, select the matrix object and open the Property Inspector.

...................................................................................................................................................... 18-16 Oracle9i Reports Developer: Build Reports

Building a Nested Matrix .....................................................................................................................................................

What Is in the Nested Matrix Paper Layout? The Report Wizard creates the same objects as for a simple matrix structure. It also creates the following objects: • One repeating frame for each additional break group, nested within the same overall matrix structure • One matrix object for each additional break group The exact appearance depends on whether the extra level is in the Row or in the Column tab. The diagram opposite shows nested rows. In the Object Navigator, you can see the matrix structure in the Dimension and Frequency nodes. These nodes exist only if there is a matrix. You do not see a node for the matrix object name, but it does have properties. You can open the Property Inspector in the Paper Layout only. Creating a Matrix Object When possible, use the Wizard to create your matrix reports, because the layout consists of many layers and interdependent objects. If you resize the repeating frames, take care to maintain the intersection between them, otherwise you might destroy the matrix object. Reports Builder gives you a warning message if this is likely to happen. If you destroy the matrix object, or want to create the matrix structure yourself, you must re-create the matrix object. Each matrix object intersects and determines the relationship between two and only two repeating frames. How to Create the Matrix Object 1 Make sure that the two related repeating frames intersect each other correctly. 2 Select both repeating frames [Shift]-Click. Note: If you do not select both frames, you receive an error message: REP-3100: Matrix must include at least 1 Down and 1 Across repeating frame.

3 Select Insert > Layout Matrix. 4 Move the matrix object back through the layout layers until it is behind the cell

field (Layout > Move Backward). This step is not necessary to make the report run, but it is useful to see the cell field if the matrix has a solid fill.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 18-17

Lesson 18: Enhancing Matrix Reports .....................................................................................................................................................

Creating Matrix Summaries

F_SumSALARY

F_SumSALPerDEPA

F_Sum_CITY

F_SumSALPerJOB_

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Demonstration: Modify l18nest (instructions below), or show the end result in l18nstsm.rdf. Field names on the slide and in the report have been changed so that they fit on the page, but are still meaningful and similar to the Wizard default names. (Also refer to hidden PowerPoint slide and notes page for additional teaching aids.) Show the Property Inspector for the different summaries and explain Product Order. Create the additional summary to show region subtotals: 1 In Data Model, create another summary in the cross product group. Name: CS_Sum_CITY. Source: SumSALARY. Product Order: G_CITY. Reset At: G_CITY. 2 In the Paper Layout, make sure that Flex Mode is on, and then enlarge the city repeating frame downward to make room for the field. 3 Create a field using the Field tool. Name: F_Sum_CITY. Source: CS_Sum_CITYSum. Reset At: G_CITY. Product Order: G_CITY. 4 Run the paper layout.

...................................................................................................................................................... 18-18 Oracle9i Reports Developer: Build Reports

Building a Nested Matrix .....................................................................................................................................................

Creating Nested Matrix Summaries The Wizard creates nested summaries in the cross product group. If you create or alter a nested summary, you must set the Product Order correctly. Always test the report output very carefully to ensure that the summaries are displaying exactly the value you expect. Setting the Product Order The Product Order indicates the way in which Reports Builder evaluates a summary. Product Order also determines the position (frequency) of the summary if you use the reenterable Wizard. The diagram opposite shows three summary fields created by the Wizard: • F_SumSALPerJOB_ID: Total salary for each column of job ID. Displayed once for each job ID. • F_SumSALPerDEPARTMENT_NAME: Total salary for each department row nested within a specific region row. Displayed once for each combination of region/department. • F_SumSALARY: The cell value. Displayed once for each combination of city/ department name/job ID. Field Name F_SumSALPerJOB_ID F_SumSALPerDEPARTMENT_NAME F_SumSALARY

Reset At G_JOB_ID G_DEPARTMENT_ NAME G_DEPARTMENT_ NAME

Product Order G_JOB_ID G_DEPARTMENT_NAME G_CITY G_JOB_ID G_DEPARTMENT_NAME

The diagram also shows a fourth summary, F_Sum_CITY, which you might want to create yourself to produce a subtotal for each region. 1 Create another summary in the cross product group. 2 Create a layout field and place it as shown in the diagram, in the R_G_CITY repeating frame (use the Object Navigator to identify it). The following table shows the property settings. Field Name F_Sum_CITY

Reset At G_CITY

Product Order G_CITY

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 18-19

Lesson 18: Enhancing Matrix Reports .....................................................................................................................................................

Displaying Zeros

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Demonstration: Value if Null is available either from the summary column Property Inspector or from the field’s Property Inspector, in the Column section. The quickest and easiest way to access the Property Inspector is to double-click the cell field in the Paper Design view. Optional demonstrations: Show how to add the text TOTALS about the department totals column on the right side of the output. Add it in the Paper Design, and then show it in the Paper Layout. Show how to rotate the column values. You cannot rotate fields, so delete the column field (F_JOB_ID). Use the text tool to create an object in the column repeating frame and enter &JOB_ID (direct reference to the column value). Now you can rotate this object to display it on its side and take less room for each column.

...................................................................................................................................................... 18-20 Oracle9i Reports Developer: Build Reports

Building a Nested Matrix .....................................................................................................................................................

Displaying Zeros in Cells with No Value The field that displays each cell of the matrix is sourced to a summary column, such as SumSalary. If there is no value for a particular row/column combination, the summary column is null and therefore the cell is displayed as a blank field. You can very simply change the blank to zero or any default value you choose. To display zeros, open the Property Inspector for either the cell field or the SumSalary summary column and set the Value if Null property to zero.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 18-21

Lesson 18: Enhancing Matrix Reports .....................................................................................................................................................

Summary

In this lesson, you should have learned how to: • Create matrix reports with the Report Wizard • Describe the relationship between the data and layout in a cross product structure • Identify and describe matrix-specific elements: – Cross product group – Product Order property – Matrix layout object

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 18-22 Oracle9i Reports Developer: Build Reports

Summary .....................................................................................................................................................

Summary Letting the Wizard and Template Work for You Use the Report Wizard to create your matrix, even if you need to modify it later. Allowing the Wizard to create the initial Paper Layout saves you a lot of time. If you make many modifications to your Wizard reports, consider creating or modifying a template to include object spacing horizontally and vertically, between frames, fields, and so on. This helps you to select and manipulate the objects in the Paper Layout. Note: You can override the default template properties for the Matrix and Matrix with Group sections of an existing template. Matrix Structure The Data Model and Paper Layout of a matrix report contain special features, and relationships between objects, to reflect the cross product structure of the report. Matrix-Specific Objects and Properties Matrix reports include special objects and properties: • Data Model: Cross product group; Product Order property • Paper Layout: Matrix object

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 18-23

Lesson 18: Enhancing Matrix Reports .....................................................................................................................................................

Practice 18 Overview

• • • •

Opening and changing an existing matrix report Building a nested matrix report Adding summaries to a nested matrix report Displaying zeros for summary values and restricting columns per page • Displaying more meaningful column information and rotating the text

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 18-24 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 18 .....................................................................................................................................................

Practice Session Overview: Lesson 18 This practice session contains: • Opening and changing an existing matrix report • Building a nested matrix report • Adding summaries to the nested matrix report • Displaying zeros for summary values and restricting the number of columns per page • Displaying more meaningful column information and rotating the text Introduction In this practice session, you use an existing matrix report to investigate the different methods of displaying the cell values and the implications. You use an existing SQL query to create a nested matrix report. You then add row- and column-level summaries to the report and display zeros for null values. If you have time, include the product names instead of the product IDs and rotate the names.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 18-25

Lesson 18: Enhancing Matrix Reports .....................................................................................................................................................

Practice Session: Lesson 18 1 Open report p4q6.jsp.

How many repeating frames do you have? b Change the properties of the cell object to display zero for a null value. c Run the paper layout to test the report. d Close the report without saving it. 2 Create a new report, importing the query p4q6.sql from your working directory. a Select the following settings: a

Report Style Query Matrix Row Fields Matrix Column Fields Matrix Cell Fields Matrix Totals

Matrix Import Query p4q6.sql Level 1 - month Level 2 - name product_id Sum of total_value Sum of the cell field

b Change the labels and widths for the following fields: Field Label name Customer product_id Product month Month SumTotal_value Total All Summaries Total:

Width 10 5 4 5 5

Select a template and finish the Wizard. c Increase the width of the Main Section of the report to 17 inches. d Save the report as p18q2.jsp. Run the paper layout. e Which summaries were created?

...................................................................................................................................................... 18-26 Oracle9i Reports Developer: Build Reports

Practice Session: Lesson 18 .....................................................................................................................................................

f

Use the Data Model view to create summaries for each month and for the level month and product.

Property Name Product Order Function Source Reset At

Value SumMonth G_MONTH Sum SumTOTAL_VALUE G_MONTH

Property Name Product Order Function Source Reset At

Value SumMonthProduct G_MONTH G_PRODUCT_ID Sum SumTOTAL_VALUE G_PRODUCT_ID

Use the Paper Layout view to create the fields. You must expand the appropriate repeating frame. h Run the paper layout to test the report. i Save the report as p18q2.jsp. Do not close it. 3 Continue with the report from the previous exercise. a Add zeros for null values in the cells. b Ensure that the report shows only three product columns on each page. c Format all numeric data to be right aligned. d Add commas and two decimal places where appropriate. e Run the paper layout to test the report. f Save the report as p18q3.jsp and close it. g

If You Have Time 4 Open the report as p18q3.jsp. a Make the necessary changes in the Data Model view to include the product name. b In the Paper Layout view, delete the product_id field and insert a boilerplate object to display the product name. Rotate this object by 90 degrees. Note: You may have to expand the R_G_PRODUCT_ID frame and the outermost frame. Watch out for frequency errors. c Run the paper layout. Save the report as p18q4.jsp.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 18-27

Lesson 18: Enhancing Matrix Reports .....................................................................................................................................................

...................................................................................................................................................... 18-28 Oracle9i Reports Developer: Build Reports

19 ................................

Coding PL/SQL Triggers

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Objectives

After completing this lesson, you should be able to do the following: • Describe the different types of triggers • • • •

Describe sample uses of triggers Write and reference common code Create a PL/SQL library Publish a report as a result of a database event

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Topic Timing Lecture 50 minutes Practice 45 minutes Total 95 minutes

...................................................................................................................................................... 19-2 Oracle9i Reports Developer: Build Reports

Overview .....................................................................................................................................................

Overview Introduction This lesson identifies different trigger types and creates conditional trigger code to control report output. It also explains how to improve productivity and maintenance by sharing and reusing commonly referenced code. Finally, you will learn about publishing reports based on database events. Objectives After completing this lesson, you should be able to do the following: • Describe the different types of triggers • Describe sample uses of the different types of triggers • Write and reference common code • Create and reference a PL/SQL library • Publish a report based on a database event

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-3

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Types of Triggers in Reports

• Report: – Five triggers – Report Triggers node in Object Navigator

• Data Model: – Formula (column) – Filter (group) – Parameter validation

• Layout: Format trigger on most objects

Copyright © Oracle Corporation, 2002. All rights reserved.

Trigger Code

Boolean: true false

Character Number Date

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 19-4 Oracle9i Reports Developer: Build Reports

Types of Triggers in Reports .....................................................................................................................................................

Types of Triggers in Reports Introduction You can code PL/SQL in a variety of triggers in a report to provide conditional or additional processing at run time. This lesson discusses examples of each type of PL/ SQL trigger. The Three Trigger Types • Report: – A set of five report-level triggers. Each trigger fires at a different stage of the report execution. – Access report triggers from the Report Triggers node in the Object Navigator. • Data Model: – Column formula fires each time the column is processed. – Group filter fires for each record in the group. – Parameter validation fires when the run-time parameter form is displayed and when the user leaves the parameter field. – Access Data Model triggers in the Property Inspector of a Data Model object— column, group, or parameter—or the corresponding layout object—field, repeating frame, or parameter field. • Layout: – Format triggers on most paper layout objects (excluding anchors). Each trigger fires as the layout object is processed. – Access format triggers in the Property Inspector of a paper layout object. – For Web layouts, use the formatTrigger attribute in the rw:field tag. Note: Reports Builder does not allow data manipulation language (DML) commands—INSERT, DELETE, UPDATE—in layout format triggers. Trigger Code When you create a trigger such as a report trigger or a PL/SQL formula, the Program Unit editor supplies a template for the function block, giving the basic syntax that you require. You can change the name of the function. The value that you return must match the return data type in the function declaration.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-5

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Using Report Triggers Before Parameter Form

F

2

1 F After Parameter Form

3

If readonly=yes

If > 1 page Before Report F

page1

Between Pages

page2

After Report

F

Copyright © Oracle Corporation, 2002. All rights reserved.

1

2

3

If After Parameter Form returns FALSE, the cursor returns to the run-time parameter form. If the run-time parameter form is not displayed, control returns to the calling program. If READONLY=Yes, Reports Builder implicitly sets the readonly transaction (SET TRANSACTION READONLY) after the Before Report Trigger fires. If READONLY=Yes, Reports Builder implicitly ends the read-only transaction (COMMIT) after the final page and before the After Report Trigger fires.

Instructor Note Report triggers appear in the Object Navigator in the order in which they fire.

...................................................................................................................................................... 19-6 Oracle9i Reports Developer: Build Reports

Using Report Triggers .....................................................................................................................................................

Using Report Triggers Introduction Report triggers enable you to execute PL/SQL functions at specific times during the execution and formatting of your report. Using these triggers, you can: • Customize the formatting of your report • Perform initialization tasks • Access the database Report triggers must return a Boolean value—true or false. Accessing the Report Triggers You can access the report triggers from the Object Navigator. Open the Report Triggers node and double-click the object icon of the required trigger to open the program unit. Trigger Before Parameter Form

Fires Before run-time parameter form is displayed; fires even if parameter form is suppressed

After Parameter Form

After run-time parameter form is displayed; fires even if parameter form is suppressed

Before Report

Before a report executes; after queries are parsed, but before records are fetched Before each page is formatted, except the first page; in Previewer, fires only once for each page, even if you revisit the page After report output is sent to its destination; fires on success only

Between Pages

After Report

Use to Access and change parameter values (initial or command line), global variables; create temporary tables; insert into tables to be queried Access and change parameter values (initial, command line, or user-entered); create temporary tables; insert into tables to be queried Carry out initialization procedures Carry out customized page formatting; insert or update values in tables

Clean up initial processing, such as deleting temporary tables

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-7

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Using Report Triggers

• After Parameter Form Example: Build dynamic WHERE clause FUNCTION AfterPForm RETURN BOOLEAN IS BEGIN IF :p_customer IS NULL THEN :p_where_clause := ' '; ELSE :p_where_clause := 'where id >= :p_customer'; END IF; RETURN(TRUE); END;

• Query syntax SELECT CUSTOMER_ID, CUSTOMER_NAME FROM CUSTOMERS &p_where_clause ORDER BY CUSTOMER_NAME Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Stress the improved efficiency of a dynamic WHERE clause, used when users sometimes require restricted records and sometimes require all records. Write on whiteboard: WHERE ID = :P_CUSTOMER OR :P_CUSTOMER IS NULL

Then put a big cross beside this code. This is what some developers would do to return all customers if the parameter is NULL, but stress this is not efficient. Ask why not. The OR clause prevents the Oracle server from using an index, whether or not the user specifies a value. The dynamic clause ensures that the WHERE clause allows an index to be used if the user specifies a value. Point out that the code returns TRUE in both cases, so this function is transparent to the user.

...................................................................................................................................................... 19-8 Oracle9i Reports Developer: Build Reports

Using Report Triggers .....................................................................................................................................................

Creating a Dynamic WHERE Clause Use the After Parameter Form report trigger to build a dynamic WHERE clause depending on the value of a bind parameter that the user enters at run time. There are two possibilities at run time: • The bind parameter is NOT NULL. The WHERE clause restricts the query; therefore the Oracle server makes use of an existing index when retrieving data. • The bind parameter is NULL. The WHERE clause is also NULL; therefore the Oracle server uses a full table scan to retrieve all records.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-9

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Using Data Model Triggers

Group Filter • Restrict records in a group • Perform PL/SQL for each record FUNCTION G_empGroupFilter RETURN BOOLEAN IS BEGIN IF :department_name = 'Operations' AND :salary > 5000 THEN RETURN(my_function); ELSE RETURN(TRUE); END IF; END;

• PL/SQL filters result in all records being fetched

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 19-10 Oracle9i Reports Developer: Build Reports

Using Data Model Triggers .....................................................................................................................................................

Using Data Model Triggers Introduction This section gives examples of: • Using a PL/SQL group filter • Validating a parameter value Using a PL/SQL Group Filter Use a group filter when you want to do the following: • Restrict records in a specific group, depending on other processing in the report; that is, when a WHERE clause in a query is not applicable • Perform some PL/SQL processing for each record in a group A group filter is useful when you want to restrict group values of a lower group in the group hierarchy. The function must return a boolean value: • True: The record is included. • False: The record is excluded. Reports Builder fetches all records and applies the group filter to each record. Therefore, avoid group filters as a method of restricting many records in a group. Note: You cannot add a filter to a matrix cross product group. Example Use a group filter that calls a function for some records in the G_emp group, depending on the department in the master group. 1 Open the Property Inspector for the G_emp group. 2 Change Group Filter to PL/SQL. The PL/SQL Filter property appears. 3 Select the PL/SQL Filter property to open the PL/SQL editor. 4 Enter the code. This filter code must return a boolean value of true or false. Note: It is more efficient to use a WHERE clause in a query when possible. You cannot use a WHERE clause in this case, because you want to process all records and call the function for some.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-11

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Using Data Model Triggers

Parameter Validation • Example: Do not allow report output to be sent directly to a printer. FUNCTION DESTYPEValidTrigger RETURN BOOLEAN IS BEGIN IF :DESTYPE = 'Printer' THEN RETURN(FALSE); ELSE RETURN(TRUE); END IF; END;

• You cannot reassign values to parameters or columns in this trigger. Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Point out that the values for DESTYPE are case-sensitive; you must enter the value with an initial capital, otherwise the condition is never true.

...................................................................................................................................................... 19-12 Oracle9i Reports Developer: Build Reports

Using Data Model Triggers .....................................................................................................................................................

Validating a Parameter Value Both system parameters and user parameters have a Validation Trigger property in which you can write a PL/SQL function to validate the value of the parameter. The trigger code must return a boolean value—true or false—depending on whether the validation is successful or not. If the returned value is false, the cursor remains in the parameter and the user has the following two options: • Enter a different value • Cancel the report You can test the value of a parameter, but you cannot change the value. If you want to assign a new value to the parameter, use the After Parameter Form report trigger. Example To prevent report output from being sent directly to a printer, test the value of the DESTYPE system parameter. 1 Open the Property Inspector for the DESTYPE parameter. 2 Select Validation Trigger to open the PL/SQL editor. 3 Enter the code and make sure that the value Printer has an initial capital.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-13

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Using Layout Triggers

Format triggers: • Exist on most layout objects • Can suppress an entire layout section (master group frame): No records fetched • Can suppress the display of individual records (repeating frame): All records fetched

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note This is a very brief introduction, to focus attention on the paper layout. The following pages contain examples of using a format trigger. However, it is worth making the point that a format trigger on a frame to suppress an entire layout can prevent data being retrieved, but a format trigger on a repeating frame to suppress individual records does not. Remind the students of conditional formatting, discussed earlier, and point out that a format trigger has been automatically created.

...................................................................................................................................................... 19-14 Oracle9i Reports Developer: Build Reports

Using Layout Triggers .....................................................................................................................................................

Using Layout Triggers Introduction Most paper layout objects can contain a format trigger; it is one of the common properties of a paper layout object. Format triggers enable you to modify the display of objects dynamically at run time or to suppress display altogether. The trigger must return a boolean value—true or false. The following pages discuss some examples of format triggers. Suppressing the Entire Paper Layout for a Query Because Reports is driven by the layout, you can use a format trigger on a group frame to conditionally suppress the entire layout associated with a query. In this case, the query does not retrieve any data. Suppressing Individual Records Do not use format triggers to suppress individual records that you can restrict on the Data Model. For example, a format trigger on a repeating frame does not prevent data from being retrieved, it simply determines whether or not you see it in the output. Therefore, it is more efficient to restrict individual records using the Maximum Records property in a query or a First type of group filter when possible. Note: Summaries compute against all data fetched by the Data Model. If you suppress records in the layout only, summaries give misleading results.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-15

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Displaying a Text String in Place of Column Headings Dept: 10 Dept:

f_deptno Name

1

Salary

Name Salary No employees in this department Dept: 40 No employees in this department 2

Copyright © Oracle Corporation, 2002. All rights reserved.

1 2

Boilerplate text: Position behind column heading frame, or position below and create collapsible anchor to column heading Column heading frame does not display; suppressed by format trigger

...................................................................................................................................................... 19-16 Oracle9i Reports Developer: Build Reports

Using Layout Triggers .....................................................................................................................................................

Coding a Format Trigger on a Frame Requirement: Display a Text String in Place of Column Headings Display a master-detail report for departments and employees. If a department has no employees, suppress the detail column heading frame and display a piece of text instead. Example 1 Create a summary column, :count_emp, in the master group that counts the number of employee records (reset = master group). 2 Create a piece of boilerplate text, “No employees in this department”. 3 Position the text behind the column heading frame. 4 Code a format trigger on the column heading: function hide_headings return boolean is begin if :count_emp = 0 then return(false); else return(true); end if; end;

5 Either code the reverse logic in the format trigger of the boilerplate text item

created in step 2 or ensure that the column heading frame has a solid fill. Note: You should code format triggers at the highest possible level of the paper layout hierarchy. The format trigger on the column headings frame suppresses all objects enclosed by the frame. Do not duplicate code unnecessarily by coding the same trigger on all individual objects in the frame; this can cause increased processing and maintenance overheads.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-17

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Dynamically Altering the Display of Records Employee Details Employee Details King

17-JUN-87 24000

Kochhar 21-SEP-89 17000

f_name f_date f_salary

De Haan 13-JAN-93 Employee17000 Details f_name

f_date King

2 1

17-JUN-87

Kochhar

21-SEP-89

De Haan

13-JAN-93

3

Copyright © Oracle Corporation, 2002. All rights reserved.

1 2 3

One repeating frame is positioned behind the other or positioned below with collapsing anchor Report output run by payment clerks Report output run by other employees

Instructor Note Explain the example using the diagram above. If you want to demonstrate it, you must modify the code, because there are no pay_clerks in the EMPLOYEES table. For example, you could test for your own userid.

...................................................................................................................................................... 19-18 Oracle9i Reports Developer: Build Reports

Using Layout Triggers .....................................................................................................................................................

Coding a Format Trigger on a Repeating Frame Requirement: Alter the Display of Records Dynamically In a report of employee details, display salaries if the user is a payment clerk, but hide salaries and alter the appearance of remaining fields when any other employee runs the report. Example 1 In the layout, create two repeating frames sourced by the same group. One repeating frame must contain a salary field, the other must not. Note: You can create two separate default layouts and modify the layout as required. 2 Place one repeating frame behind the other. 3 Add a format trigger to suppress the top repeating frame (the one that contains the salary field), or alternatively use conditional formatting: function pay_clerks_see_this return boolean is begin if :user_type = 'PAY_CLERK' then return(true); else return(false); end if; end;

Note: User_type can be a column or parameter that is initialized at run time. 4 Either code the reverse logic on the other repeating frame or ensure that the top repeating frame (containing salary) has a solid fill.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-19

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Dynamically Hiding Fields

Employee Details f_name f_date f_salary

Employee Details King

17-JUN-87 24000

1

Kochhar 21-SEP-89 17000 De Haan 13-JAN-93 Employee17000 Details King

17-JUN-87

Kochhar 21-SEP-89 De Haan 13-JAN-93

2

Copyright © Oracle Corporation, 2002. All rights reserved.

1 2

Report for payment clerks shows all fields Report for other employees shows gap where salary is hidden

Using a Format Trigger in a Web Layout … class="OraCellNumber"> F_SALARY …

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 19-20 Oracle9i Reports Developer: Build Reports

Using Layout Triggers .....................................................................................................................................................

Format Trigger on a Field Requirement: Dynamically Hide Fields Given the same employee details report as before, hide the salary field without rearranging the other fields in the repeating frame. The report contains a gap in the layout when the salary field is not displayed. Example Place the format trigger on the salary field to display it only when payment clerks run the report. The code is the same as before: function pay_clerks_see_this return boolean is begin if :user_type = 'PAY_CLERK' then return(true); else return(false); end if; end;

In this example, you do not need to create any additional layout object or code. Using a Format Trigger on a Field in a Web Layout In an earlier lesson, you learned that you can customize your Reports JSPs by editing the Web source and including attributes for the Reports custom JSP tags. Recall that the rw:field tag has an attribute called formatTrigger. You set the formatTrigger attribute to the name of a format trigger function. If the format trigger returns a Boolean value of true, the field is displayed according to the attributes specified in the trigger. The formatTrigger attribute is used to modify the display characteristics of the field and can only use specific SRW packaged functions to set the field’s attributes. You will learn about the SRW package in the next lesson.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-21

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Inserting Spacing Between Groups of Records f_1

f_2

f_3 1 2 4

3 FUNCTION spacing RETURN BOOLEAN IS BEGIN IF MOD(:count_column, 3) = 0 THEN RETURN(TRUE); ELSE RETURN(FALSE); END IF; END;

Copyright © Oracle Corporation, 2002. All rights reserved.

1

Transparent rectangle

3

2

Repeating frame, Vertical Elasticity = Variable

4

Summary column name (created in Data Model) Format trigger on transparent rectangle

Instructor Note Stress that the repeating frame must be variable. This example uses the modulus SQL function: IF MOD(:count_column, 3) = 0

Modulus returns the remainder of the first argument divided by the second argument. Therefore, the modulus is 0 for every third record. When the condition is true, the extra space is printed. Mention that the number can be supplied as a parameter. L19SPACE.RDF is a prebuilt example. Enter a number in the parameter P_SPACE. Spacing is output after that number of records.

...................................................................................................................................................... 19-22 Oracle9i Reports Developer: Build Reports

Using Layout Triggers .....................................................................................................................................................

Coding a Format Trigger on a Boilerplate Object Requirement: Insert Spacing Between Groups of Records Display additional spacing between a defined number of records; for example, group every three records together. Example 1 Create a transparent rectangle inside the repeating frame to increase spacing conditionally at run time. 2 In the repeating frame Property Inspector, set Vertical Elasticity to Variable. 3 In the Data Model, create a summary column named COUNT_COLUMN that counts the number of records retrieved (Reset=Report). 4 On the rectangle, create a format trigger that references the summary column, as shown on the opposite page. Note: You can vary the number of records in each group dynamically at run time by creating a parameter.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-23

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Writing Common Code

At the Report level: • Object Navigator, Program Units • Menu: Program > PL/SQL Editor In a library: • Object Navigator, PL/SQL Library • File > New: Create new library • File > Open: Add to existing library • Attach library to report

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Most developers make less use of PL/SQL code and libraries in reports than they do in forms. If students have attended the Oracle9i Forms Developer class, they will be familiar with this topic. Demonstration: Create a report-level program unit. Create a library. Copy the program unit to the library. Save the library. Attach the library to a report. Remind students that library program units cannot directly reference Reports variables. Point out that Reports Builder loads only the library program units that are referenced in the report. Remind students that you can also store code in the database as stored procedures and functions. Warn them that they must not confuse a library that is saved in the database with database stored procedures; they are not the same.

...................................................................................................................................................... 19-24 Oracle9i Reports Developer: Build Reports

Writing Common Code .....................................................................................................................................................

Writing Common Code Introduction If you use the same program unit in several places in a report, or in multiple reports, consider writing the code once as a report-level program unit, or in a PL/SQL library, and then call the program unit when you require it in your PL/SQL trigger code; this enables you to maintain the code in one place. If you have to write code that is not simple, then define the code at the report level. Remember that you can also code stored procedures and functions in the database; you must decide whether server-side or client-side processing is more efficient in each case. Two Ways to Create Report-Level Program Units • In the Object Navigator, select the Program Units node and create a new program unit. • From the menu, select Program > PL/SQL Editor. Click New to create a new program unit. Writing Code as Report-Level Program Units You can create a report-level program unit that contains a function, procedure, or package, and that you call from any object in the same report. You cannot reference a report-level program unit from a different report. Three Ways to Create Library Program Units • In the Object Navigator, select the PL/SQL Libraries node and create a new library. • From the menu, select File > New > PL/SQL Library. • From the menu, select File > Open to open an existing library and create additional program units. Referencing Code from a PL/SQL Library You can reference library code in a report trigger by attaching one or more libraries to one or more report definitions, which enables you to reference the same code in multiple reports. To attach a library to a report, open the report definition, select the Attached Libraries node in the Object Navigator, and add each library that you need in the report.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-25

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Event-Based Reporting

• Implemented through PL/SQL stored procedures • Uses include: – – – –

Running a report Displaying report status Canceling a report Managing parameter lists

Copyright © Oracle Corporation, 2002. All rights reserved.

Event-Based Reporting Oracle Advanced PL/SQL Workflow Queuing

Output Client

Reports Cache

Data model

Data interface

Reports Services

E-mail File

Printer

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 19-26 Oracle9i Reports Developer: Build Reports

Publish Reports Based on Database Events .....................................................................................................................................................

Publish Reports Based on Database Events Introduction Today, it is often desirable to blend automation into business processes through the invocation of behind-the-scenes procedures and functions. These behind-the-scenes tasks can include the spontaneous production of output such as an invoice that prints automatically when an order is processed, a Web site that is automatically updated with fresh data, or an automatic e-mail alerting an employee’s manager that the employee has submitted an expense report for approval. Consider this last scenario. When an employee submits an expense report, new data is inserted into the database. When this database event occurs, you want to alert the employee’s manager through an e-mail or a notification in his portal page that a submittal is awaiting his approval. With the Oracle9i Reports event-driven publishing API, this is possible. Event-Driven Publishing API The event-driven publishing API is a PL/SQL package that provides the basic functions required for the development of procedures that respond to events in the database. The procedures are called from a database trigger to run a report when an event occurs. The trigger invokes the Reports server and the job is submitted to the server. In addition to database events, the API can be integrated with Advanced Queuing (AQ) and Oracle Workflow. The API consists of several key elements: • The SRW Package contains all relevant procedures and functions for submitting, checking the status, and canceling jobs, as well as manipulating parameter lists. • The ParamList Type defines a parameter list. A parameter list is the main vehicle for passing values when submitting a job and is required for each job submittal. • The ParamList Object is required for such features as Advanced Queuing, where a parameter list must be stored in the database so that it can be passed along with a message. Technical Note Advanced Queuing is the message queuing functionality of the Oracle database. Oracle Workflow is a tool that manages complex user-based business processes. For more information refer to the Oracle Technology Network, http://otn.oracle.com. See Oracle9iAS Reports Services Publishing Reports to the Web manual for more information on the Event-Driven Publishing.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-27

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Invoking a Report from a Database Event

• Create a database trigger • Include a parameter list with the required entries: – – – –

GATEWAY SERVER REPORT USERID

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Oracle9iAS Reports Services is needed for event-based reporting. You cannot run an event-driven report with Reports Builder.

...................................................................................................................................................... 19-28 Oracle9i Reports Developer: Build Reports

Publish Reports Based on Database Events .....................................................................................................................................................

Invoking a Report from a Database Event Database triggers are the primary mechanism for invoking reports using the eventdriven publishing API.The Oracle database allows you to define various scopes of triggers that fire in response to various events. One way to use event-based reporting is to create automated processes that respond to certain types of changes to data in a table. Example: Send a report when an employee has submitted an expense report. The code for the trigger is below. CREATE TRIGGER EXP_REP_TRG AFTER INSERT OR UPDATE on EXP_REP FOR EACH ROW myPlist SRW_PARAMLIST; myIdent SRW.Job_Ident; BEGIN IF (:new.ExpStat = 'DONE') THEN myPlist := SRW_PARAMLIST(SRW_PARAMETER('','')); srw.add_parameter(myPlist,'GATEWAY','http://…'); srw.add_parameter(myPlist,'SERVER','repSVR'); srw.add_parameter(myPlist,'REPORT','alert.RDF'); srw.add_parameter(myPlist,'USERID','scott/tiger'); srw.add_parameter(myPlist,'ExpenseID',:new.ExpID); myIdent := srw.run_report(myPlist); END IF; END;

This trigger will fire after each insert or update on the EXP_REP table. When the status of an expense report changes to DONE, the parameter list is constructed and a report is invoked. The parameter list, myPlist, contains the necessary parameters for submitting the job: • GATEWAY provides the URL to the Reports Servlet that will be used to process the request. • SERVER identifies the name of the Reports Server to be used in conjunction with the servlet. • REPORT identifies the report file to be run. • USERID identifies the user ID and password of the person running the report. The API method RUN_REPORT takes the parameter list containing all vital information as input, creates and submits the request, and returns the job identification record. The information in the record can be used to check the status of the job.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-29

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Summary

In this lesson, you should have learned how to: • Select the appropriate trigger type for your requirement: Report, Data Model, or Layout • Identify the trigger and code needed for: – – – –

Building a dynamic WHERE clause Validating a parameter value Dynamically altering record display Suppressing null fields

• Use Report-level program units and create PL/SQL libraries for common code • Invoke a report from a database event

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 19-30 Oracle9i Reports Developer: Build Reports

Summary .....................................................................................................................................................

Summary Using PL/SQL Triggers Select the most applicable type of trigger for your requirement. Report-level triggers fire once for each of the five events during the report execution. Data Model triggers can affect which records are processed and how they are processed. Layout triggers affect the way in which objects are formatted. Objects That Must Return a Value The following table is a reminder of the Reports Builder triggers that must always return a value, and the values that are acceptable. Trigger Type

Valid Values

Result if False

Report trigger

True, False

Abort execution

Group filter

True, False

Do not process record

Validation trigger

True, False

Return to parameter form

Format trigger

True, False

Do not format object

PL/SQL Formula (column)

Computation: Character, Number, Date

N/A

Writing Common Code Use report-level program units or PL/SQL libraries for common code requirements to improve productivity and maintenance. Event-Driven Publishing Use the PL/SQL procedures and functions in the event-driven publishing API to automatically submit jobs to Oracle9iAS Reports Services when an event occurs in the database.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-31

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Practice 19 Overview

• Creating a format trigger to display different paper layouts conditionally • Creating and using a report-level PL/SQL function • Creating an using an external PL/SQL library • Creating a PL/SQL group filter

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 19-32 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 19 .....................................................................................................................................................

Practice Session Overview: Lesson 19 This practice session contains: • Creating a format trigger to conditionally display different layouts • Creating and use a report-level PL/SQL function • Creating and use an external PL/SQL library • Creating a PL/SQL group filter Introduction In this practice session, you create a format trigger to conditionally flag one of two paper layouts for the same data. You also conditionally flag some records in a report by creating and referencing a report-level (local) PL/SQL function. You then move the code into a new PL/SQL library and alter the reference to use the library function instead. If you have time, restrict the number of records processed in a report by creating your own group filter.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-33

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

Practice Session: Lesson 19 1 Create a new report, using p19q1.sql.

Display the records in two different layouts, showing different columns in each. b Create a Group Above layout using the following columns from the query: a

Group G_CUSTOMER_ID G_ORDER_ID

c

Column customer_id order_id order_date order_mode order_total

Create a Tabular layout using the following columns from the query

Column order_id order_date order_total d e

f

Create a user parameter, user_type, that has two possible values: Sales Representative and Sales Clerk. Ensure that only the Group Above layout is displayed when the user type is Sales Representative, and that only the Tabular layout is displayed when the user type is Sales Clerk. Hint: As an alternative to coding format triggers, you can also use conditional formatting. To test the result, display the USER_TYPE parameter in the margin of the report. Make sure that you can enter a parameter value at run time. Save the report as p19q1.rdf.

...................................................................................................................................................... 19-34 Oracle9i Reports Developer: Build Reports

Practice Session: Lesson 19 .....................................................................................................................................................

2 Continue with the previous report.

Modify the report so that the Tabular (Sales Clerk) layout displays an extra column that indicates, with an asterisk, those orders that have an order status of 5. b Remember to change user type to Sales Clerk during testing. c Move the function code into a new PL/SQL library named p19lib.pll d Call the external function from your report instead of calling the report-level function. e Save the report to p19q2.rdf. 3 Continue with the previous report. a Modify the same layout to display additional spacing between every two records. b Save the report as p19q3.rdf and close it. 4 Open report p19q1.rdf. a Write your own group filter to restrict the number of customers displayed, depending on the value of a parameter that you enter at runtime. Hint: You need to create two parameters, to keep track of how many records have been processed, compared to the cutoff number of records required. Make sure that you can enter the cutoff parameter at run time. b Test the report several times by changing the cutoff parameter at run time. c Save the report as p19q4.rdf. a

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 19-35

Lesson 19: Coding PL/SQL Triggers .....................................................................................................................................................

...................................................................................................................................................... 19-36 Oracle9i Reports Developer: Build Reports

20 ................................

Extending Functionality Using the SRW Package

Lesson 20: Extending Functionality Using the SRW Package .....................................................................................................................................................

Objectives

After completing this lesson, you should be able to do the following: • Describe the package contents • • • •

Output messages at run time Execute a drill-down report Create and populate temporary tables Modify visual attributes dynamically

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Topic Timing Lecture 50 minutes Practice 45 minutes Total 95 minutes

...................................................................................................................................................... 20-2 Oracle9i Reports Developer: Build Reports

Overview .....................................................................................................................................................

Overview Introduction This lesson provides examples of the contents and use of the Reports Builder built-in package. The package contains program units that you can reference in your PL/SQL code. Objectives After completing this lesson, you should be able to do the following: • Describe the contents of the Reports Builder built-in package • Output messages at run time • Execute a second report from within another report • Create and populate temporary tables within a report • Modify visual attributes dynamically at run time

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 20-3

Lesson 20: Extending Functionality Using the SRW Package .....................................................................................................................................................

Contents of the SRW Package

SRW.SET_BOOKMARK

SRW.DO_SQL

SRW.SET_FONT_STYLE

SRW.RUN_REPORT

SRW.SET_MAXROW

SRW.TRACE_START

SRW.MESSAGE

SRW.PROGRAM_ABORT

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note This can be a very long and very boring lesson potentially, or it can be interesting and entertaining (format masks). Try to keep explanations of code clear but very concise; outline some procedures and concentrate on a few. Where possible, have prebuilt demonstrations and show most of the code rather than writing it. Mention that all parts of the package must be prefixed by the package name, SRW. (If asked, the acronym SRW stands for SQL Report Writer.) Also point out SRW.PROGRAM_ABORT. You can raise this packaged exception to stop report execution at any stage. The diagram shows only some of the package. Not all the examples above are discussed in this lesson. For further details, investigate the Help system in the same way that you encourage your students.

...................................................................................................................................................... 20-4 Oracle9i Reports Developer: Build Reports

Contents of the Reports Builder Built-in Package .....................................................................................................................................................

Contents of the Reports Builder Built-in Package Introduction The Reports Builder built-in package, known as the SRW package, contains procedures, functions, and exceptions that can help you to do the following: • Control report execution • Output messages at run time • Initialize layout fields • Perform DDL statements (create or drop temporary tables) • Dynamically set format attributes, such as font style and fill patterns Referencing the Contents of the Package You can reference any part of the SRW package in a PL/SQL statement in any PL/SQL area of a report, such as a paper layout format trigger or a report-level trigger. Rules •

You can only call the contents of the SRW package from within Reports Builder. Other tools, such as Forms Builder, do not recognize this package.

•

You must always reference an SRW procedure, function, or exception by preceding it with the package name, SRW ; for example, SRW.MESSAGE or SRW.DO_SQL.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 20-5

Lesson 20: Extending Functionality Using the SRW Package .....................................................................................................................................................

Outputting Messages

• Warning: WHEN THEN SRW.MESSAGE (999, ‘Warning: report continues’);

• Error: WHEN THEN SRW.MESSAGE (999, ‘Warning: report terminated’); RAISE SRW.PROGRAM_ABORT;

• Exceptions: – SRW.INTEGER_ERROR – SRW.NULL_ARGUMENTS

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note If you have not already mentioned it during a previous demonstration, point out that SRW.MESSAGE does not override any Reports default messages. The difference between the warning and the error is that, for the error, you terminate the report by raising SRW.PROGRAM_ABORT. Compare the difference from the MESSAGE built-in in Forms Builder (if students are familiar with Forms Builder): SRW.MESSAGE does not override the default Reports Builder error messages in the way that ON-ERROR and ON-MESSAGE triggers do in Forms Builder. At run time, you see both user-defined and Reports Builder default messages, where applicable.

...................................................................................................................................................... 20-6 Oracle9i Reports Developer: Build Reports

Outputting Messages .....................................................................................................................................................

Outputting Messages Introduction The SRW.MESSAGE packaged procedure displays a message with a code and text string, which you specify. The message is displayed in the following format: MSG-code: text

The message is displayed in a small dialog box. The user must acknowledge the message before continuing. Message Code and Text You can enter a code number from zero up to ten digits. If you enter a number of fewer than five digits, the code is displayed with leading zeros. You can enter a text string to a maximum of 190 characters, excluding the code number. You can embed extra spaces to display your message neatly in the message dialog box. Reports does not suppress extra spaces in the message text. Is It a Warning or an Error? SRW.MESSAGE does not implicitly terminate the report execution. You can choose to issue a warning message that enables the report to continue execution after the user accepts the message. To force the report to terminate after outputting the message, raise the exception SRW.PROGRAM_ABORT. Note: You cannot suppress or replace the default message that Reports Builder gives if you abort a report. Your own error message augments the existing messages. Causes of Exceptions Exception Name SRW.INTEGER_ERROR SRW.NULL_ARGUMENTS

Cause You entered a code that is not a numeric integer. You omitted the code number or text message or both.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 20-7

Lesson 20: Extending Functionality Using the SRW Package .....................................................................................................................................................

Executing a Report KING.LIS

1

KING

2

xxx xxxxxxx xxx xxxxxxx

KOCHHAR KOCHHAR.LIS DE HAAN

yyy yyyyyyy yyy yyyyyyy

DE HAAN.LIS

SRW.RUN_REPORT

zzz zzzzzzz zzz zzzzzzz

Copyright © Oracle Corporation, 2002. All rights reserved.

1 2

Output from master output Output from detailed report

Instructor Note Mention that you can code this nested example on a group filter, which processes each manager record, and can enable you to send each report to a different filename. Move quickly to the next slide, which shows some syntax.

...................................................................................................................................................... 20-8 Oracle9i Reports Developer: Build Reports

Executing a Nested Report .....................................................................................................................................................

Executing a Nested Report Introduction SRW.RUN_REPORT invokes RWRUN with the string that you specify. Use SRW.RUN_REPORT to execute a second report from within a report process. For example: • Output parts of a report to different destinations • Divide a large report into several smaller reports that can be run conditionally Both Reports Use the Same Process SRW.RUN_REPORT starts only one run-time process; it does not start a new process for each report execution. Example Run a report called MANAGERS that retrieves the employee record for each manager. Call the EMPS report (for example, from a group filter). EMPS retrieves the employee records of all employees managed by the current manager only. Sample code to implement this example appears on the following page.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 20-9

Lesson 20: Extending Functionality Using the SRW Package .....................................................................................................................................................

Executing a Report

• Example: SRW.RUN_REPORT ('Report=EMPS DESTYPE=FILE DESNAME='||:LAST_NAME||'.LIS BATCH=YES MGRNO='||TO_CHAR(:MANAGER_ID));

• Exceptions: – SRW.RUN_REPORT_FAILURE – SRW.RUN_REPORT_BATCHNO

• Function: SRW.GETERR_RUN

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Talk through the SRW.RUN_REPORT syntax, which you can code in the group filter. Point out that you restrict the employees to those of the current manager by using a parameter. You pass the manager’s last name as a parameter to supply a unique filename for each report. Mention that you should use TO_CHAR to convert the MANAGER_ID to character type to reference it in the character string argument. However, although PL/SQL often successfully converts mismatched datatypes, you should still advise students to use the TO_CHAR function for efficiency and also to avoid upgrade problems, because Oracle does not guarantee to support this kind of internal functionality in future releases. Note: You cannot run a report on Reports Services when using SRW.RUN_REPORT.

...................................................................................................................................................... 20-10 Oracle9i Reports Developer: Build Reports

Executing a Nested Report .....................................................................................................................................................

Example Function Code FUNCTION MGR_REP RETURN BOOLEAN IS BEGIN SRW.RUN_REPORT ('REPORT=EMPS DESTYPE=FILE DESNAME='||:LAST_NAME||'.LIS BATCH=YES MGRNO='||TO_CHAR(:MANAGER_ID)); RETURN(TRUE); EXCEPTION WHEN SRW.RUN_REPORT_FAILURE THEN SRW.MESSAGE(100, 'Error executing EMPS report'); RAISE SRW.PROGRAM_ABORT; END;

Note: Use LAST_NAME to create the output filename for each manager; use MANAGER_ID to restrict the EMPS query. Causes of Exceptions Reports Builder raises a specific exception that applies to the SRW.RUN_REPORT procedure in two cases: Exception Name SRW.RUN_REPORT_FAILURE SRW.RUN_REPORT_BATCHNO

Causes Detail report does not exist. Detail report failed during execution. BATCH parameter is used inconsistently: master report running with BATCH=YES, called a detail report with BATCH=NO

Displaying Report Failure Message If the detail report fails during execution, you can display information about the cause by using the SRW.GETERR_RUN function. Assign the function to a local PL/SQL character variable, which you display using the SRW.MESSAGE procedure. ... msg char(150); ... EXCEPTION WHEN SRW.RUN_REPORT_FAILURE THEN msg := SRW.GETERR_RUN; SRW.MESSAGE (100, msg); ...

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 20-11

Lesson 20: Extending Functionality Using the SRW Package .....................................................................................................................................................

Restricting Data

Before Report trigger SRW.SET_MAXROW('Q_EMP',3);

Q_EMP EMPLOYEE_ID LAST_NAME 100 101 102

G_EMP

KING KOCHHAR DE HAAN

EMPLOYEE_ID LAST_NAME

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Ask the students where to set the Maximum Rows property to restrict the rows fetched. Answer: In the Property Inspector for a query. SRW.SET_MAXROW provides the same function programmatically. Stress the warning on the opposite page: SRW.SET_MAXROW must be set in the Before Report trigger. If you call it after the Before Report trigger, Reports Builder raises the SRW.MAXROW_UNSET exception. If you handle SRW.MAXROW_INERR, always raise SRW.PROGRAM_ABORT.

...................................................................................................................................................... 20-12 Oracle9i Reports Developer: Build Reports

Restricting Data .....................................................................................................................................................

Restricting Data Introduction Use SRW.SET_MAXROW to restrict the maximum number of records you want to retrieve for a specified query. Reports Builder uses only the retrieved rows of the query in subsequent processing, such as computations and summaries. Suppressing a Query At run time, you can choose not to display any data from a query by setting the maximum number of records to zero. This prevents Reports Builder from retrieving any records from the database. Example Below is a function to display data for the Q_emp query only if a value of one or more is entered in a run-time parameter called HOW_MANY: FUNCTION FETCHIT RETURN BOOLEAN IS BEGIN IF :HOW_MANY >= 1 THEN SRW.SET_MAXROW ( 'Q_emp', :how_many); ELSE SRW.SET_MAXROW('Q_emp', 0); END IF; RETURN(TRUE); EXCEPTION WHEN SRW.MAXROW_UNSET THEN SRW.MESSAGE (100, 'Data was fetched before SRW.MAXROW was called'); RAISE SRW.PROGRAM_ABORT; WHEN SRW.MAXROW_INERR THEN SRW.MESSAGE(200, 'Contact someone clever!' ); RAISE SRW.PROGRAM_ABORT; END;

You must set SRW.SET_MAXROW in the Before Report trigger; that is, after the query has been parsed but before it has been executed. If you call it after the Before Report trigger, Reports Builder raises the SRW.MAXROW_UNSET exception. If you handle SRW.MAXROW_INERR, always raise SRW.PROGRAM_ABORT, because your report has an internal problem and you cannot guarantee the outcome.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 20-13

Lesson 20: Extending Functionality Using the SRW Package .....................................................................................................................................................

Initializing Fields

Paper Layout Page:

Output: logical page no. = 2

F_NEWPAGE

Page: 5

1 FUNCTION F_LOGICAL_PAGEFormatTrigger RETURN BOOLEAN IS my_page number; BEGIN SRW.GET_PAGE_NUM(my_page); SRW.SET_FIELD_NUM(0,my_page+3); RETURN(TRUE); END;

2

Copyright © Oracle Corporation, 2002. All rights reserved.

1 2

Create format trigger on F_NEWPAGE field Set field value to my_page+3

...................................................................................................................................................... 20-14 Oracle9i Reports Developer: Build Reports

Initializing Fields .....................................................................................................................................................

Initializing Fields You can dynamically initialize the value in a layout field by using the relevant SET_FIELD packaged procedure; for example: • SRW.SET_FIELD_CHAR (0, 'a text string') • SRW.SET_FIELD_NUM (0, 1234) • SRW.SET_FIELD_DATE (0, '01-JAN-2002') The first argument is always 0 (zero); you can set the value of the current object. These procedures are relevant only in the format trigger of a field of the correct type. If you use a procedure that conflicts with the field type, it has no effect in the report; Reports Builder ignores the code and does not raise an error. Why Use SRW.SET_FIELD? You might want to retrieve the current logical page number and recalculate the value before displaying it. Example Create a function to display the logical page number plus three in a field called F_NEWPAGE. Because the logical page number variable is available only in the report layout, not in the Data Model, you cannot calculate the value in a data column. Therefore, you use a paper layout field to get the current page number and output a new number by initializing the same field. Make use of a packaged procedure called SRW.GET_PAGE_NUM to retrieve the current logical page number into a local PL/SQL variable, as in the format trigger code opposite.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 20-15

Lesson 20: Extending Functionality Using the SRW Package .....................................................................................................................................................

Performing DDL Statements

• Example: SRW.DO_SQL('CREATE TABLE SRW_LOG (RPT_NAME VARCHAR2(40), REC_NUM NUMBER, MSG_TEXT VARCHAR2(80))' ); SRW.DO_SQL('INSERT INTO SRW_LOG (RPT_NAME, REC_NUM, MSG_TEXT) VALUES (''PAY_REPORT'', TO_CHAR(:EMPLOYEE_ID), :LAST_NAME||''PAY REPORT RUN'') ');

• Exception: SRW.DO_SQL_FAILURE

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Stress that you can usually code DML statements directly as a PL/SQL statement in your trigger. It is inefficient to use SRW.DO_SQL for DML statements unless it is necessary, as in the example above where the table does not exist until run time. In this case, if you code the INSERT statement directly as PL/SQL, you cause a validation error: “Table or view does not exist.” Mention that if you want to create a temporary table and use it in a query in the same report, you must create the table before the Before Report trigger. Note that DML statements are not valid in layout format triggers.

...................................................................................................................................................... 20-16 Oracle9i Reports Developer: Build Reports

Performing DDL Statements .....................................................................................................................................................

Performing DDL Statements Introduction The SRW.DO_SQL packaged procedure executes any specified SQL statement from within Reports Builder. Why Use SRW.DO_SQL? Use SRW.DO_SQL to perform DDL statements dynamically during run time. For example, create a temporary table and insert rows into it during execution. You can also perform DML statements using this procedure, but DML statements run faster if you code them directly within a PL/SQL block. Code DML statements in the SRW.DO_SQL procedure only if necessary. For example: • To insert records into a permanent table in the database, code DML in a PL/SQL block. • To insert records into a temporary table that does not exist until run time, code DML in SRW.DO_SQL. Querying from Temporary Tables If you want to create a temporary table and reference it in a query in the same report, the table must exist before the Before Report trigger fires. That is when Reports Builder parses its queries. Therefore, you must create this table in the Before Parameter Form or After Parameter Form report trigger.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 20-17

Lesson 20: Extending Functionality Using the SRW Package .....................................................................................................................................................

Setting Format Attributes

Tuesday, January 2002

Borderwidth Fill pattern Format mask

Printer tray control

Car Policy

Define a bookmark

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 20-18 Oracle9i Reports Developer: Build Reports

Setting Format Attributes .....................................................................................................................................................

Setting Format Attributes Introduction The SRW.SET_ packaged procedures apply specified format attributes to the current layout object: frame, repeating frame, field, or boilerplate object. Why Use a Format Attribute? Some of the reasons that you would want to use format attributes are: • To change the visual appearance of layout objects conditionally, depending on values in a repeating frame, or on run-time parameter values • To switch to different printer trays as your report formats • To place a logo or standard links in an HTML document • To define a bookmark in an HTML or PDF document Coding a Format Attribute You can code the packaged procedure in a function as a local or report-level program unit, or in a PL/SQL library. This is useful if you want a common format attribute in more than one object or more than one report. You can then call the function whenever required from the format trigger of a specific object. As an alternative to a PL/SQL library, in the Object Navigator, you can drag and drop report-level program units between different reports. However, note that this creates a copy of the program unit in each report, rather than a reference to one piece of code.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 20-19

Lesson 20: Extending Functionality Using the SRW Package .....................................................................................................................................................

Setting Format Attributes

• Visual attributes: SRW.SET_FILL_PATTERN('solid'); SRW.SET_BORDER_WIDTH(250); SRW.SET_FORMAT_MASK('Day, Month yyyy ' ); • Printer tray control: SRW.SET_PRINTER_TRAY('letterhead'); • Defining a bookmark: SRW.SET_BOOKMARK('Car Policy');

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note The slide shows only a few examples. There is a complete code example on the next page. However, stress that many more examples are documented in the online Help system. Some attributes are ignored for character mode. Demonstration: l20mask.rdf. This is a tabular report. Show the mask that is coded at report level, program unit: SAL_MASK. Point out the different attributes and explain their purposes. Show the format trigger on F_SALARY that calls the mask in a conditional piece of code depending on the value of SALARY. Stress that you must include the statement return(true); at the end of the function. Run the paper layout.

...................................................................................................................................................... 20-20 Oracle9i Reports Developer: Build Reports

Setting Format Attributes .....................................................................................................................................................

Example Conditionally set the following attributes if salary is greater than $12000: Attribute Background fill color Fill pattern Font style Font face Font weight Text color

Value Blue Sandpaper Italic Helvetica Extra bold White

FUNCTION SAL_MASK RETURN BOOLEAN IS BEGIN IF :SALARY > 12000 THEN SRW.SET_BACKGROUND_FILL_COLOR(‘blue'); SRW.SET_FILL_PATTERN(‘sandpaper'); SRW.SET_FONT_STYLE(SRW.ITALIC_STYLE); SRW.SET_FONT_FACE(‘helvetica'); SRW.SET_FONT_WEIGHT(SRW.EXTRABOLD_WEIGHT); SRW.SET_TEXT_COLOR(‘white'); END IF; RETURN(TRUE); END;

Remember that you can use the Conditional Formatting dialog box to specify output formatting attributes (font and/or color) for a selected layout object based on conditions that exist. However, if you need to apply other attributes such as fill color, for example, then you need to use the SRW.SET_ procedures.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 20-21

Lesson 20: Extending Functionality Using the SRW Package .....................................................................................................................................................

Setting Format Attributes in a Web Layout … F_SALARY … FUNCTION mystyles RETURN BOOLEAN IS BEGIN SRW.SET_JUSTIFICATION(SRW.RIGHT_HJUST); SRW.SET_TEXT_COLOR('Red'); SRW.SET_FONT_WEIGHT(SRW.BOLD_WEIGHT); SRW.SET_FONT_STYLE(SRW.ITALIC_STYLE); return (TRUE); END;

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note The H in SRW.RIGHT_HJUST is for horizontal justification.

...................................................................................................................................................... 20-22 Oracle9i Reports Developer: Build Reports

Setting Format Attributes .....................................................................................................................................................

Using Format Attributes in a Web Layout Recall that the formatTrigger attribute of the rw:field tag is used to modify the display characteristics of the field. You can only use specific SRW packaged procedures to set the field’s attributes. These are: • SET_FORMAT_MASK • SET_HYPERLINK • SET_LINKAGE • SET_TEXT_COLOR • SET_JUSTIFICATION • SET_FONT_FACE • SET_FONT_STYLE • SET_FONT_SIZE • SET_FONT_WEIGHT • SET_DISPLAY_NAME • SET_FIELD_CHAR • SET_FIELD_DATE • SET_FIELD_NUM Example In the example on the opposite page, the formatTrigger attribute contains the name of the function mystyles. When executed, mystyles will set the following attributes on the salary field: Attribute Justification Text color Font weight Font style

Value Right Red Bold Italic

Using Format Attributes in Character Mode Reports Some attributes are applicable to character environments only; some are for bitmapped environments only. If you include a bitmapped attribute and then run the report in character mode, or the reverse, Reports Builder ignores the code and does not cause an error. This enables you to develop a report for use in a different environment.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 20-23

Lesson 20: Extending Functionality Using the SRW Package .....................................................................................................................................................

Summary

In this lesson, you should have learned how to: • Describe the SRW package contents: – Procedures – Functions – Exceptions

• Use SRW.PROGRAM_ABORT to stop report processing • Use the SRW package to: – Execute a detail report – Dynamically execute DDL statements – Provide conditional layout formatting and display

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 20-24 Oracle9i Reports Developer: Build Reports

Summary .....................................................................................................................................................

Summary •

•

The SRW package contains procedures, functions, and exceptions. Remember that you can raise the exception SRW.PROGRAM_ABORT in your PL/SQL code to stop report processing at any time. The SRW package provides a number of features, including: – Executing a second report from within a report process, using SRW.RUN_REPORT – Support for DDL statements, using SRW.DO_SQL, to enable you to create or drop temporary tables from PL/SQL code in a report – Conditional formatting and display using SRW.SET_

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 20-25

Lesson 20: Extending Functionality Using the SRW Package .....................................................................................................................................................

Practice 20 Overview

• Building a report containing conditional highlighting • Modifying a Web report with conditional highlighting • Executing a detail report from a master report • Writing to a temporary table from a report

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 20-26 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 20 .....................................................................................................................................................

Practice Session Overview: Lesson 20 This practice session contains: • Building a report containing conditional highlighting • Modifying a Web report with conditional highlighting • Executing a detail report from a master report • Writing to a temporary table from a report Introduction In this practice session, you highlight values based on conditional PL/SQL code. You build a master report that enables you to call a detail report and generate a separate PDF file for each detail report. If you have time, you add report-level triggers to an existing report to write messages to a temporary table at various stages of report execution. • Write PL/SQL functions as local PL/SQL (report-level), and reference the code in a condition within the relevant object trigger in the report.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 20-27

Lesson 20: Extending Functionality Using the SRW Package .....................................................................................................................................................

Practice Session: Lesson 20 1 Open report p9q3.jsp. a

Display the column CUST_TOTAL in the customer group as red, bold italics if its value is 10,000 or more. Hint: Check the Help system for the SRW.SET or use conditional formatting.

Mask Attribute SRW.SET_TEXT_COLOR SRW.SET_FONT_WEIGHT SRW.SET_FONT_STYLE

Suggested Value Red SRW.BOLD_WEIGHT SRW.ITALIC_STYLE

You can copy the code from the file p20q1a.txt. b Alter the pattern and color of those records where the customer total is 5,000 or less. Hint: Check the Help system for the SRW.SET or use conditional formatting. Attribute

Suggested Values

Fill pattern Foreground fill color Background fill color

Crisscross, sandpaper Yellow Green

You can import the file p20q1b.txt. c Write the necessary format triggers. d Save the report as p20q1.jsp. e Run the paper layout. 2 Continue with the same report. a Run the Web layout for the report. Is there formatting on the CUST_TOTAL column? b Modify the Web source so that the column CUST_TOTAL in the customer group as displays as red, bold italics if its value is 10,000 or more. c Save the report as p20q2.jsp. d Run the Web layout to test.

...................................................................................................................................................... 20-28 Oracle9i Reports Developer: Build Reports

Practice Session: Lesson 20 .....................................................................................................................................................

3 Execute a detail report from a master report. a

Create a simple tabular report called MGR_MASTER. The report should query last names and employee IDs for those employees who are managers. Display all fields. You can use the query: SELECT distinct manager.last_name Manager, manager.employee_id FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id

Save the report as mgr_master.rdf. c Create another tabular report, the detail report, and name it EMP_RPT. The report should return department number, employee name, and salary for a specific manager. The manager_id will be passed to EMP_RPT by MGR_MASTER. d Save the report as emp_rpt.rdf and close it. e Write the code to execute the detail report. For each manager returned, MGR_MASTER should invoke the detail report, EMP_RPT, pass it the manager’s employee_id, and produce a report in PDF format containing information about the employees working for that manager. Use the manager’s employee_id to create an output filename for each manager: MGR_.PDF. Hint: You can write the code in the group filter. f Compile the PL/SQL code and close the window. g Run the paper layout for MGR_MASTER to test. Access the file system and locate the output for the detail reports. View two or three of the output files. h Save mgr_master.rdf and close it. b

If You Have Time 4 Open report p10q1.rdf In this report, you use report triggers to create a temporary table at the start of report execution, and you insert rows into the table at various stages of the execution. a Write a trigger that creates a temporary table to hold the date and userid each time the report is run. The table should contain three columns: one date column, one character column of width 10, and one character column of width 80. CREATE TABLE RUNREPORT (DATE_RUN DATE, USER_RUN VARCHAR2(10), COMMENTS VARCHAR2(80))

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 20-29

Lesson 20: Extending Functionality Using the SRW Package .....................................................................................................................................................

b

The same trigger should also insert the current date, userid, and “Starting Report” into this table. INSERT INTO RUNREPORT (DATE_RUN, USER_RUN, COMMENTS) VALUES (SYSDATE, USER, ' Starting Report')

Hint: Think about how you code the single quotes. c Compile the PL/SQL code and close the window. d Save the report as p20q4.jsp. e Run the paper layout. f Using SQL*Plus, verify that your table exists and was populated successfully. g Log on to SQL*Plus (in Microsoft Windows, select the SQL*Plus icon in the Oracle group) using the same userid as you used for Reports. h Display all records in the RUNREPORT table. SQL> col comments format a40 SQL> SELECT * FROM RUNREPORT;

i

j k l

Try running the report more than once. When you run the report a second time, the table already exists and Reports Builder raises an exception. Make the necessary change to your code so that the report runs even when the table exists Write a trigger that inserts a record with the comment “Printing another page” whenever it begins a new page. Write a trigger that inserts a record with the comment “Report completed” when the report finishes. Save the report as s20q4l.jsp. Generate the output to a PDF file. Verify the results in SQL*Plus.

...................................................................................................................................................... 20-30 Oracle9i Reports Developer: Build Reports

21 ................................

Maximizing Performance Using Oracle9iAS Reports Services

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

Objectives

After completing this lesson, you should be able to do the following: • Describe the Oracle9iAS Reports Services architecture • Distinguish between the JSP and Servlet run-time architectures • Describe the various ways to run a report • List the options for managing and monitoring Oracle9iAS Reports Services

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Topic Timing Lecture 30 minutes Practice 15 minutes Total 45 minutes

...................................................................................................................................................... 21-2 Oracle9i Reports Developer: Build Reports

Overview .....................................................................................................................................................

Overview Introduction When you are ready to publish your reports, all the Web server and application server tools you need are available in the Oracle9i Application Server (Oracle9iAS). In this lesson, you will learn about the architecture of relevant Oracle9iAS components in combination with its reports publishing component, Oracle9iAS Reports Services. You learn how to make report requests, and about the options available for managing and monitoring Reports Services. Objectives After completing this lesson, you should be able to do the following: • Describe the Oracle9iAS Reports Services architecture • Distinguish between the JSP runtime architecture and the servlet runtime architecture • Define the different methods of invoking a report • List the options for managing and monitoring Reports Services

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-3

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

Running Reports Using Oracle9iAS Reports Services • • • •

Provides the deployment platform Formats, prints reports on a powerful server Eliminates rerunning reports Enhances scalability and performance

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 21-4 Oracle9i Reports Developer: Build Reports

Why Use Oracle9iAS Reports Services? .....................................................................................................................................................

Why Use Oracle9iAS Reports Services? Introduction Oracle9iAS Reports Services is the reports publishing component of Oracle9iAS. It is an enterprise reporting service for producing high quality production reports that dynamically retrieve, format, and distribute any data, in any format, anywhere. You can use Oracle9iAS Reports Services to publish in both Web-based and non-Webbased environments. Benefits of Oracle9iAS Reports Services Oracle9iAS Reports Services provides a flexible architecture for the distribution and automated management of report generation engines on the same server and across multiple servers. • When used in conjunction with JSP, servlet, or CGI, Oracle9iAS Reports Services enables you to run reports on any platform from a Web browser using a standard URL syntax. The JSP engine runs the Web layout. The Reports servlet runs the paper layout. Reports CGI is maintained only for backward compatibility. • Reports are formatted on a high-performance NT or UNIX server. • Reports Services caches the output in a central repository on the server machine. With Oracle9iAS Reports Services, the Reports cache is persistent. This means that if the server is shut down and then restarted, all jobs are recovered, not just scheduled jobs. • Reports Services detects duplicate reports. Users can share output without rerunning the report. • You can implement multiple run-time engines for one Reports Services machine. • You can implement multiple Reports Services server instances on one machine. • Oracle9iAS Reports Services provides peer-level clustering to allow sharing of the application server load among multiple machines for unlimited scalability.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-5

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

Report Request Methods

• • • •

RWCLIENT command line URL syntax Oracle9iAS Portal SRW.RUN_REPORT

• Event-Driven Publishing API

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 21-6 Oracle9i Reports Developer: Build Reports

Why Use Oracle9iAS Reports Services? .....................................................................................................................................................

Report Request Methods You can run a report using various request methods. Below is a brief overview of each method. • The RWCLIENT command This enables you to run a report from the command line prompt in a non-Web environment. RWCLIENT is an executable that parses and transfers the command line to the specified Oracle9iAS Reports server. It can use command line arguments similar to those used with the Oracle9iAS Reports Runtime executable file, RWRUN • URL syntax To run a report from a Web browser, use the URL syntax. The Oracle9iAS Reports Servlet (and CGI, for backward compatibility) converts the URL syntax into an rwclient command line request that is then processed by Oracle9iAS Reports Services. You can give your users the URL syntax needed to make the report request from their browser, or you can add the URL syntax to a Web site as a hyperlink. • Oracle9iAS Portal Oracle9iAS Portal enables you to add a link to a report on a portal page or portlet. Authorized users accessing the Oracle Portal site simply click the link to run the report. You can also publish your report output directly in a portlet. Using Oracle9iAS Portal makes it more convenient and secure to publish a report on the Web. • SRW.RUN_REPORT This is a packaged PL/SQL procedure that submits a report to Reports Services. In the argument list, you specify the JSP or servlet information in addition to the Reports parameters, and RUN_REPORT sends the request to Reports Services. Set the SERVER argument to the Oracle9iAS Reports Services server name to cause the SRW.RUN_REPORT command to behave as though you executed the RWCLIENT command. • Event-Driven Publishing API The Event-Driven Publishing API is a PL/SQL package that allows for the automatic submission of jobs to Oracle9iAS Reports Services from within the database. The API provides the basic functions required for the development of procedures that respond to events in the database. With the event-driven publishing, you can automatically run a report in response to a database event, such as the insertion of a record or the change of a value. Event-driven jobs are submitted using the HTTP protocol. The server assigns a unique job identifier to every call, useful for tracking the status of the job. The Event-Driven Publishing API is a PL/SQL API

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-7

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

Oracle9iAS Components

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Note that when referring to a multi-tier architecture, the term tier refers to the logical location of the components that comprise the Oracle9iAS Reports Services architecture. Each of the tiers, though, could reside on the same or different machines.

...................................................................................................................................................... 21-8 Oracle9i Reports Developer: Build Reports

Oracle9iAS Reports Services Architecture .....................................................................................................................................................

Oracle9iAS Reports Services Architecture Introduction Oracle9iAS Reports Services is architected to publish reports in both Web-based and non Web-based environments. Reports Services can be configured in a number of ways depending on your requirements. In this section, you will learn about the Oracle9iAS Reports Services runtime architecture and the various commands used to invoke a report that is deployed on the Oracle9i Application Server. First, you should be familiar with the key components of Oracle9iAS: • The Oracle9iAS HTTP Server is a Web server. It incorporates an OpenSSL module to provide support for Secure Sockets Layer (SSL) and HTTP Secure Sockets Layer (HTTPS). It also provides a servlet engine to support the running of Java Servlet applications. It is the HTTP Listener. • The module mod_OC4J is used by the HTTP Listener to redirect requests for servlets and JSPs to Oracle9iAS Containers for Java 2 Enterprise Edition (OC4J). OC4J provides a fast, lightweight, highly scalable, easy-to-use complete J2EE environment that includes a JSP translator, a JSP servlet engine, and an Enterprise JavaBeans (EJB) container. • The Reports Servlet and JSP are components of Oracle9iAS Reports Services that run inside the Web server’s OC4J. The Reports Servlet translates and delivers information between HTTP and the Reports Server. • The Reports Server processes client requests. It spawns runtime engines, fetches completed reports from the Reports cache, and notifies the client that the report is ready. • The Reports Engine includes components for running SQL- and PDS-based reports. It fetches requested data from the data source, formats the reports, sends the output to cache, and notifies the Reports Server that the job is complete. • The Reports Cache stores completed jobs. Web and Non-Web Architectures When used in a Web environment, the Oracle9iAS Reports Services architecture consists of four tiers: • The client tier (a Web browser) • The Web server tier • The Oracle9iAS Reports Services tier • The data tier, including databases and pluggable data sources

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-9

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

Enabling Single Sign-On Access Oracle Internet Directory

• Log in only once • Access application level authentication • Access data level authentication

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 21-10 Oracle9i Reports Developer: Build Reports

Oracle9iAS Reports Services Architecture .....................................................................................................................................................

Web and Non-Web Architectures (continued) When used in a non-Web environment, there are three tiers (a Web server being unnecessary): • The client tier • The Oracle9iAS Reports Services tier • The data tier Enabling Single Sign-On Access When deploying reports, you need to ensure that your user has the necessary privileges to run that particular report, at that particular time, on that particular server. Likewise, now that pluggable data sources are part of the benefits offered to you through Oracle9i Reports, you need to ensure that your user has the privileges to access the required data sources. You may want to spare your user the trouble of having to log in to multiple data sources in order to run one job. You can address application level authentication and data level authentication through single sign-on (SSO). SSO enables you to establish unique identities for each user which are tied to resources unique to that user. The user’s resources contain key-identified connection strings for accessing different data sources. The user is uniquely identified through a once-per-session login, and the login references the user’s resources to ensure that he or she has access to the appropriate data sources, without users having to enter this information themselves. SSO is made possible through the partnership of Oracle9iAS Reports Services, Oracle Internet Directory (OID), and the Oracle Login Server, all delivered through the Oracle9i Application Server. Technical Note For more information on data source single sign-on, see the Oracle9iAS Reports Services, Publishing Reports to the Web manual. For information about the requirements and procedures required for setting up SSOrelated components, such as the Oracle Internet Directory, see the Oracle Internet Directory Administrator's Guide and the Oracle HTTP Server Administrator's Guide on the Oracle9iAS documentation CD and on the Oracle Technology Network (http:// otn.oracle.com).

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-11

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

JSP Run-time Architecture for Running the Web Layout

HTTP

rept.jsp

Oracle HTTP Listener mod_OC4J JSP Engine CORBA/IIOP

OID

Oracle9iAS Reports Services

CORBA/IIOP Reports Engine PDS

Copyright © Oracle Corporation, 2002. All rights reserved.

Servlet Run-time Architecture for Running the Paper Layout HTTP Oracle HTTP Listener mod_OC4J Reports Servlet CORBA/IIOP

OID

Oracle9iAS Reports Services

Fax

nt Pri

ma il

CORBA/IIOP rept.rdf rept.jsp

Reports Engine PDS

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 21-12 Oracle9i Reports Developer: Build Reports

Oracle9iAS Reports Services Runtime Architecture .....................................................................................................................................................

Oracle9iAS Reports Services Runtime Architecture Running the Web Layout You use the JSP runtime architecture to run the Web layout. The JSP engine runs the Reports JSP because in a Reports JSP, you can include not only Reports custom JSP tags, but tags from other libraries as well. Hence, the Reports JSP cannot be run within Oracle9i Reports because it may contain tags that are not Reports custom tags. The JSP engine executes the JSP, and the Reports custom JSP tags invoke the Reports server to ensure that the user is authenticated to run the report. Once authentication has been established, the Reports server spawns a Reports engine and sends the request to that engine. The Reports engine retrieves the data from the specified data source(s) and passes it to the JSP engine. The JSP engine formats the data and returns it to the user. The communication between the JSP engine, the Oracle9iAS Reports server, and the Reports engine is done through industry standards, Common Object Request Broker Architecture (CORBA) and Internet Inter-ORB Protocol (IIOP). Running the Paper Layout You use the Servlet runtime architecture to run the paper layout. When a request is received for the Reports servlet, the servlet contacts the Reports server to ensure the user is authenticated to run the report. Once authentication has been established, the Reports server spawns a Reports engine and sends the request to that engine to run the report. The Reports engine executes the RDF by: • Retrieving and formatting the data • Populating the Reports cache • Notifying the Reports server that the report is ready. The Reports server accesses cache and send the report to the specified output destination. If the report is a Reports JSP that contains a paper layout, the Reports engine interprets the paper layout, coded in XML, and executes the report as stated above.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-13

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

Running a Web Report

URL syntax: http://vesuvius.world.com:80/reports/rwservlet? report=emp_dept.rdf&desformat=html&destype=cache& server=repsrv1&dept_param=10

Key map file: cgicmd.dat

Copyright © Oracle Corporation, 2002. All rights reserved.

Technical Note For information on testing reports deployment without installing Oracle9iAS, refer to the Oracle9i Reports Technical Note How to Execute Reports Services from 9iDS on the Oracle Technology Network (http://otn.oracle.com).

...................................................................................................................................................... 21-14 Oracle9i Reports Developer: Build Reports

Making a Request to Run a Report .....................................................................................................................................................

Making a Request to Run a Report Introduction Oracle9iAS Reports Services supports requests both from Web and non-Web clients. This section covers how to run a report in both environments. Running a Web Report Thanks to the Reports thin client Web interfaces (the Reports Servlet or JSP), running a report on the Web is simply a matter of entering a URL, just as you would fetch almost any document on the Web. The URL specifies all the required information to run the report, such as: what report to run, what format to produce the output in, whether to schedule the report, and so on. Consider the following example: http://vesuvius.world.com:80/reports/rwservlet? report=emp_dept.rdf&desformat=html&destype=cache& server=repsrv1&dept_param=10

In the example, the first line contains the Web server hostname (vesuvius), the domain name (world), the port (80), and the alias (reports), a virtual path that stands in for the absolute path to the files the URL will access. RWSERVLET invokes the Oracle9iAS Reports Services servlet. The question mark (?) identifies the beginning of the command line arguments such as the module name, destination format, destination type, Reports server name, and a value for a runtime parameter. The Key Map File As you can see from the example above, the URL can become quite long. To get around this problem, the key map file cgicmd.dat can be used. The key map file contains command strings for running reports, each identified by a unique keyword. By using key mapping, the command line arguments are all hidden from the user. By default, the key map file is located in the /reports/conf directory. For the example above the mapfile entry may look like the following: emp_rep1: report=emp_dept desformat=html destype=cache server=repsrv1 dept_param=%1

Note that the delimiter is the map file entry is a space. The URL now becomes much shorter: http://vesuvius.world.com:80/reports/rwservlet?emp_rep1&10

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-15

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

Parameters for RWCLIENT

• SERVER = • MODULE|REPORT = • DESTYPE = FILE, PRINTER, MAIL, CACHE, LOCALFILE, SYSOUT,ORACLEPORTAL • DESNAME = • DESFORMAT = PDF, HTML, HTMLCSS, RTF, DELIMITED, XML RWCLIENT SERVER=server1 MODULE=my_rep.rdf DESTYPE=cache DESFORMAT=html

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 21-16 Oracle9i Reports Developer: Build Reports

Making a Request to Run a Report .....................................................................................................................................................

Running a Non-Web Report from the Command Line Example 1 Invoke the command line. In Windows NT, Select Start > Run. 2 Enter the command string, for example: RWCLIENT.EXE SERVER=server1 REPORT=my_rep.rdf DESTYPE=FILE DESFORMAT=PDF DESNAME=my_rep_output.pdf

This command references an executable file that parses and transfers the command line to the specified Oracle9iAS Reports server. The report is run immediately. Returning Control to the Client Include BACKGROUND=Yes in your RWCLIENT command line, the report runs asynchronously, and then RWCLIENT returns immediately after submitting a job request to Reports Services. Technical Note For a complete list of command line arguments, refer to Oracle9iAS Reports Services, Publishing Reports to the Web documentation.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-17

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

The Schedule Parameter

SCHEDULE = [FREQ from] TIME [retry n after LEN] RWCLIENT... SCHEDULE=monthly_last_weekday_from_17:00_Jan_22,_2002_ retry_3_after_1_hour

FREQ

CLOCK

DATE

TIME

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 21-18 Oracle9i Reports Developer: Build Reports

Making a Request to Run a Report .....................................................................................................................................................

The Schedule Parameter SCHEDULE is a scheduling parameter that you can include in the RWCLIENT command line to control the time and frequency with which your report runs. The syntax of the SCHEDULE parameter comes primarily from the syntax of a "cron" job on UNIX. To avoid quoting the schedule string, use underscores (_) instead of spaces. In default mode, the program runs the report immediately. Syntax The full syntax of the SCHEDULE parameter is:, [SCHEDULE=] string, where the string is, [FREQ from] TIME [retry {n} + after LEN] FREQ = hourly|daily|weekly|monthly|{every LEN|DAYREPEAT}|{last {WEEKDAYS| weekday | weekend} before {n}+} TIME = CLOCK [DATE] [INCR] CLOCK = DATE= INCR = {+LEN} | {next SUFFIX} LEN = {n} * SUFFIX SUFFIX =

Example SCHEDULE=hourly_from_09:00am_Mar_01,_2002 SCHEDULE=last_weekday_from_17:00_Oct_23,_2001_retry_3_after_ 1_hour

Note: This syntax is necessary only if you want to control your report jobs from the command line. You can also specify the scheduled time and frequency of a report when you define a new job in the easy-to-use Queue Manager screen, which you learn about later in this lesson.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-19

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

Reports Caching

Cached reports on server: • Tolerance = n (minutes) • Tolerance = 0 (default/current behavior) • Maximum tolerance is 232 minutes (approximately 10,000 years) • Cached output controlled by a CACHEDIR server parameter

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 21-20 Oracle9i Reports Developer: Build Reports

Making a Request to Run a Report .....................................................................................................................................................

Reports Caching Include the TOLERANCE parameter in your command line to set the maximum acceptable time (in minutes) for reusing a report's cached output when a duplicate job is detected. Setting the time tolerance on a report reduces the processing time when duplicate jobs are found. Setting TOLERANCE=60 means that Reports Services can detect a duplicate report request from the same userid and return the existing cached output to the client without rerunning the report if the existing report output is 60 minutes old or less. Setting TOLERANCE=0 forces the report to rerun for each request. This is the default behavior. Cached Output Reports Services uses a parameter CACHEDIR to determine where to place the cached output. The default directory is \reports\cache. Technical Note For more information on Oracle9iAS Reports Services, see the eClass Oracle9iAS Reports Services: Deploy Internet Reports. Also refer to Oracle9iAS Reports Services, Publishing Reports to the Web documentation.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-21

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

Using Oracle Enterprise Manager

Copyright © Oracle Corporation, 2002. All rights reserved.

Oracle Enterprise Manager

• Start, stop, and restart a Reports server • View and manage: – Current Jobs queue – Scheduled Jobs queue – Finished Jobs queue

• Monitor server performance • Edit server configuration

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 21-22 Oracle9i Reports Developer: Build Reports

Managing and Monitoring Oracle9iAS Reports Services .....................................................................................................................................................

Managing and Monitoring Oracle9iAS Reports Services Introduction This section introduces Oracle Enterprise Manager as a means for monitoring Oracle9iAS Reports Services. It also explains the features of the Queue Manager and how to use it to view and schedule reports in an easy-to-use GUI. Using Oracle Enterprise Manager Oracle9iAS Reports Services operates under the centralized management provided for Oracle9iAS by Oracle Enterprise Manager (OEM). Oracle Enterprise Manager is Oracle's single, integrated solution for administering and monitoring global e-business enterprises. Oracle9iAS Reports Services can be managed through the new browserbased user interface for Oracle Enterprise Manager. Using OEM, you can: • Start and stop your Oracle9iAS Reports server • Monitor response metrics • Edit server configuration • View server trace information • View the server log file • View the various job queues Because Oracle9iAS Reports Services is integrated with Oracle Enterprise Manager, you can administer it right along with all the other Oracle9i Application Server components.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-23

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

What Is the Queue Manager?

Real-time Reports Server status: • View report status and scheduled jobs • Refresh to see the up-to-date status • Reprioritize or cancel jobs

• Administrator password encrypted in .ora file

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 21-24 Oracle9i Reports Developer: Build Reports

Managing and Monitoring Oracle9iAS Reports Services .....................................................................................................................................................

What Is the Queue Manager? The Queue Manager is a facility that enables users and administrators to view the current status of each report. You can see all the jobs controlled by the master, as well as the server on which each job is running, and the time that each report started and finished, and whether it terminated successfully or with an error. You can also view jobs that are scheduled to run at a future date or time. You can reprioritize or cancel your own scheduled jobs. Administrators can reprioritize or cancel any scheduled jobs. Administrator Privilege In the .ora file, the first line gives an encrypted password, IDENTIFIER=. You can set this password the first time that you start the Reports Services. At the login prompt, select any username and password to assign as Administrator.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-25

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

Queue Manager Past jobs Current jobs Scheduled jobs

Copyright © Oracle Corporation, 2002. All rights reserved.

Queue Manager All my jobs All jobs Refresh

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note To delete a queued job, there is a menu option, Job > Remove.

...................................................................................................................................................... 21-26 Oracle9i Reports Developer: Build Reports

Managing and Monitoring Oracle9iAS Reports Services .....................................................................................................................................................

Viewing Report Jobs The Queue Manager status bar enables you to switch among different views of report jobs. Icon Name Show Past Jobs Show Current Jobs Show Scheduled Jobs

Description Displays jobs that have already run for a given reports server Displays jobs that are currently running Displays scheduled jobs in order to reprioritize or cancel a job

Viewing Jobs of Different Owners The Queue Manager status bar enables you to restrict the view of jobs by owner. Icon Name Show All My Jobs Show All Jobs

Description Displays your reports for a given reports server Displays all reports for a given reports server

Displaying Up-to-Date Job Status Click Refresh on the status bar to refresh the screen and display the up-to-date status. The default update interval is 15 seconds. You can alter this interval in the Queue Manager preferences window. Select Options > Preferences > Polling Interval.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-27

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

Using the Queue Manager RSVR1 Queue Manager RSVR2

• Connect to any number of servers • Administrator can shut down server • Reports Services restores scheduled jobs after shutdown

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 21-28 Oracle9i Reports Developer: Build Reports

Managing and Monitoring Oracle9iAS Reports Services .....................................................................................................................................................

Connecting to Reports Services You can connect to Reports Services by changing the server name in the Queue Manager status bar. Shutting Down Reports Services You can shut down Reports Services from the Queue Manager menu if you have Administrator privileges. Select Queue > Shutdown This option remains grayed out if you are not logged on as Administrator. Retaining Scheduled Jobs After Shutdown Reports Services maintains a list of all scheduled jobs in a file, / reports/server/servername.dat. If Reports Services shuts down and restarts for any reason, it reads this file at startup and restores all scheduled jobs.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-29

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

Summary

In this lesson, you should have learned how to: • Describe the JSP and servlet run-time architectures • List several ways to run a report • Describe the features of Oracle Enterprise Manager and Queue Manager for monitoring Reports Services

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 21-30 Oracle9i Reports Developer: Build Reports

Summary .....................................................................................................................................................

Summary •

•

•

• •

Oracle9iAS Reports Services provides a flexible architecture for the distribution and automated management of report generation engines on the same server and across multiple servers, in both Web-based and non Web-based environments. Run a report using several methods: – RWCLIENT – URL syntax – Oracle9iAS Portal – SRW.RUN_REPORT – Event-Driven Publishing API Oracle9iAS Reports Services Runtime Architecture – Run Web layouts using the JSP runtime architecture. – Run paper layouts using the Servlet runtime architecture. Use Oracle Enterprise Manager to monitor Oracle9iAS Reports Services. Use Queue Manager to view and schedule reports.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-31

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

Practice 21 Overview

• Constructing a URL to run a report on the Web • Creating an entry in the key map file

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 21-32 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 21 .....................................................................................................................................................

Practice Session Overview: Lesson 21 This practice session contains: • Constructing a URL to run a report on the Web • Creating an entry in the key map file Introduction You publish your reports using Oracle9iAS Reports Services in the Oracle9i Application Server. This practice session consists of paper-based questions to test your understanding of Oracle9iAS Reports Services. Instructor Note Because Oracle9iAS is not part of the classroom setup environment, students will not be able to actually deploy reports on the Web. However, encourage students to work with the scenario in the practice and become familiar with the structure of the URL for deploying reports on the Web, as well as creating entries in the key map file to simplify the command line arguments.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 21-33

Lesson 21: Maximizing Performance Using Oracle9iAS Reports Services .....................................................................................................................................................

Practice Session: Lesson 21 1 You created a report called SalesByDept.jsp and need to deploy it on the

Web using a URL. Component Web server domain name port alias for the directory path

2

3 4 5

Description neptune world 8888 salesdata

The report should be executed by repserver4, and the output should be in HTML format and go directly to Oracle9iAS Reports cache. Access the data by connecting to the Sales database using a username of scott, a password of tiger, and salesdb as the database connect string. Construct the URL to run the report using the Reports servlet. So that you do not have to enter such a long URL each time you run the SalesByDept report, create a key entry for the key map file. a Name the key sales. You can also restrict the data in the report by passing it a value for the runtime parameter p_deptno. Be sure to include the parameter in the map file entry. b Reconstruct the URL to run the report using the key. Pass in a value of 70 for p_deptno. In addition to using a URL, what are the other methods for requesting a report? What is the JSP runtime architecture used for? Which two facilities can be used to monitor Oracle9iAS Reports Services?

...................................................................................................................................................... 21-34 Oracle9i Reports Developer: Build Reports

22 ................................

Building Reports: Efficiency Guidelines

Lesson 22: Building Reports: Efficiency Guidelines .....................................................................................................................................................

Objectives

After completing this lesson, you should be able to do the following: • Describe performance areas • Use performance measuring tools • Build reports to run in other languages

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Topic Timing Lecture 40 minutes Practice 15 minutes Total 55 minutes

...................................................................................................................................................... 22-2 Oracle9i Reports Developer: Build Reports

Overview .....................................................................................................................................................

Overview Introduction A report does not run in a vacuum. It is dependent on the underlying hardware, software, and network connection. Because of this complex interrelationship, performance is always an issue. The second part of this lesson focuses on performance in the run-time environment and gives you a number of performance tips. You should also realize that Report applications are portable across multiple platforms and multiple languages. You can reduce cross-platform development effort by planning for platform-specific features or restrictions. In this lesson, you also learn how to build reports to run on various platforms, and in different environments and languages. Objectives After completing this lesson, you should be able to do the following: • Describe the various areas to investigate to improve performance • Use the performance measuring tools • Describe the facilities available for building reports to run in other languages

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 22-3

Lesson 22: Building Reports: Efficiency Guidelines .....................................................................................................................................................

Tuning Reports

• No absolute rules • Investigate specific areas: – – – –

Data Model Paper Layout Web Layout and JSP report definitions Run-time arguments

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Explain the Trace option. Ask the students what was time-consuming in the profile output on the opposite page. Answer: Formatting and laying out the data rather than querying and fetching.

...................................................................................................................................................... 22-4 Oracle9i Reports Developer: Build Reports

Tuning Oracle9i Reports .....................................................................................................................................................

Tuning Oracle9i Reports Tuning a report is not a matter of applying a few small tweaks that will result in dramatic improvements in performance. It is more a case of investigating specific areas. The areas to investigate are the Data Model, the Paper Layout, the Web Layout and JSP reports definitions, and run-time arguments. Investigating some of these areas can result in significant performance improvements, minor performance improvements, or may have no effect on the actual performance at all, but rather impact the perceived performance. Be aware that there are no absolute standards. The Reports Measurement Tools The first step to take when attempting to tune a report is to determine where the report is spending the majority of its time in execution. In other words, does the report spend a large portion of its time in the database retrieving the actual data, in the formatting process once the data has been retrieved, or waiting for runtime resources and distribution? The Reports Trace Option The Reports Trace option produces a file that describes the series of steps completed during the execution of the report. You have the option to log all events or only a subset of events such as SQL execution or breakpoints. The trace file can provide very useful information for performance evaluation, and also tells you what executed when. Tracing can be set for various methods of report execution, for both RDF and JSP report definitions containing SQL and non SQL data sources, such as the XML and Text pluggable data sources. To set the Trace option, use the TRACEFILE= command line argument when running the report, or from the main menu select Program > Tracing. Typical profile output is shown below: Reports Profiler Statistics TOTAL ELAPSED Time: 29.00 seconds Reports Time: 24.00 seconds (82.75% of TOTAL) ORACLE Time: 5.00 seconds (17.24% of TOTAL UPI: 1.00 seconds SQL: 4.00 seconds

In this profile output, you see the execution time (total elapsed time) for the report, the amount of time spent formatting the retrieved data (Reports time), and the amount of time spent waiting for the data to be retrieved (Oracle time), comprised of time spent establishing the database connection, parsing and executing the SQL statement (UPI time), and the time spent fetching the data and executing SRW.DO_SQL() statements, EXEC_SQL statements, PL/SQL cursors, and so forth (SQL time).

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 22-5

Lesson 22: Building Reports: Efficiency Guidelines .....................................................................................................................................................

Performance Measurement

Measurement tool: Trace option Server and network measurements: • SQL Trace • Traffic and bandwidth

Copyright © Oracle Corporation, 2002. All rights reserved.

Non SQL Data Sources

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 22-6 Oracle9i Reports Developer: Build Reports

Tuning Oracle9i Reports .....................................................................................................................................................

Server and Network Specific Measurements Oracle9i Reports uses Structured Query Language (SQL) to retrieve data from a relational database. It is essential for anyone tuning reports to have a good working knowledge of SQL and to understand how the database is going to executes these statements. For those users less proficient in SQL, Oracle9i Reports provides wizards to help build queries. However, wizards cannot prevent inefficient SQL from being created (e.g., SQL that does not use indexes). Database servers and network systems often provide measurement tools to obtain performance information. An invaluable aid for tuning SQL is the SQL Trace facility provided by the database. SQL trace allows you to determine the SQL statement sent to the database as well as the time taken to parse, execute, and fetch the data. Once the trace file has been generated, use the TKPROF utility to generate the execution plan as well as vital time statistics. For more information, refer to the Oracle9i SQL Reference Manual. You may also want to consult with the database and network administrators. They may be able to suggest ways to improve performance for their respective areas. An obvious approach is to consider upgrading to a later more efficient product release, or to increase the capacities and speeds of the hardware systems and network connections. Non SQL Data Sources As you have learned, it is possible to publish data from any data source in Oracle9i Reports. Both the XML and Text pluggable data sources can be accessed through a remote URL. If speed is a concern, download the data locally and use the local data stream rather than the remote URL. You can also specify the domains for which you can bypass a proxy server. The XML pluggable data source supports validation of the XML data at runtime. You can activate this option by checking the “Validate Data Source” checkbox in the XML query definition window. When you do, the XML data is validated against the data definition you specified in the DTD or XML schema. This is a rather costly operation. If you are sure that your XML data is valid, do not activate validation. You will see a noticeable performance difference, especially if your XML data stream is very large. You have the option to specify either an XML schema or a DTD for the data definition. Using an XML schema forces type checking. With a DTD, the data is merely treated as a string and no type checking is necessary. You can also specify an Extensible Stylesheet Language file (XSL) for the XML data stream. Unless you absolutely need to apply the XSL at runtime, it is better to have the data in the correct format to start with. The pluggable Text data source supports the use of cell wrappers. This causes the file format level delimiter to be ignored for every field for which a wrapper is defined. Unless you really need this feature, avoid using cell wrappers for performance reasons.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 22-7

Lesson 22: Building Reports: Efficiency Guidelines .....................................................................................................................................................

Investigating the Data Model

• • • • •

Schema design Indexes Efficient SQL Efficient PL/SQL Calculations

• • • •

Redundant queries Break groups Group filters Linking queries

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 22-8 Oracle9i Reports Developer: Build Reports

Tuning Oracle9i Reports .....................................................................................................................................................

Investigating the Data Model If the report spends a large amount of time in the database, then it is often wise to review the data structure and how it is being used. The following areas should be examined: • Schema design The specific nature of your application will determine the most efficient Data Model, but bad schema design can have a dramatic effect. Generally speaking, query-driven applications can benefit from denormalized tables, whereas normalized tables are usually best for applications that do many updates and inserts. Oracle9i Reports is a typical nonprocedural tool geared for set-based data retrieval. Hence, an overly normalized Data Model can result in many avoidable joins or queries. • Use of indexes A lack of appropriate indexes can result in many full table scans and Cartesian joins. Indexes should be placed on columns used in the query WHERE clause, and on the tables in the detail queries. Indexes on columns in the master queries of a report have limited impact as these queries only access the database once. • Efficient use of SQL Inefficient SQL can severely impact performance. As mentioned earlier, an invaluable aid to tuning SQL in a report is the SQL Trace facility provided by the database. For reports that have large queries, it is advisable to either activate the cost based optimizer (by running ANALYZE on the tables or by setting the appropriate parameter in the init.ora file), or optimize all SQL statements following the rules laid out for the rule based optimizer. • Efficient use of PL/SQL PL/SQL that performs database operations will give better performance if it is used in stored database procedures. Stored procedures perform database operations faster than local PL/SQL program units. The opposite is true if PL/SQL does not involve any database operations. In this case, PL/SQL should be coded locally using the Program Units node in the Report Object Navigator. You may also want to consider using PL/ SQL libraries, but the performance overhead is outweighed only when the benefits of code sharing can be used.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 22-9

Lesson 22: Building Reports: Efficiency Guidelines .....................................................................................................................................................

Investigating the Data Model

• • • • •

Schema design Indexes Efficient SQL Efficient PL/SQL Calculations

• • • •

Redundant queries Break groups Group filters Linking queries

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 22-10 Oracle9i Reports Developer: Build Reports

Tuning Oracle9i Reports .....................................................................................................................................................

Investigating the Data Model (continued) • Performing calculations For calculations within a report (either through summary or formula columns), the rule of thumb is that the more calculations that can be performed within SQL, the better. If calculations are included in SQL, they are performed before the data is retrieved rather than performed on the retrieved data by Oracle9i Reports. PL/SQL code that performs a significant amount of database operations will perform better if it is implemented as a stored database procedure. SRW.DO_SQL() should be used as sparingly as possible because each call to SRW.DO_SQL() necessitates parsing and binding of the command and the opening a new cursor (just as with a normal query). Unlike the query, however, this operation will occur each time the object owning the SRW.DO_SQL() fires. For example, if a block of PL/SQL in a formula column calls SRW.DO_SQL() and the data model group where the formula resides returns 100 records, then the parse/bind/create cursor operation will occur 100 times. It is therefore advisable to only use SRW.DO_SQL() for operations that cannot be performed within normal SQL (for example to create temporary tables or any other form of DDL). • Redundant queries Ideally, a report should have no queries that return data that is not required in the report. In general, the fewer queries you have, the faster your report will run. Single query Data Models tend to execute more quickly than multiquery Data Models. However, situations can arise where a report not only needs to produce a different format for different users, but also needs to utilize different query statements depending on user input. Clearly, this could be achieved by producing two different reports. • Break groups If the report requires the use of break groups, care should be taken to ensure that the break order property is set for as few columns in the break group as possible as Oracle9i Reports appends an extra column to the ORDER BY clause of the query for every break column. The creation of a break group may also make the ORDER BY clause redundant and in this case should be removed. To give better performance, break order columns should be as small as possible and should be database columns wherever this is feasible. • Group filters It is usually more efficient to incorporate a group filter into the query WHERE clause. This restricts the number of rows returned from the database and avoids local filtering by Oracle9i Reports itself.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 22-11

Lesson 22: Building Reports: Efficiency Guidelines .....................................................................................................................................................

Investigating the Paper Layout

• • • • •

Frames Object size Format triggers Fetching ahead Bursting and distribution

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 22-12 Oracle9i Reports Developer: Build Reports

Tuning Oracle9i Reports .....................................................................................................................................................

Investigating the Data Model (continued) • Linking queries There are a number of ways to create Data Models that include more than one table. You can create a single query with a join condition or you can create two separate queries and create a link between the two. In general, it is preferable to minimize the actual number of queries as each time a query is issued, Oracle9i Reports needs to parse, bind, and execute a cursor. A single query report is therefore able to return all the required data in a single cursor rather than many. Also, with master detail queries, the detail query will be reparse, rebound, and reexecuted for each master record retrieved. In this case, it is often more efficient to use a single query report with break groups. Paper Layout Considerations Once the data has been retrieved, Oracle9i Reports needs to format the output following the Paper Layout the user has created. The time it takes to generate the layout depends on a number of factors, such as: • Frames When generating a default layout, Oracle9i Reports puts a frame around virtually every object to protect the object from being overwritten by another object. In some situations, such as boilerplate text column headings, there is clearly no risk of the objects being overwritten, hence the immediately surrounding frame can be removed. • Object size An object that is defined as variable, expanding, or contracting in either or both the horizontal and vertical directions requires extra processing. In this case, Oracle9i Reports must determine the size for the object’s instance before actually formatting the object and any objects around it. If the sizing can be set to fixed, this can be avoided as the positional relationship between objects is already known. • Format triggers Care should be taken when using format triggers as the trigger fires every time the object is formatted at runtime. Format triggers should also be placed at the highest level possible in the object/frame hierarchy so that the triggers fires at the lowest possible frequency. • Fetching ahead Oracle9i Reports provides the ability to display data such as the total number of pages or grand totals in the report margin or header pages. This requires that the entire report is “fetched ahead” before the first page can be output. The usual execution model is to format pages on an as-needed basis. Using the “read ahead” functionality does not affect the overall time it takes to generate the report, but rather affects the amount of temporary storage required and the time it takes before the first page can be viewed. To enhance perceived performance, reading ahead should be avoided when a report is going to be run to the screen in a production environment.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 22-13

Lesson 22: Building Reports: Efficiency Guidelines .....................................................................................................................................................

Investigating the Paper Layout

• • • • •

Frames Object size Format triggers Fetching ahead Bursting and distribution

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 22-14 Oracle9i Reports Developer: Build Reports

Tuning Oracle9i Reports .....................................................................................................................................................

Paper Layout Considerations (continued) • Bursting and distribution With the introduction of report bursting, a report layout can be made up of three distinct sections: header, body, and trailer. A report can comprise all three sections or it can be viewed as three separate reports within one report. Oracle9i Reports allows you to control bursting at the group record level, offering a further level of granularity in report bursting. The use of report bursting in conjunction with distribution offers considerable performance gains. Each section of a report can have multiple different formats and can be sent to multiple destinations in one single run rather than running the same report multiple times. Web Layout and JSP Report Definition You learned that Oracle9i Reports takes full advantage of JSP technology to deliver high quality Web publishing. Recall that you can use your favorite Web authoring tool to design the static portion of your Web page and then use Oracle9i Reports to add the dynamic portion (data) into the appropriate sections of the page. A poorly designed Web page has an impact on the perceived performance. Because it is possible to include any Java code in the JSP, it is easy to get carried away and mix business and data access Java code with the presentation logic. This should be avoided as it increases the footprint of the JSP and limits the efficient use and management of system resources. Customized formatting of a Web page is always an expensive operation. Any type of formatting that you cannot natively achieve through Oracle9i Reports (e.g., changing the foreground color of a report block) should be done using Java in the most direct way. The use of PL/SQL wrappers for formatting purposes in discouraged. A .jsp report definition can contain both a paper layout and a Web layout. Since the Web layout section of a JSP report could contain an tag referencing a paper layout object, Oracle9i Reports always formats the paper layout when executing the report. If your JSP report does not reference any paper layout objects, it is recommended that you use the SUPPRESSLAYOUT command option to prevent executing the paper layout. More performance tips on JSP technology can be found in the white paper Oracle9iAS Best Practices in Performance and Scalability. See http://otn.oracle.com.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 22-15

Lesson 22: Building Reports: Efficiency Guidelines .....................................................................................................................................................

Running the Report

• • • • •

RUNDEBUG=NO Array processing LONGCHUNK PARAMFORM Batch processing

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 22-16 Oracle9i Reports Developer: Build Reports

Tuning Oracle9i Reports .....................................................................................................................................................

Running the Report Having designed a report to run efficiently, it is possible to further affect the overall performance of a report by setting specific run-time arguments. By default, Oracle9i Reports automatically runs error checks on the layout and bind variables within a report. This is useful at the design stage but is generally superfluous in a production environment. Setting the runtime parameter RUNDEBUG= No turns off this extra checking at run-time. Oracle9i Reports is able to take advantage of array processing capabilities. This allows records to be fetched in batches instead of one at a time and results in fewer calls to the database. The downside of array processing is that more space is required on the execution platform for storing the arrays of records returned. If the load on the network becomes a major bottleneck in the production environment, the ARRAYSIZE argument (defined in kilobytes) can be set to as large a value as possible for the execution environment to reduce the number of network trips made to fetch the data for the report. The default value for ARRAYSIZE is 10 kilobytes. If the report has columns of the LONG, CLOB, or BLOB datatype, the LONGCHUNK parameter can be set to a large value to reduce the number of increments it takes Oracle9i Reports to retrieve the long values. If a parameter form or online previewing is not required, these functions can be bypassed by setting the PARAMFORM and BATCH system parameters to No and Yes, respectively. Calling Reports from Forms In an application built using Forms Builder and Reports Builder, it is often the case that some type of report is required on data that has already been retrieved and/or updated by the Oracle9i Forms section of the application. The tight integration between Oracle9i Forms and Oracle9i Reports allows you to pass blocks of data between the associated products. Passing data is achieved using record groups and data parameters in conjunction with the RUN_REPORT_OBJECT built-in for calling reports from forms. Sourcing a Reports query from a Forms record group avoids requerying the database and hence Oracle9i Reports will only spend time formatting the data.

Technical Note For more information on tuning your reports, see the whitepaper Tuning Oracle9i Reports on the Oracle Technology Network (http://otn.oracle.com).

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 22-17

Lesson 22: Building Reports: Efficiency Guidelines .....................................................................................................................................................

Different Development and Run-Time Environments • Reports Builder development Bitmapped only • Reports Builder Runtime – Bitmapped MODE = bitmap – Character mode MODE = character

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Mention the different environments briefly. Explain the MODE parameter.

...................................................................................................................................................... 22-18 Oracle9i Reports Developer: Build Reports

Different Development and Run-Time Environments .....................................................................................................................................................

Different Development and Run-Time Environments Introduction Although you always develop your reports in a bitmapped environment, the production reports can run in either bitmapped or character mode. Therefore, you can develop a report in a bitmapped environment that is to run in a character mode environment. Bitmapped Environment This environment has a unit of pixel and typically supports a mouse. Interaction with the host computer is continuous. The bitmapped Designer allows you, as a developer, to take advantage of the graphical user interface. Character Mode Environment This environment has a unit of character, typically 80*24, and rarely supports a mouse. Interaction with the host computer is at field level; that is, when the user navigates from the field. A report that runs in character mode cannot display any graphics features, such as images, drawings, or multimedia objects. Running a Character Mode Report from the Builder You can test your character mode reports very easily in the bitmapped Reports Builder and view the report output in the Previewer (File > Print Preview). The execution of the report as bitmapped or character simply depends on a system parameter called MODE. You can change this parameter in Reports Builder to test the character output during your report development.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 22-19

Lesson 22: Building Reports: Efficiency Guidelines .....................................................................................................................................................

Developing Reports to Run in Different GUIs • Use common fonts or map fonts in uifont.ali • Use common colors • Be aware of dots per inch (DPI)

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note uifont.ali is in the Windows installation directory \tools\common90.

...................................................................................................................................................... 22-20 Oracle9i Reports Developer: Build Reports

Developing Reports to Run in Different GUIs .....................................................................................................................................................

Developing Reports to Run in Different GUIs Introduction There are a few points that you should consider when moving a report from one GUI to another, because the following GUI differences may affect the report output. Using Common Fonts If possible, use fonts that you know exist on the target GUI; otherwise, remember to set up your font alias file so that the fonts that you use during development map to existing similar fonts on the target GUI. Example In the uifont.ali file on Microsoft Windows, map Motif and Mac fonts to Microsoft Windows: /* Motif fonts */ lucidatypewriter= "Courier New" lucidabright= "Times New Roman" fixed = "Courier New" /* Mac fonts */ "Avant Garde"= "Arial" "New York"= "Times New Roman"

In the uifont.ali file on UNIX (motif), map Microsoft Windows fonts to Motif: /* Microsoft Windows fonts */ "Courier New" = lucidatypewriter "Times New Roman"= lucidabright

Using Common Colors If possible, use a color that you know exists on the target GUI; otherwise, use one that maps well to the default color of the target GUI. Beware of Dots Per Inch The dots per inch (DPI) may differ among machines, even if they use the same GUI. This only affects how alphanumeric characters wordwrap on the screen. Therefore, if you design a report that is displayed in the Previewer, try to use the same DPI as the users who will eventually run the report.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 22-21

Lesson 22: Building Reports: Efficiency Guidelines .....................................................................................................................................................

Setting NLS Language Environment Variables • NLS_LANG • DEVELOPER_NLS_LANG, USER_NLS_LANG NLS_LANG=French_France.WE8DEC

Unicode: NLS_LANG=_.UTF8

Copyright © Oracle Corporation, 2002. All rights reserved.

Instructor Note Recommend that students use the same language and territory settings on both the client and the server. On the server, use the ALTER SESSION command to change parameters NLS_LANGUAGE, and NLS_TERRITORY.

...................................................................................................................................................... 22-22 Oracle9i Reports Developer: Build Reports

Building Reports to Run in Different Languages .....................................................................................................................................................

Building Reports to Run in Different Languages Introduction This section gives a brief overview of the features available on the server and in Oracle9i Reports Developer that provide support for building multilingual applications. • Switching languages using Oracle’s National Language Support (NLS) • Translating an application using Oracle’s TranslationHub Setting NLS Language Environment Variables Three language environment variables are available: • NLS_LANG • DEVELOPER_NLS_LANG • USER_NLS_LANG DEVELOPER_NLS_LANG and USER_NLS_LANG take the same parameter as NLS_LANG. Use them as an alternative to NLS_LANG when you need to use two sets of resource and message files at the same time; for example, if you prefer to develop using English but need to build an application in another language. Syntax NLS_LANG = _.character_set • Language: Language conventions for displaying messages and day and month names • Territory: Conventions for default date format, and for displaying currency, decimal, and thousands symbols • Character_set: Character set in which data is displayed Example NLS_LANG=French_France.WE8DEC Using Unicode in Oracle9i Reports Developer Unicode is a global character set that allows multilingual text to be displayed in a single application. Unicode is a fixed-length two-byte character set that represents up to 65,536 characters. Using Unicode in Oracle9i Reports enables you to display multiple languages in one application without switching character sets. To enable Unicode support, set the NLS_LANG environment variable as follows: NLS_LANG = _.UTF8

The data must also be stored in Unicode.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 22-23

Lesson 22: Building Reports: Efficiency Guidelines .....................................................................................................................................................

Translating an Application

Use TranslationHub to translate messages and text defined as part of the application. Consider: • Format masks • Hard-coded strings • Multiple character sets

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 22-24 Oracle9i Reports Developer: Build Reports

Building Reports to Run in Different Languages .....................................................................................................................................................

Translating an Oracle9i Reports Application In any Oracle9i Reports application, you see many types of messages, including: • Error messages from the database • Runtime error messages produced by Oracle9iAS Reports Services • Messages and boilerplate text defined as part of the application If the NLS environment variable is set correctly and the appropriate message files are available, then translation of messages for the first two bullets is done for you. To translate messages and boilerplate text defined as part of the application, you can use the Oracle translation tool, TranslationHub. For more information about using TranslationHub, see the Oracle9iDS documentation CD and the Oracle Technology Network (http://otn.oracle.com). Considerations When Designing Multilingual Applications • Format masks: Make sure to provide extra space for translation of date and numeric format masks. • Text strings: Avoid hard-coding a string containing language-specific words such as a month name. • Character sets: If using an application that will run with multiple character sets, determine the one that is most frequently used and generate the application files with the relevant NLS language settings.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 22-25

Lesson 22: Building Reports: Efficiency Guidelines .....................................................................................................................................................

Summary

In this lesson, you should have learned how to: • Use performance measuring tools • Describe the facilities for running reports in other languages

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 22-26 Oracle9i Reports Developer: Build Reports

Summary .....................................................................................................................................................

Summary •

•

Analyze the performance of your reports using the Trace option in Oracle9i Reports, as well as the SQL trace facility in the database. Tune reports by optimizing SQL, minimizing calls to the database, and minimizing the amount of unnecessary format processing required for the layout of returned data. Using Oracle’s translation tool, TranslationHub, the NLS parameters, and various features in Oracle9i Reports, you can build multilingual report applications.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 22-27

Lesson 22: Building Reports: Efficiency Guidelines .....................................................................................................................................................

Practice 22 Overview

• • • •

Identifying areas to investigate for tuning reports Using the trace facility Interpreting time statistics Improving performance

Copyright © Oracle Corporation, 2002. All rights reserved.

...................................................................................................................................................... 22-28 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 22 .....................................................................................................................................................

Practice Session Overview: Lesson 22 This practice session contains: • Generating and interpreting report trace information • Modifying a report to make it more efficient Introduction Report performance is always an issue. Reports Builder offers a few tools that allow you to measure performance. In this practice you will use them and interpret the results.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports 22-29

Lesson 22: Building Reports: Efficiency Guidelines .....................................................................................................................................................

Practice Session: Lesson 22 1 Name four main areas to investigate when tuning reports. 2 What is the name of the font mapping file? 3 Generate and interpret Reports Profiler Statistics.

Open report p19q4.rdf. From the menu select Program > Tracing. b In the Runtime Trace Settings dialog box specify p14q4.txt as the trace file and check the Profile Messages and Timestamp checkboxes. c Select OK to close the dialog box. d Run the paper layout. Paginate through the report in the Paper Design view. e Open p19q4.txt and look at the results. Where does this report spend time? f Repeat the same steps for report p22q2b.rdf. Specify p22q2.txt as the trace file. Compare the result. How do you explain this? 4 Make a suggestion to improve performance. a Open the report p12q1.rdf and generate Reports Profiler Statistics. Look at the time statistics. b Examine the Data Model. What could you possibly do to make the report run faster? c Implement your change and regenerate the time statistics. Did things improve? a

...................................................................................................................................................... 22-30 Oracle9i Reports Developer: Build Reports

A ................................

Solutions

Appendix A: Solutions .....................................................................................................................................................

Instructor Note If your setup environment uses Reports Builder 9.0.2, you need to be aware of known issues with that release, as they may affect instructor demonsrations and student practices. • Query Builder: You cannot open tables containing user-defined objects in Query Builder because Query Builder does not recognize these object types. These tables include CUSTOMERS, OC_CORPORATE_CUSTOMERS, OC_CUSTOMERS, OC_INVENTORIES, OC_ORDERS, OC_PRODUCT_INFORMATION, PRODUCT_INFORMATION, PRODUCTS, and WAREHOUSES. To create a report with one of these tables, you have to enter the query directly. • Query Builder: Using the Sort option causes Reports Builder to crash. Refer to bug #2352728 in the bug database (http://bug.us.oracle.com) for more information. • Report Wizard: If you create a report using an object column (e.g., CUST_ADDRESS in the CUSTOMERS table) and then reenter the Wizard to make modifications to the report or the layout, the selections you initially made for that object column are not retained. Refer to bug #2294059 in the bug database for more information. • Templates: If you open one of the predefined templates in Reports Builder and save it as another name, you receive “REP-69: Internal error”. Note, however, the template is saved correctly under that new name. Refer to bug #2353764 in the bug database for more information. • Creating Columns: Suppose you create a report using the Report Wizard and then manually create columns in the Data Model view of the Report Editor. In order to display the new columns, you reenter the Wizard to update the layout. Reentering the Wizard destroys the data model and you will receive several error messages (e.g., “invalid reset group”) when you finish the Wizard and the paper layout attempts to run. Refer to bug #2288263 in the bug database for more information. • Matrix Reports: If you create a matrix with group report and save it as a JSP, you will get an error: “ID attribute of rw:field tag is not unique”. Refer to bug #2310757 in the bug database for more information.

...................................................................................................................................................... A-2 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 2 .....................................................................................................................................................

Practice Session Overview: Lesson 2 This practice session contains: • Previewing a report in a browser • Executing a number of different reports. For each report, answer some questions Introduction This practice session consists of a combination of practical and paper-based questions.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-3

Appendix A: Solutions .....................................................................................................................................................

Practice Session Solutions: Lesson 2 1 Invoke the Reports Builder executable.

Select Reports Builder from the Oracle9i Reports menu: Start > Programs > Oracle9i Developer Suite > Oracle9iDS V2 > Reports Developer > Reports Builder. This may differ according to the classroom setup. 2 Open the report called p2q2.jsp. Run the Web layout. From the menu select File > Open. Locate p2q2.jsp in your working directory and click Open. Select Program > Run Web Layout from the menu or select the Run Web Layout icon on the horizontal toolbar. a Examine the report in your browser. Among the styles discussed in this lesson as a reference, what style of report is it? Tabular report. b How many groups of data are there in this report? One group of data, each row being displayed once within the group. c In the browser, use the scroll bars to move up and down the report to see all of the data. Use the scroll bar on the right side of the browser window to scroll up and down the report data. d Close the browser window. You are now back in Reports Builder. In the browser, click the Close icon in the upper right corner or select File > Exit from the menu. 3 Open report p2q3.rdf and run the paper layout. From the menu select File > Open. Locate p2q3.rdf in your working directory and click Open. Select Program > Run Paper Layout from the menu or select the Run Paper Layout icon in the horizontal toolbar. a In the Paper Design view, move to the next page. Notice that this report has a header page with text. Select Next Page to navigate from one page to another. b What style of report is this? Tabular report.

...................................................................................................................................................... A-4 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 2 .....................................................................................................................................................

Close the Paper Design view. From the File menu, select Print > Preview. In the Previewer window, experiment with the horizontal and vertical split screen features. Select File > Print Preview. To split the Previewer horizontally: Select the small gray area at the top of the right scroll bar. Press the left mouse button down and drag the gray area. As you do this, you see that a thick dotted line is dragged down the Previewer. Depress the left mouse button when you have reached the desired split point. Now you have two windows, which work independently from each other. Move the split bar back to its original position. To split the Previewer vertically: Select the small gray area at the left of the horizontal scroll bar at the bottom of the window. Press the left mouse button down and drag the gray area. As you do this, you see that a thick dotted line is dragged right across the Previewer. Depress the left mouse button when you have reached the desired split point. Now you have two windows, which work independently from each other. d Close the Previewer window. In the Previewer, click Close Previewer. 4 Open report p2q4.jsp and run the Web layout. Select File > Open, locate p2q4.jsp, and click Open. Select Program > Run Web Layout from the menu or select the Run Web Layout icon on the horizontal toolbar. a What style of report is this? It is a Group Left master/detail report. b How many groups of data are in this report? There are two groups of data. c How many pages are there in this report? There are no pages in a Web report. In Reports Builder, run the paper layout to determine the number of pages. There are three pages in this report. d Close the browser. In the browser, click Close. c

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-5

Appendix A: Solutions .....................................................................................................................................................

5 Open report p2q5.jsp and run the Web layout.

From the menu select File > Open. The File Browse window opens. Locate p2q5.jsp and select Open. Select Program > Run Web Layout from the menu or select the Run Web Layout icon from the horizontal toolbar. a What style of report is this? It is a Matrix report. b How many groups of data are in this report? There are four groups of data: Year Department Name Job ID Salary There actually is an additional, invisible “cross product” group around Year, Department Name, and Job ID; this special group defines the matrix structure and is explained in the lesson on matrix reports. c Close the browser window. In the browser, click Close. 6 Open report p2q6.rdf and run the paper layout. From the menu select File > Open. The File Browse window opens. Locate p2q6.rdf and click Open. Select Program > Run Paper Layout from the menu or select the Run Paper Layout icon in the horizontal toolbar. a Notice the Parameter Form. You are requested to enter a customer ID. The report will display order information based on your input. b Specify a valid customer ID and run the report. Valid values include 101, 118, 148 and 170; pick one of these and then select Run Report. Click in the customer ID field and enter one of the suggested numbers. Select Run Report. c What do you think would be a better method of providing valid values to a user? It would be more user-friendly to display a list of values. And it would be even more meaningful to add customer names. This can easily be built into the report. You will see how this is done in a later lesson.

...................................................................................................................................................... A-6 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 2 .....................................................................................................................................................

d e

Close the Paper Design view. In the Paper Design view, click Close. Run the paper layout again. This time do not specify a customer ID. From the menu select Program > Run Paper Layout. Immediately select Run Report without entering a value for customer ID. i What is the result? The result is that the report does run; however, it does not display any data. There are no NULL customer IDs. ii In a production situation, what should happen in this instance? The user should be prevented from entering a NULL value or a blank. In later lessons you will see how this can be implemented.

Close the Paper Design view. In the Paper Design view, click Close. g Run the paper layout again. This time enter Customer ID 999. From the menu select Program > Run Paper Layout. Enter the customer ID 999 and select Run Report. i What is the result? The result is that the report does run; however, it does not display any data. There is no customer ID 999. ii In a production situation, what should happen in this instance? The user should be prevented from entering an invalid value. In later lessons you will see how this can be implemented. h Close the Paper Design view. In the Paper Design view, click Close. 7 Open report p2q7.rdf and run the paper layout. From the menu, select File > Open. Locate p2q7.rdf and select Open. a In the Parameter Form, notice the list of values for the customer information. The report displays order information based on your input. b Select a valid customer name and run the report. Select a customer name from the list of values for customer. c Close the Paper Design view. In the Paper Design view, click Close. f

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-7

Appendix A: Solutions .....................................................................................................................................................

Practice Session Overview: Lesson 3 This practice session contains: • Invoking Reports Builder • Opening an existing report • Switching views in the Report Editor • Accessing the Help system Introduction The questions in the practice session provide an introduction to the Reports Builder interface. You open and navigate through an existing report definition and use the Help system to obtain information about some of the Report executables.

...................................................................................................................................................... A-8 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 3 .....................................................................................................................................................

Practice Session Solutions: Lesson 3 1 Start Reports Builder and open the existing report: p3q1.jsp. Run the paper

layout. On your desktop, locate the Reports Builder icon and launch the executable. In the Welcome dialog box of Reports Builder select the Open an Existing Report option and click OK. Open p3q1.jsp from the file system and run the paper layout. Use the iconic button on the horizontal toolbar, or use the Run Paper Layout option in the Program menu. If prompted, connect to the database using the login information your instructor gives to you. When the Paper Parameter Form comes up, select one of the customers in the list, and select Run Report. 2 Using the same report, display the Data Model view of the Report Editor. In the Object Navigator, select Q_1. Hint: Move the Data Model window to the right so that you can also see the Object Navigator. Use the Find field at the top of the Object Navigator to locate Q_1. Notice the object that is selected in the Data Model view. If the Report Editor window is still active from the previous exercise, select View > Change View > Data Model from the menu. If you closed the Report Editor after the previous exercise, select Tools > Report Editor from the menu, or double-click the Data Model object icon in the Object Navigator. Move the Data Model window to the right of your screen so that you can also see the Object Navigator. Activate the Object Navigator window. Click in the Find field and enter Q_1. The Navigator expands the appropriate node and highlights the first object that meets the entered value. In the Data Model view, the query object Q_1 is selected.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-9

Appendix A: Solutions .....................................................................................................................................................

3 Using the same report, switch to the Paper Layout view of the Report Editor.

In the Object Navigator, select F_CUSTOMER_ID. Hint: Use the Find field at the top of the Object Navigator. Notice the object that is selected in the Paper Layout view. Fully expand the Paper Layout node and select R_G_ORD_ID. Notice the object that is selected in the Paper Layout view. While the Report Editor window is active, select View > Change View > Paper Layout from the menu. Activate the Object Navigator window. Click in the Find field and enter F_. The Navigator expands the appropriate node and highlights the first object that meets the entered value. In the Paper Layout view the field F_CUSTOMER_ID is selected. In the Object Navigator window, select the Paper Layout node and select Expand All in the vertical toolbar; or select View > Expand All from the menu. Select R_G_ORD_ID. In the Paper Layout view, the repeating frame R_G_ORD_ID is selected. 4 Using the same report, run the Web layout. Select Program > Run Web Layout from the menu, or select the Run Web Layout iconic button from the horizontal toolbar. 5 Use Help Contents to answer the following questions: From the menu, select Help > Help Contents. Use the Index tab to locate each term. a What is RWSERVLET? Enter RWSERVLET. RWSERVLET is the executable for the Reports Servlet. b What is RWCLIENT? Enter RWCLIENT. RWCLIENT is the executable for the Reports Client. It parses and transfers the command line to the Reports Server. c What is the Web Source view? Enter Web Source. The Web Source is a view of the Report Editor that displays the source code for your Web report, including JSP and HTML tags.

...................................................................................................................................................... A-10 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 4 .....................................................................................................................................................

Practice Session Overview: Lesson 4 This practice session contains: • Creating and saving a Tabular report • Modifying the report to create and save a Group Above break report • Creating and saving a form letter • Creating and saving a Matrix report • Creating and saving a Matrix with Group report Introduction The questions in the practice session are intended to ensure that you have a good understanding of how to create various styles of reports using the Report Wizard. Note: When you are completing the practice sessions, keep to the filenaming conventions that the questions specify. You might need these files in later practices. In addition, note the field headings and widths. With many reports, you need to change these to achieve the desired result. Many of the practice sessions ask you to rename files beforehand; be sure to use Save As, not Save, so that you do not overwrite the existing file name.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-11

Appendix A: Solutions .....................................................................................................................................................

Practice Session Solutions: Lesson 4 Invoking Reports Builder and the Wizard If Reports Builder is open after the previous practice session: • Close any reports currently in the Object Navigator. • Create a new report by selecting the Reports node and selecting Create Object. Note: There is an alternative method. If no reports are open in the Object Navigator, you can double-click the Reports node. 1 Create a tabular report, paper layout only, containing the following data. Use Query Builder to build the query. DEPARTMENTS: DEPARTMENT_NAME EMPLOYEES: FIRST_NAME, LAST_NAME, JOB_ID, SALARY Display all fields. Do not make any changes to the other Wizard pages. In the Select Data Tables dialog box, highlight the DEPARTMENTS and EMPLOYEES tables, click Include, and close the dialog box. In the Query Builder window, select the columns from the tables and click OK. Click Next. Copy all fields from the Available Fields area to the Displayed Fields area. Click Next on all other Wizard pages and click Finish on the last page.

...................................................................................................................................................... A-12 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 4 .....................................................................................................................................................

2 Using the reentrant Report Wizard, make the following modifications (test your

report frequently). Select Tools > Report Wizard from the menu or the right-mouse-button menu. a Modify the query to sort the data in descending order of salary. On the Data tab choose Query Builder. In the Query Builder window select Sort. Copy the SALARY column to the Sorted Columns, select it, and select Sorting Order Descending. Close the Sort dialog box and the Query Builder. Click Finish to test your report. b Add a summary to show the total salary value. Reenter the Report Wizard by choosing Report Wizard from the menu. On the Totals tab select SALARY and click Sum. c Ensure the width of SALARY and TOTAL is 6. Make the necessary changes on the Labels tab. d Select the Green template. Select the Green template on the Templates tab and click Finish to test your report. e Save the report to a file named p4q2.rdf . Select File > Save As from the menu and save the file to disk. 3 Using the reentrant Wizard, modify the report to create a break report. a Select the report style Group Above. Select the report style on the Style tab. b Modify the query and remove the join clause so that the report contains all employees and not just managers. Select the Data tab. Edit the query, removing the join clause HR.DEPARTMENTS.MANAGER_ID=HR.EMPLOYEES.EMPLOYEE_ID. Watch for syntax errors! c Group the report by the department name. On the Groups tab copy DEPARTMENT_NAME to the Group Fields area. d Save the report as p4q3.rdf. Select File > Save As from the menu and save the file to disk.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-13

Appendix A: Solutions .....................................................................................................................................................

4 Create a new report as follows:

Using the Report Wizard, create a paper layout and select the Mailing Label style. Use Query Builder to build the query. Create a new report by selecting the Report node in the Object Navigator and choosing the Create icon. As an alternative you can also select File > New > Report from the menu. Select Create paper layout only. Select Mailing Label style. b Enter the query in the data source definition window: For the query type, accept the default selection of SQL statement. Enter the query text: select c.cust_first_name || ' ' || c.cust_last_name Name, c.cust_address from customers c c In the text area, include all six customer fields, each field on a new line. Copy NAME, STREET_ADDRESS, CITY, STATE_PROVINCE, COUNTRY_ID, and POSTAL_CODE to the Mailing Label Text area. To enter the line feeds you can either use the button or simply press the [Return] key. d Select No template and click Finish. Select the No template radio button on the Template page and click Finish. e Save the report to a file named p4q4.rdf. Select File > Save As from the menu and save the file to disk. a

...................................................................................................................................................... A-14 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 4 .....................................................................................................................................................

5 Using the reentrant Wizard, modify the report to create a Form Letter style.

Select Tools > Report Wizard from the menu or the right-mouse-button menu. On the Style tab select Form Letter. a Modify the query to include order information for each customer: On the Data tab, modify the query text as follows: select c.cust_first_name || c.cust_last_name Name, c.cust_address, o.order_id, o.order_total from customers c, orders o where c.customer_id = o.customer_id b In the text area, enter some free-flowing text for each order. For example: Order No. & has been shipped to & in &. The order has a total value of & dollars. Thank you for your business. Copy the fields that you want to use to the Form Letter Text area and enter your free-flowing text. c Select a different template and click Finish. On the Template page in the Wizard, select any one of the predefined templates. Click Finish. d Save the report as p4q5.rdf. Select File > Save As from the menu and save the file to disk. 6 Create a new report. Create a new report by selecting the Report node in the Object Navigator and choosing Create icon. As an alternative you can also select File > New > Report from the menu. a Create both a Web and paper layout. Select the matrix report style. On the first page, select Create the radio button Create both Web and Paper Layout. On the Style page choose Matrix. b For the query, import the contents of p4q6.sql. Choose Import SQL Query and select p4q6.sql from the Import dialog box. c Display customer names down the left side of the page. Copy NAME to the Matrix Row Fields area. d Display product numbers across the top of the page. Copy PRODUCT_ID to the Matrix Column Fields area.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-15

Appendix A: Solutions .....................................................................................................................................................

Display the total value in the cells. Select Sum to copy the sum of TOTAL_VALUE to the Matrix Cell Fields area. f Create a summary to give the total of the sum(total_value) values. Select Sum(TOTAL_VALUE) and choose Sum. g Change the width of all four summaries to 4. Change the PRODUCT_ID label to Product. Remove the label for sumTOTAL_VALUE. Make the necessary changes in the Labels and Width fields. h Select any template and click Finish to preview your report. Select a template and click Finish to run your report. i Save the report to a file named p4q6.jsp . Select File > Save As from the menu and save the file to disk. 7 Modify the matrix report to create a Matrix with Group. Select Tools > Report Wizard from the menu or the right-mouse-button menu. On the Style tab select Matrix with Group. a Select Month as the group and check that all totals have a width of 4. Go to the Group tab and copy MONTH to the Matrix Group Fields area. Check on the Labels tab that all fields with a label Total: have a width of 4. b Click Finish to preview your report and save the report as p4q7.jsp. Select File > Save As from the menu and save the file to disk. Note: The query for the matrix reports above has been restricted to display only products beginning with “1,” so that you can see and understand the complete matrix more easily. e

...................................................................................................................................................... A-16 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 4 .....................................................................................................................................................

8 Web-enable a paper report.

Open report p2q9.rdf and run the paper layout. To open the file select File > Open from the menu or choose Open from the Toolbar. Run the report by selecting the Run Paper Layout icon from the horizontal toolbar. b Add a bookmark to the report, letting the department names serve as bookmarks. Select Insert > Bookmark from the menu. Copy the Department_Name field from the Available Columns to the Bookmarks. Click OK. c Generate HTML Style Sheet output. Save the file as p4q8.htm. Select File > Generate to File and select Paginated HTMLCSS. In the Save file dialog box change the filename to p4q8.htm and select Save. d Open the report in a browser. Start your browser. Select File > Open and use the Browse button to specify the location and name of your file. e What happens when you click on the bookmarks? The cursor jumps to that specific part in the report. f Close the browser and in Reports Builder, save the report as p4q8.rdf. Close the browser. Select File > Save as and change the filename to p4q8.rdf. Select Save. g Save and close all reports. For each open report, select File > Save from the menu or Choose Save from the horizontal toolbar. Then Select File > Close. a

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-17

Appendix A: Solutions .....................................................................................................................................................

Practice Session Overview: Lesson 5 This practice session involves modifying reports by applying changes in the Paper Design view of the Report Editor. Introduction In this practice session, you will reuse some of the reports that you created in the last session.

...................................................................................................................................................... A-18 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 5 .....................................................................................................................................................

Practice Session Solutions: Lesson 5 1 Open report p4q2.rdf. Make the following changes in the Paper Design view:

To open the file select File > Open from the menu or select Open on the toolbar. Run the report by selecting it in the Object Navigator and then selecting Program > Run Paper Layout from the menu or selecting the Run Paper Layout icon in the toolbar. a Make the SALARY column right-justified. Do not forget to justify the column header accordingly. Select the column SALARY and its label. Then choose Align Right from the style bar or select Format > Justify > Right from the menu. b Add a comma and a currency symbol to SALARY. Add two decimal places. While the SALARY column is selected, click Commas from the style bar. This adds the group separators. Then click Add Decimal Place from the style bar to add one decimal place. Repeat the action for the second decimal place. You can also select the commas and decimal places options from the Format > Number menu. c Make the total at the end of the report right-justified and add commas, a currency symbol, and two decimal places, as in the column SALARY. Select the column, then select Align Right from the style bar or select Format > Justify > Right from the menu. While the column is selected choose Commas, Currency, and then Add Decimal Place from the style bar. Repeat the action for the second decimal place. d Change the label of the total to italic font. Select the label, then select Italic from the style bar or use Format > Font in the menu. e Make whatever other changes you like. No formal solution. f Save the report to a file named p5q1.rdf and close it.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-19

Appendix A: Solutions .....................................................................................................................................................

2 Open report p4q3.rdf. Make the following changes in the Paper Design:

To open the file select File > Open from the menu or select Open from the toolbar. Run the report by selecting it in the Object Navigator and then selecting Program > Run Paper Layout from the menu or selecting Run Paper Layout from the toolbar. a Add a border with a 2 pt. width around the total for each department. Select the column for department total and choose Line Color from the vertical toolbar. Select any color to add the border. With the column selected, format the width of the border: select Format > Line > Line Width > 2 pt. b Add commas and two decimal places to the SALARY field and the total and make them right-justified. Do not forget to justify the column header for the SALARY field accordingly. Select the total, the column, and its header, then select Align Right from the style bar or select Format > Justify > Right from the menu. While the column is selected choose Commas and then Add Decimal Place from the style bar. Repeat the action for the second decimal place. You can also select the options from the Format menu. c Make the same changes to the total at the end of the report. Move the grand total so that it aligns with the SALARY field (you must do this manually; use the ruler guides to help you). Select the column, then choose Align Right from the style bar or select Format > Justify > and from the menu. While the column is selected choose Commas and then Add Decimal Place from the style bar. Repeat the action for the second decimal place. You can also select the options from the Format menu. d Change the color of the employee data heading to blue. Select a label, and choose Select Parent Frame from the style bar. Select Fill Color from the vertical toolbar. e Make whatever other changes you like. No formal solution. f Save the report to a file named p5q2.rdf and close it.

...................................................................................................................................................... A-20 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 5 .....................................................................................................................................................

3 Open report p5q3_a.rdf. Make the following changes in the Paper Design:

To open the file select File > Open from the menu or select Open from the toolbar. Run the report by selecting it in the Object Navigator and then selecting Paper Layout from the menu or selecting Run Paper Layout from the toolbar. a Add a date at the top center of the page. Give it any format you want. Hint: Choose the font, style, and text color you want before inserting the date. Select Insert > Date and Time from the menu. b Make whatever other changes you like. No formal solution. c Save the report to a file named p5q3.rdf and close it. 4 Open report p4q6.jsp. Make the following changes in the Paper Design: To open the file select File > Open from the menu or select Open from the toolbar. Run the report by selecting it in the Object Navigator and then selecting Program > Run Paper Layout from the menu or selecting Run Paper Layout from the toolbar. a Right-justify the Product ID field. Select the Product ID field, then select Align Right from the style bar or select Format > Justify > Right from the menu. b Add commas and two decimal places to the cells and summaries and make them right-justified. Select the columns and choose Align Right from the style bar or select Format > Justify > End from the menu. While the columns are selected choose Commas and then Add Decimal Place from the style bar. You can also select the options from the Format menu. c Why are some of the cell and summary values displayed with asterisks? What can you do to correct this? The addition of the comma and two decimal places increased the width of the data such that it exceeded the display size. To correct this, reenter the Wizard and increase the width of the cell and summary fields. Select Report Wizard from the right-mouse-button menu. Select the Labels tab and choose a width more suitable for displaying the formatted values. Try a width of 6. d Add a blue line color to the parent frame of NAME. Select the column NAME and choose Select Parent Frame from the style bar. Select Line Color from the vertical toolbar.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-21

Appendix A: Solutions .....................................................................................................................................................

e

Save the report to a file named p5q4.jsp and close it.

If You Have Time 5 Open the file p5q2.rdf and conditionally highlight the SALARY field. To open the file select File > Open from the menu or select Open from the toolbar. Run the report by selecting it in the Object Navigator and then selecting Program > Run Paper Layout from the menu or selecting Run Paper Layout from the toolbar. a If the salary is less than 8500, print the field in italics. Select the SALARY field, then choose Format > Conditional Formatting from the menu or use the right-mouse-button to open the pop-up menu and select Conditional Formatting from the pop-up menu. The Conditional Formatting dialog box opens. Click New and enter the condition and formatting as required. Select a font name. Click OK to return to the first page but do not close the dialog box. b If the salary is more than 12000, print the field in bold italics and make the text color red. Click New again and enter the second condition and formatting as required. Click OK to return to the first page of the dialog box and then OK again to close the dialog box. c Save the report to a file named p5q5.rdf and close it.

...................................................................................................................................................... A-22 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 6 .....................................................................................................................................................

Practice Session Overview: Lesson 6 This practice session contains: • Customizing an existing template • Applying the modified template to an existing report Introduction In this practice session, you open an existing template, which is one of the standard templates shipped with the Oracle9i Reports Developer installation. You modify objects in the margin and body sections and save the template to a new filename. Then, you apply the modified template to an existing report and note the differences. Instructor Note The practice does not include modifying the preferences file to register predefined templates. You can suggest that the students do so, if you have time and if your class setup allows students to modify the preference file and to write to the \reports\templates directory.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-23

Appendix A: Solutions .....................................................................................................................................................

Practice Session Solutions: Lesson 6 1 Customize an existing template, adding a company logo and report title to the

report margin, and modify the report body. a Open rwblue.tdf. This file should exist in your working directory; it is a copy of the Blue predefined template. Select the Templates node in the Object Navigator. Select Open and select the file named rwblue.tdf. b Display the margin region. (This usually appears by default when you open a template.) Select Tools > Template Editor from the menu. If the margin region is not displayed, click the Margin icon in the horizontal toolbar. c Delete the Your Company logo. Import towers2.jpg and place it in the top left corner of the margin; this file should exist in your home directory. Select the Oracle buildings logo and press the [Delete] key. Select Insert > Image from the menu. From the File Browse window choose the file name towers2.jpg. Resize it to fit into the margin region. d Add a report title:Lakeside Technologies. Select the Text tool from the vertical toolbar. Click inside the editor and type in the title. Select a font and font size. Click outside the textbox to end. e Display the body region to view the changes you will make in the next steps. Click the Margin button in the horizontal toolbar in the Template Editor. f Change the justification of numeric fields (number field and summary) to Right. Do not forget to change the justification of labels for number fields to Right. Select the number field and the number heading in the Template Editor and click the Align Right tool from the stylebar. Alterative solution: Open the Field Labels/Headings and the Fields nodes in the Object Navigator. Select Number in both nodes. Open the Property Inspector for your selections. Click Union (the button is a toggle and the tooltip says Intersection at this point) from the Property Inspector toolbar to display all properties. Under the headings Label and Fields, change the Number Justification property to Right.

...................................................................................................................................................... A-24 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 6 .....................................................................................................................................................

Select Summaries in the Object Navigator. In the Property Inspector change the Number Justification property for fields to Right. g Save the template as laketech.tdf. 2 Apply the modified template to an existing report. Hint: You must search for the template file; it does not appear in the list of predefined templates. Open p4q3.rdf. b Apply laketech.tdf to the report. Invoke the Report Wizard. On the Template tab select the option Template File. Click Browse to enter the name of the file in the field below. c The report shows some changes but not all. Which template changes have been applied and which have not? Changes applied: Logo and report title Changes not applied: Number justification d To apply all changes to your report, you will have to create the layout again. From the menu, select Edit > Select All to select all layout objects. Now delete them. Invoke the Report Wizard and reapply your template. e Save the report as p6q2.rdf. a

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-25

Appendix A: Solutions .....................................................................................................................................................

3 Continue to modify the template and test it using a new report.

Change the text color of the master group fields to red for the Group Left style only. Hint: In the Template Editor, use the Report Style list to switch to a Group Left style. This way you can select the correct group. You may also want to use the Zoom Out tool so that you can see the entire template layout structure, and recognize the difference between the master group fields and the detail group fields. Select the Character, Number, and Date fields and change the text color with the Text color palette. Alternative solution: In the Object Navigator, expand the Override node, then the Group Left, the Section (Level1), and the Fields nodes. Bring up the Property Inspector for all the fields Change the text color to red. b Save your template as laketech2.tdf and close it. c Create a new report, using the Report Wizard. Use the layout style Group Left. Import the query p6q3.sql from your working directory. Make DEPARTMENT_NAME the group field. Display all other columns. Do not create summaries. Change the width of SALARY to 6 and COMMISSION_PCT to 4. Apply the Template file laketech2.tdf. No formal solution. d Save the report as p6q3.rdf and close it. a

...................................................................................................................................................... A-26 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 7 .....................................................................................................................................................

Practice Session Overview: Lesson 7 This practice session contains: • Creating reports for the Web using the Report Wizard • Examining the Web source code • Modifying a Web report using the Report Wizard Introduction: In this practice session, you create simple reports for the Web using the Report Wizard. You also examine the Web source code to identify specific JSP tags.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-27

Appendix A: Solutions .....................................................................................................................................................

Practice Session Solutions: Lesson 7 1 Create a tabular report, Web and paper layout, entitled Compensation Report. The

report should contain the following data: SELECT d.department_name, e.first_name, e.last_name, e.job_id, e.salary FROM departments d, employees e WHERE d.department_id = e.department_id a Display all fields. Select the predefined Blue template. Do not make any changes to the other Wizard pages. Crete a new report using the Report Wizard. Create both a Web and paper layout. Select Tabular as the style and enter Compenation Report as the title. Select SQL as the data source and enter the query text from above. Select all fields for display. Do not create any totals. Select the Blue template and Finish the Wizard. b Run the Web layout to test. Select Program > Run Web Layout from themenu or select the Run Web Layout icon from the horizontal tool bar. c Examine the Web source. How many tags are in the code? Why this number? Display the Web Source view of the Report Editor by double-clicking the Web Source object icon in the Object Navigator. Use Edit > Find and Replace to locate the rw:foreach tag(s). There is one tag in this Web source code because there is one group in the data model. d Modify the report using the Report Wizard. Make this a Group Left break report displaying employing information grouped by department. Include a sum for salary. Reenter the Wizard. Select Group Left as the style. On the Groups page, select DEPARTMENT_NAME from the Available Fields list and copy into the Group Fields list. On the Totals page, select SALARY and click Sum >. Finish the Wizard. e Examine the Web source again. Now how many tags are in the code? Display the Web Source view of the Report Editor. Use Edit > Find and Replace to locate the rw:foreach tag(s). There are two rw:foreach tags, one for each group in the Data Model—one for the master group, one for the detail group.

...................................................................................................................................................... A-28 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 7 .....................................................................................................................................................

Which JSP tag identifies the overall appearance and formatting of the data in the Web report? The rw:style tag identifies the style sheet for the report. g Which JSP tag delimits the data model and layout information? Can you view this information in the Reports Builder? The rw:objects tag delimits the data model and the layout information for a Reports JSP. Reports Builder hides this information. To see it, use a text editor such as Wordpad. h Run the Web layout to test. Save the report as p7q1.jsp. 2 Create a report for the Web that displays current product information. a Use the Report Wizard to create a new tabular report called Our Products. Include the following data from the PRODUCT_INFORMATION table: f

Column product_id product_name product_description catalog_url

Label Product No. Name Description Catalog URL

Sort the output by product number.. Create a new report using the Report Wizard. Create both a Web and paper layout. Select Tabular as the style and enter Our Products as the title. Select SQL as the data source and enter the query: SELECT product_id, product_name, product_description, catalog_url FROM products ORDER BY product_number Select all fields for display. Do not create any totals. Modify the labels as above. b Select the predefined Beige template. Select the Beige template and Finish the Wizard. c Run the Web layout to test. Select Program > Run Web Layout from themenu or select the Run Web Layout icon from the horizontal tool bar. d Save the report as p7q2.jsp.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-29

Appendix A: Solutions .....................................................................................................................................................

Practice Session Overview: Lesson 8 This practice session contains: • Modifying report hierarchy by creating additional groups • Restricting records in a query • Restricting records with a packaged filter Introduction In this practice session, you modify an existing tabular report and create a break report. Using two different techniques, you restrict the records in a report.

...................................................................................................................................................... A-30 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 8 .....................................................................................................................................................

Practice Session Solutions: Lesson 8 1 Open report p8q1_a.jsp, a tabular report showing customer information.

Run the paper layout. Select Program > Run Paper Layout from the menu or select Run Paper Layout from the toolbar. b In the Data Model view, create a break group using NLS_TERRITORY as the break column. Rename the group G_COUNTRY. Open the Data Model view of the Report Editor. Click and drag NLS_TERRITORY up out of the G_CUSTOMER_ID group, creating a new group above G_CUSTOMER_ID. Double-click the group bar to display the Property Inspector for this new group. Enter G_COUNTRY in the Name property field. Alternative solution: In the Object Navigator, double-click on the name to obtain the I-beam, and then edit the name as desired. c Reenter the Report Wizard to update the layout. Select the Group Left style. Ensure that all fields are displayed. Select Tools > Report Wizard from the menu or the right-mouse-button menu. On the Style tab select Group Left. On the Fields tab, make sure that all fields are listed in the Displayed Fields area. Click Finish. Examine the Web Source and note that is was updated with the new group information. Locate . d Save the report as p8q1.jsp. Do not close the report. 2 Modify the previous report to restrict the number of customer records processed by the group. a Place a filter on the G_CUSTOMER_ID group to display only the first ten customers. Run to test. Select the group G_CUSTOMER_ID in the Data Model and open the Property Inspector. Set the property Filter Type to First and enter 10 for the Number of Records property. b Save the report as p8q2.jsp and close it. a

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-31

Appendix A: Solutions .....................................................................................................................................................

3 Modify p8q1.jsp to restrict the report to customers who have a credit limit

greater than 1500. You need to modify the query properties. a Include CREDIT_LIMIT in the query statement, although you do not need to display it. Restrict the query statement to show those customers who have a credit limit greater than 1500. In the Data Model select the query Q_1. Press the right-mouse-button to open the Query Dialog or open it from the Property Inspector. Enter WHERE CREDIT_LIMIT > 1500 directly in the query statement area. b Save the report as p8q3.jsp and run the Web layout.

...................................................................................................................................................... A-32 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 9 .....................................................................................................................................................

Practice Session Overview: Lesson 9 This practice session contains: • Creating a second query in an existing report • Creating a data link • Creating a report using a pluggable data source • Creating a report containing a REF cursor query Introduction In this practice session, you include new data in a report by adding a query to an existing single-query report using the Data Wizard. You also practice linking data from different queries to alter the report structure. You will create a new report using the XML pluggable data source and a SQL query.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-33

Appendix A: Solutions .....................................................................................................................................................

1 Open p8q1_a.jsp. a

b

c

d

e

f g

h

In the Data Model, create the following query: QUERY: Q_ORDERS select customer_id, order_id, to_char(order_date, 'dd-Mon-yyyy') order_date, order_total from orders Open p8q1_a.jsp from the file system. Display the Data Model view of the Report Editor by double-clicking the Data Model icon in the Object Navigator. Select the SQL Query tool and click in the Data Model painter area. Enter the query as above and click OK. Change the name of the group to G_ORDERS. Double-click the group bar to display the Property Inspector for new group. Enter G_ORDERS in the Name property field. Open the Report Wizard, create both a Web and paper layout, and select a Tabular style. Invoke the Report Wizard. Make the appropriate selections on the Report Type and Style pages. Copy group G_ORDERS to the Displayed Groups area. On the Groups page, select G_ORDERS from the Available Groups area and copy to the Displayed Groups area. Copy all remaining fields to the Displayed Fields area. On the Fields page, select all the remainig fields from the Available Fields area and copy to the Displayed Fields area. Select a template and click Finish. Note how the data appears in the Paper Design view. The G_CUSTOMER_ID data appears before the G_ORDERS data. Remember that you can alter the order in which the data isdisplayed by modifying the query order in the Data Model. You may want to try that. Note: If you do adjust the order of the queries, put them back inthe original order and save before the next question. Save the report as p9q1.jsp.

...................................................................................................................................................... A-34 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 9 .....................................................................................................................................................

2 Continue with the same report.

In the Data Model, link the two queries together on the primary and foreign keys. Select the Data Link tool. Click and drag from the CUSTOMER_ID column in the Customer group to the CUSTOMER_ID1 column in the Orders group. b Redo the layout by opening the Report Wizard. Select the Group Above style. Do not display CUSTOMER_ID. Reenter the Report Wizard and make the necessary changes. c Save the report as p9q2.jsp. d Run the Web layout. 3 Create a new report that lists the sales representatives and the products they have sold. a Create both a Web and paper layout and select a Group Above report style. b Import the query file p9q3_a.sql. c Make NAME and EMPLOYEE_ID the group fields. Make sure that these fields are in one group, at Level 1, and not two separate groups. On the Groups page, select NAME and EMPLOYEE_ID in the Available Fields area and copy them to the Group Fields area, making sure they are both at the same level. d Do not display EMPLOYEE_ID, but display all other fields in the order described in the table below. e Change the following field attributes: a

Column name product product_id quantity prod_total

Label Name Product ID Quantity Total

Width 20 16 4 4 9

Modify the lables and widths according to the table above. f Use the predefined Gray template. g Click Finish to run your paper layout.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-35

Appendix A: Solutions .....................................................................................................................................................

h

i j

k

l

Now you need to show not only which products the sales representatives have sold but also who are the customers they have done business with. Go to the Data Model and add a new query, importing the file p9q3_b.sql. This query returns the customer names, their IDs, and the total sales per customer for each sales representative. Display the Data Model view. Select the SQL Query tool and click in the painter area. Select Import SQL Query and locate the file p9q3_b.sql. Ensure that the group is named G_CUSTOMER. Link the second query to the correct group. Select the Data Link tool. Click and drag EMPLOYEE_ID in G_NAME to SALES_REP_ID in G_CUSTOMER. Use the Report Wizard to display the new group. Do not display SALES_REP_ID or EMPLOYEE_ID. Reenter the Wizard and make the requested changes. Change the following field attributes:

Column customer customer_id cust_total

Label Customer ID Total

Width 20 2 9

m Save the report as p9q3.jsp.

Run the Web layout. 4 An international business has warehouses around the world. The database at corporate headquarters maintains information about each of the warehouses (warehouse id, name, city, country, and so forth). However, each warehouse stores it operational data locally in a relational database. A planning team at headquarters needs to access this inventory data for every warehouse. The warehouse data is only available as an XML stream. Create a report that combines general data stored in headquarters with the inventory data from each warehouse location. a Create a new report manually. Select File > New > Report or select the Report node inthe Object Navigator and click the Create icon. Select the radio button Build a new report manually. b In the Data Model view, create a SQL query. Import the SQL query p9q4.sql. n

...................................................................................................................................................... A-36 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 9 .....................................................................................................................................................

Create an XML Query. For the Data Definition, locate the file warehouse_inventory.xsd. For the Data Source, locate the file warehouse_inventory.xml. Select the XML Query tool. Click in the painter area of the Data Model view. Specify the Data Defintion by clicking the Browse button and locating the file warehouse_inventory.xsd. Make sure the file type selected in the Open dialog box is XSD. Specify the Data Source by clicking the Browse button and locating the file warehouse_inventory.xml d Link the two data sources together so that your report will provide a general description of each warehouse along with the specific inventory for each location. Select the Data Link tool. Click and drag from WAREHOUSE_ID in the SQL query group to WAREHOUSE_ID1 in the XML query group. e Create both a Web and paper layout for this report. Select the Group Above style. Ensure that both groups are selected and display all fields. Use the Blue template for this report. Invoke the Report Wizard. Work through the pages of the Wizard making the selections as indicated. Click Finish to run the paper layout. f Save the report as p9q4.jsp. g Run the Web layout. c

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-37

Appendix A: Solutions .....................................................................................................................................................

5 Create a new report using a REF cursor query. a

In the Object Navigator, create a local package called RefCur defining a record and a REF cursor. The record has the following fields: Name last_name first_name salary

Definition employees.last_name%type employees.first_name%type employees.salary%type

Create a new report manually. Select the Program Units node in the Object Navigator and click the Create icon. In the New Program Unit dialog box, enter RefCur as the name for the package and choose Package Spec. Enter the code for the package specification. This code is available in p9q5a.txt. PACKAGE RefCur IS TYPE EmpRec IS RECORD ( last_name employees.last_name%TYPE, first_name employees.first_name%TYPE, salary employees.salary%TYPE); Type EmpCur IS REF CURSOR RETURN EmpRec; END; b In the Data Model, create a REF Cursor Query. The function opens the cursor for the following SELECT statement: SELECT last_name, first_name, salary FROM employees Select the Ref Cursor Query tool and click in the painter area. Define the following function or copy the code from the text file p9a5b.txt. function QR_1RefCurDS return RefCur.EmpCur is V_EmpCur RefCur.EmpCur; begin OPEN V_EmpCur FOR SELECT last_name, first_name, salary FROM employees; RETURN V_EmpCur; end;

...................................................................................................................................................... A-38 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 9 .....................................................................................................................................................

Open the Report Wizard to create the layout for the tabular report. Select all the fields and choose a template. Click Finish to run the paper layout. d Save the report as p9q5.jsp and close it. c

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-39

Appendix A: Solutions .....................................................................................................................................................

Practice Session Overview: Lesson 10 This practice session contains: • Creating a new report with summaries • Opening an existing report to add summary calculations to the columns • Creating a new report with ranking summary columns • Opening an existing report to add placeholders for the highest and lowest values in the report Introduction In your reports, you may need to include additional columns with summary functions—or even add summaries, such as break totals and report totals. This practice gives you the opportunity to create summaries and additional columns with functionality that cannot be achieved using SQL alone.

...................................................................................................................................................... A-40 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 10 .....................................................................................................................................................

Practice Session Solutions: Lesson 10 1 Create a new report using the Report Wizard. Create both a Web and paper layout

and select a Group Above report style. a Display the following data: Table orders

Column order_id

Definition

order_date line_item_id

dd-Mon-yyyy

order_items

product_id unit_price quantity line_total

Group G_order_ id G_line_ item_id

unit_price*quantity

Label Ord. No.

Width 4

Order Date No.

10 2

Product No. Price Quantity Total

5 4 4 4

Hint: Read through all the steps of this exercise before creating your report. You can use Query Builder to define the initial query for this report. You can then manually edit the query to modify the display of order_date using the TO_CHAR function: TO_CHAR(order_date, ‘dd-Mon-yyyy’). b Do you need a formula column to calculate the line_total? No, it is a much better solution to do the calculation directly in the query. Enter the formula unit_price*quantity into the query text and give it an alias name of line_total. You can also use the Query Builder to create expressions in the query. Select the table ORDER_ITEMS. Select Define Column. Click inside the Defined Columns box. Enter Line_Total. Click inside the Defined As box. Enter the expression unit_price*quantity. Click OK. c Create the following summaries. Can you use the Report Wizard to create the summaries? Because this is a single query report, you can use the Report Wizard. Column line_total line_total

Summary sum % Total

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-41

Appendix A: Solutions .....................................................................................................................................................

Use the Gray template and finish the Wizard. In the Template tab choose and apply the template. Finish and run the report. e In the Data Model, alter the position of columns in the G_ORDER_ID group so that ORDER_DATE is above ORDER_ID, and change the break order on ORDER_DATE so that it is sorted in descending order. Click on ORDER_DATE and drag it above ORDER_ID; be careful not to drag it outside the group. Open the Property Inspector for ORDER_DATE and change the Break Order property to Descending. f Run both the paper and Web layouts to test the report (do not reenter the Wizard). g Save the report as p10q1.rdf and close it. 2 Open report p9q3.jsp. a Create columns to show the percentage of the total per sales representative for PROD_TOTAL and CUST_TOTAL. Select Summary Column in the vertical toolbar and click inside the group G_PRODUCT. This creates a new summary. Open the Property Inspector for the new column and name it PCT_PROD. In the Summary section select the function % of Total. The Source is PROD_TOTAL. Reset at G_PRODUCT. Compute at G_NAME. Select Summary Column in the vertical toolbar and click inside the group G_CUSTOMER. This creates a new summary. Open the Property Inspector for the new column and name it PCT_CUST. In the Summary section select the function % of Total. The Source is CUST_TOTAL. Reset at G_CUSTOMER. Compute at G_NAME. b Update the layout. Make all the new summaries four characters wide. Reenter the Wizard to update the layout. Display the new columns and ensure each has a width of four. c Save the report as p10q2.jsp. d Run the Web layout. d

...................................................................................................................................................... A-42 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 10 .....................................................................................................................................................

3 Create a new report to list employees by department ranked by their salaries.

Create both a Web and paper layout and select the Group Left report style. You can import the query from p10q3.sql. a Display the following data. Table departments employees

Column department_name employee_id first_name last_name salary

Label Department ID First Name Last Name Salary

Width 10 2 6 6 8

Include a summary of the salary column in the report. b Use the Beige template and finish the Wizard. c Modify the report to list employees by salary, beginning with the highest paid in each department. Edit the query and add ORDER BY EMPLOYEES.SALARY DESC. d To display the ranking, add a column call RANK in the group G_EMPLOYEE_ID. Create a summary column in the group G_EMPLOYEE_ID. Name it Rank. Select the function Count. The Source is EMPLOYEE_ID. Reset at G_DEPARTMENT_NAME. e Use the Report Wizard to display the rank, giving it a width of 2. f Run the paper layout to test. g Save the report as p10q3.rdf. 4 Open report p10q1.rdf to display the highest and lowest orders at the end of the report. a In the Data Model, add the columns necessary to list the maximum order total and the corresponding ORDER_ID, as well as the minimum order total and the corresponding ORDER_ID.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-43

Appendix A: Solutions .....................................................................................................................................................

Hint: You need one formula column and two summaries and two placeholders. You can import the code for the formula column from the file p10q4.txt . At report level create two summary columns: Name CS_Max CS_Min

Function Maximum Minimum

Source SumLineTotalPerORDER_ID SumLineTotalPerORDER_ID

Reset At Report Report

Also at report level create two placeholder columns. Name them CP_Maxid and CP_Minid. This is where the ORDER_IDs of the highest and lowest orders will go. Create a formula column in the upper group and name it CF_GetIds. Return number 1 (although it does not really matter what you return if the formula column is a character type). The PL/SQL formula is the following: function CF_GetIdsFormula return Number is begin if :SumLine_TotalPerOrder_Id = :CS_Max then :CP_Maxid := :ORDER_ID; end if; if :SumLine_TotalPerOrder_Id = :CS_Min then :CP_Minid := :ORDER_ID; end if; return (1); end; Use the Report Wizard to add the new columns to the layout. Display the maximum order total, the maximum ORDER_ID, the minimum order total, and the minimum ORDER_ID. Display columns CS_Max, CS_Min, CP_Maxid , and CP_Minid. Give them more meaningful labels. c Run the paper layout to test. Scroll down to the last page to see the new columns. d Save the report as p10q4.rdf and close it. b

...................................................................................................................................................... A-44 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 11 .....................................................................................................................................................

Practice Session Overview: Lesson 11 This practice session contains: • Opening an existing report to add header and trailer pages • Distributing a report to various destinations • Modifying an existing report to add a repeating frame to the header section displaying order information. • Opening an existing report and adding flexible lines Introduction In this practice session, you use reports created in earlier practice sessions.The aim is to familiarize you with the different layout sections and regions of a report, and to use report bursting. You also add flexible lines to a frame.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-45

Appendix A: Solutions .....................................................................................................................................................

Practice Session Solutions: Lesson 11 1 Open report p10q1.rdf. Modify the report as follows:

Create a header section. Open the Paper Layout view of the Report Editor. Select Header Section from the horizontal toolbar or choose View > Layout Section > Header from the menu. Make sure that you are editing the Body region, not the Margin. Display the monitor.jpg file as fixed boilerplate in the Body region. Select Insert > Image from the menu to insert the file. Move and resize it. Add a rectangle boilerplate object. Give it fill color. Select the Rectangle tool from the vertical toolbar. Make the rectangle large enough to fit the image inside. Select Fill Color from the vertical toolbar. Place the image on top of the rectangle. Center the two objects. Select the rectangle and select Layout > Send To Back from the menu. Select the image, press the [Shift] key, and select the rectangle. Select Layout > Alignment from the menu. In the dialog box select Align to Each Other and then Align Center both horizontally and vertically. Click OK. Hint: Use the Layout option from the menu. Include today’s date in the top right portion of the margin. Select Edit Margin from the horizontal toolbar. Select Insert > Date and Time from the menu. Select Top-Right as the placement of the date field. Select any of the formats for the date. Click OK. b Add a report title in the body region. Use Arial bold as the font and 20 as the size. Click the Text tool from the vertical toolbar and click in the layout area. Enter the text and click outside the text object. While the text object is selected, choose font and size from the stylebar. a

...................................................................................................................................................... A-46 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 11 .....................................................................................................................................................

Add a trailer section that displays today’s date with the text “End of Report” on one line, and “Report run on: ” on the second line in the body region. Select a larger font. Hint: Copy and paste the date from the Margin Area of the of the header section of the report. Select Trailer Section from the horizontal toolbar, or Select View > Layout Section > Trailer from the menu. Make sure that you are editing the Body region. Click the text tool from the vertical toolbar and click in the layout area. Enter the text and click outside the text object. While the text object is selected, select font and size from the stylebar. Select Edit Margin from the horizontal toolbar. Now select Header Section. Select the f_date1 field and then select Edit > Copy from the menu. Select Trailer Section from the horizontal toolbar. Deselect Edit Margin. Select Edit > Paste from the menu and move the field to the desired place. d Save the report as p11q1.rdf. e Run the Paper Layout. Run the Web layout. What is different? The paper layout has the header and trailer sections, the Web layout does not. c

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-47

Appendix A: Solutions .....................................................................................................................................................

2 Open report p10q1.rdf.

Modify the report to add a header section that lists all order numbers, order dates, and order totals in the body region. Open the Paper Layout view of the Report Editor. Select Header Section from the horizontal toolbar, or choose View > Layout Section > Header from the menu. Make sure that you are editing the Body region. Select the Report Block tool from the vertical toolbar and click in the Layout area. In the Report Block Wizard, select a tabular layout. Select the group G_ORDER_ID and give a print direction down. Select the fields ORDER_ID, ORDER_DATE, and SumLinetotalPerOrder_Id. b Add “Executive Summary” as boilerplate text in the margin region of the header section. From the vertical toolbar, choose Edit Margin. Select the Text tool and create the boilerplate text. c Set the distribution for the header section to an HTMLCSS file. In the Object Navigator, open the Property Inspector for the Header Section. Select the Distribution property. In the Distribution Dialog box set the distribution ID, desname, and desformat fields. Make sure the output format is HTMLCSS. d Set the distibution for the main section to a PDF file. In the Object Navigator, open the Property Inspector for the Main Section. Select the Distribution property. In the Distribution Dialog box set the distribution id, desname, and desformat fields. Make sure the output format is PDF. e Run and test the report using the Distribute facility in Reports Builder. Select File > Distribute. f Look at the output generated for both file formats. Locate the files in your file system and double-click each of them to look at the output. g Save the report as p11q2.rdf and close it. a

...................................................................................................................................................... A-48 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 11 .....................................................................................................................................................

3 Open report p11q3.rdf. Modify the report to burst on repeating data, based on

warehouse ID, and distibute a PDF report for each warehouse. a In the Paper Layout view of the Report Editor, select the outermost repeating frame and delete it. Invoke the Paper Layout view of the Report Editor. Locate the repeating frame R_G_WAREHOUSE_ID in the Object Navigator. The repeating frame is now selected in the Paper Layout. With focus on the Paper Layout, delete the repeating frame. Be sure you do not delete the frame from the Object Navigator as this will delete all the objects within the frame as well. b Set the property for the main section of the report to burst on each warehouse ID. Select the Main Section node in the Object Navigator. Double-click the object icon to display the Property Inspector or select Tools > Property Inspector from the menu. Locate the Repeat On property and select G_WAREHOUSE_ID from the drop-down list. c Define the distribution for the report. Generate a PDF file for each warehouse, using the warehouse name as a unique identifier for each PDF file, such as rptBejiing.pdf. Select the Distribution property. In the Distribution Dialog box set the distribution ID, desname, and desformat fields. Use warehouse_name in the desname field, for example: rpt&.pdf. Make sure the output format is PDF. d Activate the distribution. Select File > Distibute from the menu. e Save the report as p11q3.rdf. f Look at the output generated for several of the warehouses. Locate the files in your file system and double-click several of them to look at the output. 4 Open report p11q4.rdf. a Use the Report Wizard to increase the width of PRODUCT_DESCRIPTION to 30. b Add a flexible line vertically between the PRODUCT_NAME and PRODUCT_DESCRIPTION fields. Select the line tool from the vertical toolbar. Create a line between the fields. If you want to ensure that the line is vertical, press the [Shift] key

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-49

Appendix A: Solutions .....................................................................................................................................................

while drawing. Make the line the same height as the PRODUCT_DESCRIPTION field using the Layout > Size Objects menu option. In the Property Inspector, set Line Stretch with Frame to R_G_PRODUCT_ID. c Run the Paper Layout to test. d Save the report as p11q4.rdf. 5 Open and run report p2q10.rdf. a Using the Paper Design view, compare the output with s11q5a.rdf from the \Soln directory. What is different? Change the report so that it looks like s11q5a.rdf. Both are master detail reports. But in s11q5a.rdf the groups are intermixed. b Fully expand the Paper Layout node for p2q10.rdf. Select the Layout Model node for p2q10.rdf. Then select Expand All in the Vertical toolbar. c Open the Paper Layout and move the window to the right. Make sure it doesn’t obscure the Object Navigator window. d Move the F_EMPLOYEE field down 2 inches. It should not be enclosed by any frame. Select Flex Off and Confine Off in the Horizontal toolbar. Click and drag the F_EMPLOYEE field down 2 inches. Notice where the field is being placed in the object hierarchy. e Resize the R_G_EMPLOYEE frame so that it only encloses the F_SALARY and F_JOB_ID fields. In the Object Navigator, select R_G_EMPLOYEE. In the Paper Layout, resize the frame. f Resize the M_G_EMPLOYEE_GRPFR frame so that it encloses the R_G_EMPLOYEE repeating frame. In the Object Navigator, select M_G_EMPLOYEE_GRPFR. In the Paper Layout, resize the frame. g Move the F_DEPARTMENT_NAME and F_DEPARTMENT_ID fields to the right. Click and drag the fields to the right. h Create a new repeating frame in the free space. Set the Line color attribute to No line. Select Repeating Frame form the Vertical toolbar. Click and drag an area

...................................................................................................................................................... A-50 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 11 .....................................................................................................................................................

in the space previously occupied by F_DEPARTMENT_ID and F_DEPARTMENT_NAME. Using the Line Color palette, set the line color to No Line. Note: Ensure that the new repeating frame R_1 is at the same level as M_G_EMPLOYEE_GRPFR. Use the Object Navigator to verify this. i Link the repeating frame to the G_EMPLOYEE group. Open the Property Inspector for the new repeating frame and set the Source property to G_EMPLOYEE. j Move the F_EMPLOYEE field inside the repeating frame R_1. k Save the report as p11q5a.rdf and run the paper layout. What happens? A run-time error occurs as the F_EMPLOYEE field is at the wrong hierarchy level, meaning not enclosed by the right parent object. l Fix the layout hierarchy error. Select F_EMPLOYEE, the from the menu select Layout > Bring to Front. The field is now enclosed by R_1. m Rearrange the column headers. Click and drag the column headers to match the column positions. n Run the paper layout. The two data groups are now intermixed. o Close the report. Save as p11q5a.rdf.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-51

Appendix A: Solutions .....................................................................................................................................................

Practice Session Overview: Lesson 12 This practice session contains: • Creating a break report with continuation pages displaying limited information • Modifying properties for object Web support Introduction Sometimes detail records get separated from their master header. You ensure that this does not happen. All paper layout objects support Web features. You will create a report that takes you to a PDF document with a simple click using a hyperlink.

...................................................................................................................................................... A-52 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 12 .....................................................................................................................................................

Practice Session Solutions: Lesson 12 1 Create a new break report showing each customer and the relevant orders.

Use the query p12_1.sql to create the report. In the Report Wizard use Import SQL Query. b Create a group above master detail report with all the customer data in the break group. Create a sum for the quantity. The break group should contain all the fields from the CUSTOMERS table. Create totals by using the Sum function on the quantity column. c Modify the width for the fields listed below: a

Column name street_address city state_province postal_code country_id credit_limit

Label Name: Address: City: State / Province: Zip: Country: Credit Limit:

Width 20 10 10 10 5 2 6

Resize the customer repeating frame to 3 inches high. In the Object Navigator, select the R_G_NAME group. Open the Paper Layout. Select Flex On and use the central handle on the bottom edge of the repeating frame to resize it to 3 inches high. e Run the paper layout. What do you see? Some of the customer master records print without their detail order information onthe same page. f Ensure that no customer data displays on a page without details. Open the Property Inspector for the repeating frame and set the Page Protect property to Yes. g Save the report as p12q1.rdf. h Run the paper layout again to test. d

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-53

Appendix A: Solutions .....................................................................................................................................................

2 Create a new tabular report.

Define the SQL query as: select last_name, first_name, hire_date from employees Display all fields. Choose the Wine template. b In the Margin region of the report’s Main Section, delete the company logo. Resize the margin to 1.75 inches. Insert the image dinner1.bmp. Create a rectangle around it and give it a solid fill. In the Paper Layout, select Edit Margin. Select the Your Company, Inc. logo and delete it. Select the margin rectangle. Using the central handle on the top edge, size the margin to 1.75 inches. Then from the menu, select Insert > Image and browse for dinner1.bmp. Click OK. From the vertical toolbar, select Rectangle. Click and drag a rectangle around the image. Give it a solid fill. From the menu, select Layout > Send to Back. c When you click on the image in HTML output you want to open a local .pdf file named mousse.pdf. Open the Property Inspector for the image and set the hyperlink property to mousse.pdf. d Save the report as p12q2.rdf and generate a file in paginated HTMLCSS format. Open the file in your browser and test the hyperlink. Save the report. Select File > Generate to File > Paginated HTMLCSS. Start your browser, open the file, and click on the image. a

...................................................................................................................................................... A-54 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 13 .....................................................................................................................................................

Practice Session Overview: Lesson 13 This practice session contains: • Modifying the printing of a mailing label report • Controling the number of records on a page • Adding a link file to a report • Ensuring that all details of a master are printed on the same page Introduction Modify a mailing label report to ensure you fill the printed page with label records. You then restrict the number of labels that are printed on each page. You add a link file to display a company logo by dynamically referencing a file at run time.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-55

Appendix A: Solutions .....................................................................................................................................................

Practice Session Solutions: Lesson 13 1 Open report p4q4.rdf.

Modify the layout so that the mailing labels are 2 inches wide and 1 inch high. Use the Layout option on the menu. Ensure that the customer names are printed in alphabetical order across the page. Open the Paper Layout view of the Report Editor and select the repeating frame. From the menu, select Layout > Size Objects. Select Custom for both width and height and enter the size in the fields. You can also drag and resize the repeating frame in the Paper Layout. Add an ORDER BY clause to the query. b Give each label a solid fill. Select Fill Color from the vertical toolbar and select a color. c Make the boilerplate object 1.8 inches wide and center it horizontally and vertically in the repeating frame. Select the boilerplate object in the Paper Layout and choose Layout > Size Objects from the menu. Select Custom for the width and No Change for the height. Enter the width in the field below. Select both the boilerplate object and the repeating frame and choose Layout > Alignment from the menu. Select Align to Each Other, and then Align Center, both horizontally and vertically. d Add spacing between the labels: .25 inch between each column of labels across the page; .5 inch between each row of labels down the page. Select the repeating frame and open its Property Inspector. Set Horiz. Space Between Frames to .25 and Vert. Space Between Space to .5. e Run the paper layout to test. Save the report as p13q1.rdf. 2 Continue with report p13q1.rdf. a Modify the report so that only six labels are printed on each page. Select the repeating frame and open its Property Inspector. Set Maximum Records per Page to 6. b Run the paper layout to test. Save the report as p13q2.rdf and close it. 3 Open p10q1.rdf. a On the operating system, copy the file summitlo.tif to a file logo.tif. Ask your instructor for help if needed. b Delete the logo in the margin area and create a file link in its place to display the contents of logo.tif. Select Edit Margin from the horizontal toolbar. Select the logo boilerplate and press [Delete]. Select the File Link tool from the vertical toolbar and a

...................................................................................................................................................... A-56 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 13 .....................................................................................................................................................

create an external boilerplate object in the margin region where the logo was. Set the following properties: Note: Do not include a file pathname. Property Name Source File Format Source Filename

Value B_logo Image logo.tif

Run the paper layout. d On the operating system, copy any other .tif file to logo.tif. e Rerun your report. f Save the report as p13q3.rdf. 4 Continue with report p13q3.rdf. a Ensure that all items of one order are printed on the same page. Select Edit Margin from the horizontal toolbar. Open the Property Inspector for the repeating frame R_G_ORDER_ID and set the property Page Protect to Yes. b Save the report as p13q4.rdf and close it. c

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-57

Appendix A: Solutions .....................................................................................................................................................

Practice Session Overview: Lesson 14 This practice session contains: • Adding dynamic content to an existing Web page to create a Reports JSP • Customizing a Web report using JSP tag attributes Introduction In this practice, you will create a Web report by adding dynamic report data to an existing HTML page. You will also customize the Web report by using JSP tag attributes.

...................................................................................................................................................... A-58 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 14 .....................................................................................................................................................

Practice Session Solutions: Lesson 14 1 Create a new Web report by adding dynamic content to an existing Web page.

Open the file mywebpage.htm in Reports Builder. b Define a data model using the Data Wizard. Import the query p14q1.sql. Use DEPARTMENT_ID and DEPARTMENT_NAME as the group fields. Include a sum of the salaries. Display the Data Model view of the Report Editor. Right-mouse-click in the Data Model painter area and select Data Wizard from the menu. Select SQL Query on the Data Source page. Import the query p14q1.sql. Copy department_id and department_name from the Available Fields area to the Group Fields area. Be sure both columns are at the same level (Level 1). Select the salary column and click Sum >. Click Finish to complete the Data Model Wizard. c Save the report as p14q1.jsp. d Create a report block and add it to the Reports JSP. The Web source code has a place holder for your report block. Choose the Group Left style and include a title “Salaries by Department”. Include all groups and display in the down direction. Display all fields. Use a label of Name for DEPARTMENT_NAME and Title for JOB_ID. Select the predefined Beige template. Display the Web Source view of the Report Editor by double-clicking on the object icon for the Web Source in the Object Navigator. Scroll through the code until you locate the placeholder “Insert your report block here”. With your cursor in that position, select Insert > Report Block from the menu. Select the style Group Left and enter Salaries by Department in the title field. Select the group G_department_id and click Down >. Select the group G_Employee and click Down >. Copy all the columns from the Available Fields area to the Displayed Fields area. On the labels page, make the appropriate changes. Select the Beige template. Click Finish. e Run the Web layout to test. Save the report. a

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-59

Appendix A: Solutions .....................................................................................................................................................

2 Customize report p14q1.jsp using JSP tag attributes.

Display the salary and summary values with an appropriate currency format. Display the Web Source view of the Report Editor. Locate the rw:field tag for F_SALARY and add the formatMask attribute: . Locate the rw:field tag for F_SumsalaryPerdepartment_id and add the same format mask attribute: Locate the rw:field tag for F_SumsalaryPerReport and apply the same formatMask attribute. b Run the Web layout to test. Did you remember to format the report summary? Save the report as p14q2.jsp. a

...................................................................................................................................................... A-60 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 15 .....................................................................................................................................................

Practice Session Overview: Lesson 15 This practice session contains: • Running a report using an XML definition • Applying an XML report definition to an existing .rdf file Introduction Using XML you can build fully functional reports or partial reports that can serve as a customization file. You will run an XML file and apply an XML definition to an existing report.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-61

Appendix A: Solutions .....................................................................................................................................................

Practice Session Solutions: Lesson 15 1 Run an XML report definition.

Open p15q1.xml using Wordpad. Examine the code. What type of report definition is this? What goes on in this file? It is a full XML report definition with both a fully functional data model and layout. It builds a two query group above report. It creates a data link between the two queries and a summary column in the master group. It uses the template rwbeige.tdf, but overrides the formatting for the F_SAL field through a formatting exception. b Execute p15q1.xml using the Run Paper Layout option in Reports Builder. Open p15q1.xml in Reports Builder and select Program > Run Paper Layout from the menu or use the Run Paper Layout icon from the toolbar. 2 Apply run-time customizations. a Open p15q2.rdf. Run the paper layout. b What type of report is this? Investigate the data model, the paper layout, and the report sections. It is a simple tabular report. There is nothing in the Header and Trailer sections. There are no summary fields, and no data formatting. c Open p15q2.xml. Examine the code. What goes on in this XML definition file? It has a data source that is different from what is in the p15q2.rdf report. The data is going to be formatted in the header section. In the main section it has a formatting specification for the SALARY column in the main section. d Apply p15q2.xml to p15q2.rdf using the RWRUN command and send the output to a PDF file. RWRUN report=p15q2.rdf customize=p15q2.xml destype=file desformat=PDF desname=myreport.pdf userid=/ @ e Examine the newly created report output. What do you see? a

...................................................................................................................................................... A-62 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 16 .....................................................................................................................................................

Practice Session Overview: Lesson 16 This practice session contains: • Modifying an existing report to use a bind parameter • Adding a dynamic list of values • Adding a lexical parameter • Adding a static list of values • Creating a customized parameter form Introduction In this practice session, you create bind and lexical parameters to restrict records in the report. You add lists of values to select valid values at run time. You then create your own default parameter form and customize it to include extra text and graphics.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-63

Appendix A: Solutions .....................................................................................................................................................

Practice Session Solutions: Lesson 16 1 Open report p13q3.rdf. a

Modify the data model and add the following columns from the CUSTOMERS table. Create a new group for the customer information above the G_ORDER_ID group.

Column cust_first_name ||’ ‘|| cust_last_name cust_email

Label Name Email

Width 30 20

Create a reference to a bind parameter P_CUSTOMER in the query to restrict orders to the parameter value you input at run time. Note how Reports creates the parameter automatically. Add the following to the WHERE clause of the query: AND customers.customer_id =:p_customer b Run and test the report with valid customer IDs. Choose any number in the range 101 to 170. c Save the report as p16q1.rdf. Do not close it. 2 Continue with report p16q1.rdf. Add a list of values to select any valid CUSTOMER_ID in the CUSTOMERS table that has an order inteh ORDERS table. Do not allow users to enter any other value. The list should display customer names, not CUSTOMER_ID numbers. Ensure that each customer name appear only once int he list, even if the customer has several orders. In the Object Navigator, expand the Data Model node and the User Parameter node. Open the Property Inspector for the P_CUSTOMER parameter and make sure that the datatype is Number. In the parameter Property Inspector, select List of Values. In the dialog box, choose Select Statement. Make sure that the Restrict List to Predetermined Values check box is selected. Select the Hide First Value check box. Enter the following statement in the Enter Query area: select unique c.customer_id, c.cust_first_name ||' '|| c.cust_last_name Name from customers c, orders o where c.customer_id = o.customer_id

...................................................................................................................................................... A-64 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 16 .....................................................................................................................................................

Save the report as p16q2.rdf. Do not close it for the moment. e Run the paper layout to test the report. Run the Web layout as well. 3 Open report p5q3.rdf. a Create a lexical parameter to enable users to order the data by different columns (or not at all) at run time. Include an ORDER BY clause in the query with a lexical reference to the parameter. Enter an initial value for the parameter to order by the employee’s last name. In the Object Navigator, expand the Data Model node. Double-click the User Parameter node to create a parameter. Select the parameter name P_1 and change it to P_ORDER. Open the parameter Property Inspector. Make sure that the datatype is Character and increase the width to 50. Enter an Initial Value: ORDER BY employees.last_name In the Data Model, open the query Property Inspector and add the following lexical reference to the end of the query: &P_ORDER b Add a static list of values. Code two or three alternative ORDER BY clauses. Allow any clause to be entered at run time. In the parameter Property Inspector select List of Values. In the dialog box choose Static Values and enter other ORDER BY clauses in the Value field; for example: ORDER BY employees.last_name ORDER BY employees.job_id For each value, remember to select Add>> to add the value to the list. Clear the Restrict List to Predetermined Values check box. c Run the paper layout to test. Select an ORDER BY clause from the list. Enter your own ORDER BY clause. Delete the initial parameter value. d Save the report as p16q3.rdf and close it. 4 Continue with report p16q2.rdf. a Create a default parameter form. Include a Title message but not a Hint message. Display the parameters CURRENCY and P_CUSTOMER. Select Tools > Parameter Form Builder from the menu. d

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-65

Appendix A: Solutions .....................................................................................................................................................

Enter your own Title message. Delete the Hint message. Select CURRENCY. P_CUSTOMER is already selected. b Use your imagination to customize the form in the Parameter Form editor. Make sure that the parameter P_CUSTOMER is placed in a prominent position for user entry. Add some graphics; modify colors and fill patterns. No formal solution. c Add a format mask to the field displaying line_total. Open the Property Inspector for F_LINE_TOTAL. In the Format Mask property, select one of the masks, such as $. d Run the paper layout to test. Provide a value for the currency symbol and select a customer from the list. e Save the report as p16q4.rdf and close it.

...................................................................................................................................................... A-66 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 17 .....................................................................................................................................................

Practice Session Overview: Lesson 17 This practice session contains: • Using the Graph Wizard to create a graph in an existing report • Altering the graph with the Graph Wizard • Creating graphs in an existing report that will be printed once for each record of a group • Creating a graph for a Web report • Customizing a graph for a Web report Introduction In this practice session you open existing reports to add graphs using the Graph Wizard. You reenter the Graph Wizard to change the characteristics of a graph. You will also add and customize a graph in a Web report.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-67

Appendix A: Solutions .....................................................................................................................................................

Practice Session Solutions: Lesson 17 1 Open report p17q1a.jsp.

Run the paper layout to become familiar with the report. b Rename SumCust_totalPersales_rep_id to Total_Sales (a shorter, more meaningful name looks better in a chart). Open the Property Inspector for SumCust_totalPersales_rep_id to change the name, or make the change in the Object Navigator. c Use the Graph Wizard to create a bar chart. Select Insert > Graph from the menu and enter the following properties on the Wizard pages: a

Graph Type Graph Subtype Position Category Value

Bar Bar at the end of the report sales_rep_id total_sales

Run the paper layout to test. Resize the graph in the Paper Layout, if necessary. e Save report as p17q1.jsp. 2 Reenter the Graph Wizard to change the characteristics of the graph created in the previous exercise. Make sure that the graph is selected; otherwise you will create a new graph. Select Graph Wizard from the right-mouse menu. a Give the graph the following characteristics: Enter the following properties in the Wizard pages: d

Title Graph Type Graph Subtype

Order Total per Sales Rep Horizontal Bar Bar

Click Apply and then OK. b Run the paper layout to test. c Save report to p17q2.jsp.

...................................................................................................................................................... A-68 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 17 .....................................................................................................................................................

3 Continue with report p17q2.jsp.

Run the Web layout. What do you see? There is no graph displayed in the Web output because you have not yet added it to the Web source. Also, an error message displayed: Cannot find cursor containing column "SumCust_totalPersales_rep_id" because the name of the summary column was changed in the Data Model but not in the Web source. b Update the Web source with the correct column name. Display the Web Source view. Locate the reference for . Edit the source attribute for this field to src="Total_Sales". c Run the Web layout again to test. d Add a graph to the Web source. Position the graph at the beginning of the report. Display the Web Source view. Scroll down the code and position your cursor just before the “Data Area Generated by Reports Developer”. Select Insert > Graph from the menu and enter the following properties on the Wizard pages:. a

Graph Type Graph Subtype Category Value Title

e

Bar Bar sales_rep_id total_sales Order Total per Sales Rep

On the layout page of the Graph Wizard, be sure to drag sales_rep_id from the Groups field to the Bars field. Run the Web layout to test. Save the report as p17q3.jsp.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-69

Appendix A: Solutions .....................................................................................................................................................

4 Open report p9q3.jsp. a

Use the Graph Wizard to create a pie chart for the data group G_PRODUCT. Invoke the Graph Wizard with the focus on the Paper Layout. Display the Paper Layout view of the Report Editor. Select Insert > Graph from the menu, and enter the following properties on the Wizard pages:

Graph Type Graph Subtype Data Group Category Value Graph Frequency

Pie Pie G_PRODUCT product_id prod_total Once per Name

On the layout page of the Graph Wizard, be sure to drag product_id from the Groups field to the Slices field. b In the Paper Layout, ensure that the graph is postitioned beneath the product group. If necessary, select the chart and drag it below the product group frame. You must enable Flex Mode. Do not forget to turn it off again when you have finished. c Run the paper layout and test the report. d Save the report as p17q4.jsp. 5 Continue with report p17q4.jsp. a Add a bar graph to the Web source for the data group G_PRODUCT. Use the same data group, category, and value settings as in the previous exercise. Hint: You want to insert the graph just before the termination of the rw:foreach tag for the group G_NAME. Display the Web Source view. Locate the tag for the G_NAME group. You can use Edit > Find and Replace from the menu to help you locate G_NAME. Scroll down in the code until you come to the corresponding termination tag . The indented nature of the code will help you identify the correct tag. With you cursor just before the tag, select Insert > Graph from the menu. Select a chart type of Bar. Use the table from the previous exercise to set the other values in the Wizard. b Run the Web layout to test. Ensure that a graph displays for each sales respresentative.

...................................................................................................................................................... A-70 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 17 .....................................................................................................................................................

Resize the graph to a width of 800 and a height of 450. Display the Web Source view. Locate the rw:graph tag. Add width and height settings to the attribute list: d Save the report as p17q5.jsp. Run the Web layout to test. c

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-71

Appendix A: Solutions .....................................................................................................................................................

Practice Session Overview: Lesson 18 This practice session contains: • Opening and changing an existing matrix report • Building a nested matrix report • Adding summaries to the nested matrix report • Displaying zeros for summary values and restricting the number of columns per page • Displaying more meaningful column information and rotating the text Introduction In this practice session, you use an existing matrix report to investigate the different methods of displaying the cell values and the implications. You use an existing SQL query to create a nested matrix report. You then add row- and column-level summaries to the report and display zeros for null values. If you have time, include the product names instead of the product IDs and rotate the names.

...................................................................................................................................................... A-72 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 18 .....................................................................................................................................................

Practice Session Solutions: Lesson 18 1 Open report p4q6.jsp.

How many repeating frames do you have? There are two repeating frames, R_G_NAME and R_G_PRODUCT_ID. b Change the properties of the cell object to display zero for a null value. Open the Property Inspector for F_SumTOTAL_VALUE and set Value if Null to 0. c Run the paper layout to test the report. All cells without a value now display 0. d Close the report without saving it. 2 Create a new report, importing the query p4q6.sql from your working directory. Select File > New > Report from the menu, or open a new report in the Object Navigator. Use the Report Wizard. a Select the following settings: a

Report Style Query Matrix Row Fields Matrix Column Fields Matrix Cell Fields Matrix Totals

Matrix Import Query p4q6.sql Level 1 - month Level 2 - name product_id Sum of total_value Sum of the cell field

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-73

Appendix A: Solutions .....................................................................................................................................................

b Change the labels and widths for the following fields: Field Label name Customer product_id Product month Month SumTotal_value Total All Summaries Total:

Width 10 5 4 5 5

Select a template and finish the Wizard. c Increase the width of the Main Section of the report to 17 inches. Open the Property Inspector for the Main Section of the paper layout. Set the Width property to 17. d Save the report as p18q2.jsp. Run the paper layout. e Which summaries were created? SumTOTAL_VALUEPerName SumTOTAL_VALUEPerPRODUCT_ID SumTOTAL_VALUEPerReport These are the row, the column, and the report summaries. f Use the Data Model view to create summaries for each month and for the level month and product. Create both summaries in the cross product group. Use the following settings: Property Name Product Order Function Source Reset At

Value SumMonth G_MONTH Sum SumTOTAL_VALUE G_MONTH

Property Name Product Order Function Source Reset At

Value SumMonthProduct G_MONTH G_PRODUCT_ID Sum SumTOTAL_VALUE G_PRODUCT_ID

...................................................................................................................................................... A-74 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 18 .....................................................................................................................................................

g

Use the Paper Layout view to create the fields. You must expand the appropriate repeating frame. Select and expand R_G_MONTH. You must enable Flex Mode. Do not forget to disable it again when you have finished. Select the Field tool from the vertical toolbar. Create a field below F_SumTOTAL_VALUE. Position the field in the R_G_MONTH and the R_G_PRODUCT_ID repeating frames. Set the following properties:

Property Name Source

Value F_SumMonthProduct SumMonthProduct

Select the Field tool from the vertical toolbar. Create a field below F_SumSumTOTAL_VALUEPerNAME. Position the field in the R_G_MONTH repeating frame but outside any other repeating frames. Set the following properties: Property Name Source

Value F_SumMonth SumMonth

Run the paper layout to test the report. i Save the report as p18q2.jsp. Do not close it. 3 Continue with the report from the previous exercise. a Add zeros for null values in the cells. Open the Property Inspector for F_SumTOTAL_VALUE and set Value if Null to 0. b Ensure that the report shows only three product columns on each page. Open the Property Inspector for R_G_PRODUCT_ID and set Maximum Records per Page to 3. c Format all numeric data to be right aligned. Use the style bar. d Add commas and two decimal places where appropriate. Use the style bar. e Run the paper layout to test the report. f Save the report as p18q3.jsp and close it. h

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-75

Appendix A: Solutions .....................................................................................................................................................

If You Have Time 4 Open the report as p18q3.jsp. a Make the necessary changes in the Data Model view to include the product name. Use the right mouse button to open the Query dialog box. The query should read: SELECT C.CUST_FIRST_NAME || ' ' || C.CUST_LAST_NAME Name, I.PRODUCT_ID, SUM(I.UNIT_PRICE * QUANTITY TOTAL_VALUE), TO_CHAR (O.ORDER_DATE, 'MM/RR') MONTH, P.PRODUCT_NAME PRODUCT FROM CUSTOMERS C, ORDER_ITEMS I, ORDERS O, PRODUCT_INFORMATION P WHERE I.PRODUCT_ID LIKE '1%' AND O.CUSTOMER_ID = C.CUSTOMER_ID AND I.ORDER_ID = O.ORDER_ID AND P.PRODUCT_ID = I.PRODUCT_ID GROUP BY C.CUST_FIRST_NAME || ' ' || C.CUST_LAST_NAME, I.PRODUCT_ID, TO_CHAR (O.ORDER_DATE, 'MM/RR'), P.PRODUCT_NAME

b

c

In the Paper Layout view, delete the product_id field and insert a boilerplate object to display the product name. Rotate this object by 90 degrees. Note: You may have to expand the R_G_PRODUCT_ID frame and the outermost frame. Watch out for frequency errors. Select F_PRODUCT_ID and press [Delete]. Select the Text tool from the vertical toolbar and create a text object in the place of the F_PRODUCT_ID field. Enter: &PRODUCT. Select the boilerplate object. Select the Rotate tool from the vertical toolbar. Press [Shift] and click the boilerplate object to rotate it by 90 degrees. Open the Property Inspector for the boilerplate object and set Vertical and Horizontal Elasticity to Variable. Open the Data Model view and ensure that the G_PRODUCT_ID group contains the PRODUCT column by dragging it in there. Run the paper layout. Save the report as p18q4.jsp.

...................................................................................................................................................... A-76 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 19 .....................................................................................................................................................

Practice Session Overview: Lesson 19 This practice session contains: • Creating a format trigger to conditionally display different layouts • Creating and use a report-level PL/SQL function • Creating and use an external PL/SQL library • Creating a PL/SQL group filter Introduction In this practice session, you create a format trigger to conditionally flag one of two paper layouts for the same data. You also conditionally flag some records in a report by creating and referencing a report-level (local) PL/SQL function. You then move the code into a new PL/SQL library and alter the reference to use the library function instead. If you have time, restrict the number of records processed in a report by creating your own group filter.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-77

Appendix A: Solutions .....................................................................................................................................................

Practice Session Solutions: Lesson 19 1 Create a new report, using p19q1.sql.

Display the records in two different layouts, showing different columns in each. b Create a Group Above layout using the following columns from the query: Create a new report, using the Report Wizard. Import the SQL Query from file p19q1.sql. Display the following fields. Select Labels and other properties as you wish. a

Group G_CUSTOMER_ID G_ORDER_ID

c

Column customer_id order_id order_date order_mode order_total

Create a Tabular layout using the following columns from the query: In the Paper Layout view, select the Report Block tool from the vertical toolbar. Display the following fields. Select Labels and other properties as you wish.

Column order_id order_date order_total d

Create a user parameter, user_type, that has two possible values: Sales Representative and Sales Clerk. In the Object Navigator, select the User Parameters node and click the Create icon. Open the Property Inspector for the parameter. Enter USER_TYPE in the Name property. Ensure the Datatype property is set to Character. Create a static list of values containing Sales Representative and Sales Clerk.

...................................................................................................................................................... A-78 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 19 .....................................................................................................................................................

e

Ensure that only the Group Above layout is displayed when the user type is Sales Representative, and that only the Tabular layout is displayed when the user type is Sales Clerk. Create a format trigger on the M_G_CUSTOMER_ID_GRPFR group frame in the first layout or use conditional formatting. function M_G_CUSTOMER_ID_GRPFRFormatTrigger return BOOLEAN is begin if:user_type = 'Sales Representative' then return(true); else return(false); end if; end;

Create a format trigger on the M_G_ORDER_ID_GRPFR1 group frame in the second layout with the reverse logic or use conditional formatting: function M_G_ID_GRPFR1FormatTrigger return BOOLEAN is begin if :user_type = 'Sales Clerk' then return(true); else return(false); end if; end;

f

To test the result, display the USER_TYPE parameter in the margin of the report. Display the Paper Layout view and select Edit Margin. Create a field to display the user parameter USER_TYPE. Make sure that you can enter a parameter value at run time. Select Tools > Parameter Form Builder from the menu and select USER_TYPE from the Parameter list. Click OK to create the Paper Parameter Form. Test the report by changing the User Type parameter on the Runtime Parameter Form. Save the report as p19q1.rdf.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-79

Appendix A: Solutions .....................................................................................................................................................

2 Continue with the previous report. a

Modify the report so that the Tabular (Sales Clerk) layout displays an extra column that indicates, with an asterisk, those orders that have an order status of 5. Create a new Program Unit, either from the Program menu or in the Object Navigator. Create a function called ASTERISK: function asterisk(ostatus number) return varchar2 is begin if ostatus = 5 then return('*'); else return(''); end if; end;

In the Data Model view, create a formula column in the detail group G_ORDER_ID. Open the Property Inspector and alter the following properties for the column CF_ASTERISK: Name

CF_ASTERISK

Datatype Character

Width 1

Click PL/SQL Formula to open the PL/SQL editor, and call the ASTERISK function from the formula. function CF_asteriskFormula return varchar2 is begin return(asterisk(:order_status)); end;

In the Paper Layout view, increase the width of the R_G_ORDER_ID repeating frame in the second layout to allow room for another field. Create a field inside the repeating frame, rename it F_CF_ASTERISK, and source it to CF_ASTERISK. b Remember to change user type to Sales Clerk during testing.

...................................................................................................................................................... A-80 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 19 .....................................................................................................................................................

Move the function code into a new PL/SQL library named p19lib.pll. In the Object Navigator, create a new library. Open the Library node and click and drag the ASTERISK function into the Program Unit node below the library. Save the PL/SQL library. Delete the report-level ASTERISK function. Attach the library to the report. Recompile the formula column, CF_ASTERISK. d Call the external function from your report instead of calling the report-level function. e Save the report to p19q2.rdf. 3 Continue with the previous report. a Modify the same layout to display additional spacing between every two records. In the Data Model view, create a summary column in the G_ORDER_ID group to keep a running total of records. c

Name

COUNT_ORD

Function COUNT

Source order_id

Reset At Report

In the Paper Layout view, increase the height of the repeating frame for the second layout. Remember to switch Flex mode on, so that you can increase the repeating frame and the group frame together. Open the repeating frame Property Inspector and change the Vertical Elasticity to Variable. Select the rectangle tool and click and drag an area inside the repeating frame, below the fields. Make sure the rectangle is transparent: no fill, no line. In the Object Navigator, rename the rectangle to B_space and create the following format trigger: function B_spaceFormatTrigger return boolean is begin if mod(:count_ord,2) = 0 then return(TRUE); else return(FALSE); end if; end; b

Save the report as p19q3.rdf and close it.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-81

Appendix A: Solutions .....................................................................................................................................................

If You Have Time 4 Open report p19q1.rdf. a Write your own group filter to restrict the number of customers displayed, depending on the value of a parameter that you enter at runtime. Hint: You need to create two parameters, to keep track of how many records have been processed, compared to the cutoff number of records required. Make sure that you can enter the cutoff parameter at run time. In the Object Navigator, open the Data Model node, and select the User Parameters node select the Create icon to create the first parameter. Select the Create icon to create a second parameter. Rename the parameters P_COUNT_CUST and P_CUTOFF. Give P_COUNT_CUST an initial value of 0. Open the G_CUSTOMER_ID group Property Inspector. Set Filter Type to PL/SQL Select PL/SQL Filter to open the PL/SQL editor. Enter the following code: function G_CUSTOMER_IDGroupFilter return boolean is begin :p_count_cust := :p_count_cust + 1; if :p_count_cust <= :p_cutoff then return(TRUE); else return(FALSE); end if; end;

Rebuild the Paper Parameter Form. b Test the report several times by changing the cutoff parameter at run time. c Save the report as p19q4.rdf.

...................................................................................................................................................... A-82 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 20 .....................................................................................................................................................

Practice Session Overview: Lesson 20 This practice session contains: • Building a report containing conditional highlighting • Modifying a Web report with conditional highlighting • Executing a detail report from a master report • Writing to a temporary table from a report Introduction In this practice session, you highlight values based on conditional PL/SQL code. You build a master report that enables you to call a detail report and generate a separate PDF file for each detail report. If you have time, you add report-level triggers to an existing report to write messages to a temporary table at various stages of report execution. Write PL/SQL functions as local PL/SQL (report-level), and reference the code in a condition within the relevant object trigger in the report.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-83

Appendix A: Solutions .....................................................................................................................................................

Practice Session Solutions: Lesson 20 1 Open report p9q3.jsp. a

Display the column CUST_TOTAL in the customer group as red, bold italics if its value is 10,000 or more. Hint: Check the Help system for the SRW.SET or use conditional formatting.

Mask Attribute SRW.SET_TEXT_COLOR SRW.SET_FONT_WEIGHT SRW.SET_FONT_STYLE

Suggested Value Red SRW.BOLD_WEIGHT SRW.ITALIC_STYLE

You can copy the code from the file p20q1a.txt. Open the PL/SQL editor and create a function to apply the format attributes. function FIELD_MASK return boolean is begin srw.set_text_color('red'); srw.set_font_weight(srw.bold_weight); srw.set_font_style(srw.italic_style); return(TRUE); end;

...................................................................................................................................................... A-84 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 20 .....................................................................................................................................................

b

Alter the pattern and color of those records where the customer total is 5,000 or less. Hint: Check the Help system for the SRW.SET or use conditional formatting.

Attribute

Suggested Values

Fill pattern Foreground fill color Background fill color

Crisscross, sandpaper Yellow Green

You can import the file p20q1b.txt. Open the PL/SQL editor and create a function to apply the format attributes. function RECORD_MASK return boolean is begin srw.set_foreground_fill_color('yellow'); srw.set_background_fill_color('green'); srw.set_fill_pattern('crisscross'); return(TRUE); end;

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-85

Appendix A: Solutions .....................................................................................................................................................

c

Write the necessary format triggers. Create a format trigger on the F_CUST_TOTAL field to call the function. function F_CUST_TOTALFormatTrigger return boolean is begin if :cust_total >= 10000 then return(field_mask); else return(TRUE); end if; end;

Create a format trigger on the R_G_CUSTOMER repeating frame to call the function. function R_G_CustomerFormatTrigger return boolean is begin if :cust_total <= 5000 then return(record_mask); else return (TRUE); end if; end;

Save the report as p20q1.jsp. e Run the paper layout. 2 Continue with the same report. a Run the Web layout for the report. Is there formatting on the CUST_TOTAL column? There is no formatting in the Web layout. The format triggers that you created in the previous exercise were applied to the paper layout objects only. b Modify the Web source so that the column CUST_TOTAL in the customer group as displays as red, bold italics if its value is 10,000 or more. Display the Web Source view. Locate the rw:field tag for F_CUST_TOTAL. Use Edit > Find and Replace and search for F_CUST_TOTAL. Add the formatMask attribute to the rw:field tag and set its value to F_CUST_TOTALFORMATTRIGGER: c Save the report as p20q2.jsp. d

...................................................................................................................................................... A-86 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 20 .....................................................................................................................................................

Run the Web layout to test. 3 Execute a detail report from a master report. a Create a simple tabular report called MGR_MASTER. The report should query last names and employee IDs for those employees who are managers. Display all fields. You can use the query: d

SELECT distinct manager.last_name Manager, manager.employee_id FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id Create a new tabular report using the Report Wizard. Enter the SQL query above. Display all fields and select labels and template as desired. b c

Save the report as mgr_master.rdf. Create another tabular report, the detail report, and name it EMP_RPT. The report should return department number, employee name, and salary for a specific manager. The manager_id will be passed to EMP_RPT by MGR_MASTER. Crete a new tabular report using the Report Wizard. Define the SQL query: SELECT department_id, first_name ||' '|| last_name Employee, salary FROM employees WHERE manager_id = :mgr_no

Display all fields and select labels and template as desired. d Save the report as emp_rpt.rdf and close it.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-87

Appendix A: Solutions .....................................................................................................................................................

e

Write the code to execute the detail report. For each manager returned, MGR_MASTER should invoke the detail report, EMP_RPT, pass it the manager’s employee_id, and produce a report in PDF format containing information about the employees working for that manager. Use the manager’s employee_id to create an output filename for each manager: MGR_.PDF. Hint: You can write the code in the group filter. Open the Property Inspector for the group G_MANAGER. Set the Filter Type property to PL/SQL. Select the PL/SQL Filter property to open the PL/SQL editor. Create the following function: function G_ManagerGroupFilter return boolean is begin SRW.RUN_REPORT ('report=emp_rpt.rdf batch=yes destype=file desformat=PDF desname='||'MGR_'||:employee_id||'.pdf mgr_no='||to_char(:employee_id)); return (TRUE); end;

Compile the PL/SQL code and close the window. g Run the paper layout for MGR_MASTER to test. Access the file system and locate the output for the detail reports. View two or three of the output files. h Save mgr_master.rdf and close it. f

...................................................................................................................................................... A-88 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 20 .....................................................................................................................................................

If You Have Time 4 Open report p10q1.rdf In this report, you use report triggers to create a temporary table at the start of report execution, and you insert rows into the table at various stages of the execution. a Write a trigger that creates a temporary table to hold the date and userid each time the report is run. The table should contain three columns: one date column, one character column of width 10, and one character column of width 80. CREATE TABLE RUNREPORT (DATE_RUN DATE, USER_RUN VARCHAR2(10), COMMENTS VARCHAR2(80))

In the Object Navigator, expand the Report Triggers node, and double-click the Before Report PL/SQL icon to open the PL/SQL editor. Create the following function: function BeforeReport return boolean is begin srw.do_sql('CREATE TABLE RUNREPORT (DATE_RUN DATE, USER_RUN VARCHAR2(10), COMMENTS VARCHAR2(80))'); return(true); exception when srw.do_sql_failure then null; end;

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-89

Appendix A: Solutions .....................................................................................................................................................

b

The same trigger should also insert the current date, userid, and “Starting Report” into this table. INSERT INTO RUNREPORT (DATE_RUN, USER_RUN, COMMENTS) VALUES (SYSDATE, USER, ' Starting Report')

Hint: Think about how you code the single quotes. Add the following code: function BeforeReport return boolean is begin srw.do_sql('CREATE TABLE RUNREPORT (DATE_RUN DATE, USER_RUN VARCHAR2(10), COMMENTS VARCHAR2(80))'); srw.do_sql('INSERT INTO RUNREPORT (DATE_RUN, USER_RUN, COMMENTS) VALUES (SYSDATE, USER, ''Starting Report'')'); srw.do_sql(‘COMMIT’); return(TRUE); end;

Compile the PL/SQL code and close the window. d Save the report as p20q4.jsp. e Run the paper layout. f Using SQL*Plus, verify that your table exists and was populated successfully. g Log on to SQL*Plus (in Microsoft Windows, select the SQL*Plus icon in the Oracle group) using the same userid as you used for Reports. h Display all records in the RUNREPORT table. c

SQL> col comments format a40 SQL> SELECT * FROM RUNREPORT;

...................................................................................................................................................... A-90 Oracle9i Reports Developer: Build Reports

Practice Session Solutions: Lesson 20 .....................................................................................................................................................

i

Try running the report more than once. When you run the report a second time, the table already exists and Reports Builder raises an exception. Make the necessary change to your code so that the report runs even when the table exists. You need two PL/SQL blocks so that, if the table already exists, the record is inserted after the exception has been handled. Add the following code: function BeforeReport return boolean is begin begin srw.do_sql('CREATE TABLE RUNREPORT (DATE_RUN DATE, USER_RUN VARCHAR2(10), COMMENTS VARCHAR2(80))'); exception when srw.do_sql_failure then null; end; begin srw.do_sql('INSERT INTO RUNREPORT (DATE_RUN, USER_RUN, COMMENTS) VALUES (SYSDATE, USER, '' Starting Report '')'); srw.do_sql(‘COMMIT’); return(TRUE); end; end;

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-91

Appendix A: Solutions .....................................................................................................................................................

j

Write a trigger that inserts a record with the comment “Printing another page” whenever it begins a new page. In the Object Navigator, expand the Report Triggers node, and doubleclick the Between Pages PL/SQL icon to open the PL/SQL editor. Or, if the editor is already open, change the Name to Between Pages. Create the following function. function BetweenPage return boolean is begin srw.do_sql('INSERT INTO RUNREPORT (DATE_RUN, USER_RUN, COMMENTS) VALUES (SYSDATE, USER, ''Printing another page'' )'); srw.do_sql(‘COMMIT’); return(TRUE); end;

k

Write a trigger that inserts a record with the comment “Report completed” when the report finishes. In the Object Navigator, expand the Report Triggers node and doubleclick the After Report PL/SQL icon to open the PL/SQL editor. Or, if the editor is already open, change the Name to After Report. Report the following function: function AfterReport return boolean is begin srw.do_sql('INSERT INTO RUNREPORT (DATE_RUN, USER_RUN, COMMENTS) VALUES (SYSDATE, USER, ''Report Completed'')'); srw.do_sql(‘COMMIT’); return(TRUE); end;

l

Save the report as s20q4l.jsp. Generate the output to a PDF file. Verify the results in SQL*Plus.

...................................................................................................................................................... A-92 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 21 .....................................................................................................................................................

Practice Session Overview: Lesson 21 This practice session contains: • Constructing a URL to run a report on the Web • Creating an entry in the key map file Introduction You publish your reports using Oracle9iAS Reports Services in the Oracle9i Application Server. This practice session consists of paper-based questions to test your understanding of Oracle9iAS Reports Services. Instructor Note Because Oracle9iAS is not part of the classroom setup environment, students will not be able to actually deploy reports on the Web. However, encourage students to work with the scenario in the practice and become familiar with the structure of the URL for deploying reports on the Web, as well as creating entries in the key map file to simplify the command line arguments.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-93

Appendix A: Solutions .....................................................................................................................................................

Practice Session Solutions: Lesson 21 1 You created a report called SalesByDept.jsp and need to deploy it on the

Web using a URL. Component Web server domain name port alias for the directory path

2

3

4 5

Description neptune world 8888 salesdata

The report should be executed by repserver4, and the output should be in HTML format and go directly to Oracle9iAS Reports cache. Access the data by connecting to the Sales database using a username of scott, a password of tiger, and salesdb as the database connect string. Construct the URL to run the report using the Reports servlet. http://neptune.world.com:8888/salesdata/ rwservlet?report=salesbydept.jsp&server=repserver4& desformat=html&destype=cache&userid=scott/tiger@salesdb So that you do not have to enter such a long URL each time you run the SalesByDept report, create a key entry for the key map file. a Name the key sales. You can also restrict the data in the report by passing it a value for the runtime parameter p_deptno. Be sure to include the parameter in the map file entry. sales: report=salesbydept.jsp server=repserver4 desformat=html destype=cache userid=scott/ tiger@salesdb p_deptno=%1 b Reconstruct the URL to run the report using the key. Pass in a value of 70 for p_deptno. http://neptune.world.com:8888/salesdata/ rwservlet?sales+70 In addition to using a URL, what are the other methods for requesting a report? RWCLIENT command, Oracle9iAS Portal, SRW.RUN_REPORT, and the Event-Driven Publishing API What is the JSP runtime architecture used for? The JSP runtime architecture runs the Web layout. Which two facilities can be used to monitor Oracle9iAS Reports Services? Oracle Enterprise Manager and Reports Queue Manager

...................................................................................................................................................... A-94 Oracle9i Reports Developer: Build Reports

Practice Session Overview: Lesson 22 .....................................................................................................................................................

Practice Session Overview: Lesson 22 This practice session contains: • Generating and interpreting report trace information • Modifying a report to make it more efficient Introduction Report performance is always an issue. Reports Builder offers a few tools that allow you to measure preformance. In this practice you will use them and interpret the results.

...................................................................................................................................................... Oracle9i Reports Developer: Build Reports A-95

Appendix A: Solutions .....................................................................................................................................................

Practice Session Solutions: Lesson 22 1 Name four main areas to investigate when tuning reports.

The Data Model, the Paper Layout, Web Layout and JSP report definitions, and run-time arguments. 2 What is the name of the font mapping file? uifont.ali 3 Generate and interpret Reports Profiler Statistics. a Open report p19q4.rdf. From the menu select Program > Tracing. b In the Runtime Trace Settings dialog box specify p14q4.txt as the trace file and check the Profile Messages and Timestamp checkboxes. c Select OK to close the dialog box. d Run the paper layout. Paginate through the report in the Paper Design view. e Open p19q4.txt and look at the results. Where does this report spend time? The time is spent on the layout and formatting of the report. f Repeat the same steps for report p22q2b.rdf. Specify p22q2.txt as the trace file. Compare the result. How do you explain this? The statistics for p22q2.rdf also show that time is spend formatting the report. The figures are a bit higher because of the dynamic customized formatting of the F_ORDER_MODE and F_ORDER_TOTAL fields. There is also a group filter on G_ORDER_ID. The predefined ‘Last’ filter is inefficient as it causes all records to be retrieved from the database. 4 Make a suggestion to improve performance. a Open the report p12q1.rdf and generate Reports Profiler Statistics. Look at the time statistics. b Examine the Data Model. What could you possibly do to make the report run faster? Set the Break Order property for all columns except the NAME column to None, thus avoiding forcing (unnecessary) extra sorting. c Implement your change and regenerate the time statistics. Did things improve?

...................................................................................................................................................... A-96 Oracle9i Reports Developer: Build Reports

B Table Descriptions

Table Descriptions Overall Description The sample company portrayed by the Oracle9i Sample Schemas operates worldwide to fill orders for several different products. The company has several divisions: • The Human Resources division tracks information on the company’s employees and facilities. • The Order Entry division tracks product inventories and sales of the company’s products through various channels. • The Product Media division maintains descriptions and detailed information on each product sold by the company. • The Shipping division manages the shipping of products to customer. Each of these divisions is represented by a schema. In this course, the user has access to the objects in all of these schemas.

Oracle9i Reports Developer: Build Reports B-2

Table Descriptions Overall Description (continued)

HR

Human Resources

OE

Order Entry

QS

PM

Queued Shipping

Product Media

The Oracle9i Sample Schemas are a set of interlinked schemas. This set of schemas is aimed at providing a layered approach to complexity: • A simple schema (Human Resources, HR) for introducing basic topics. • A second schema (Order Entry, OE) for dealing with matters of intermediate complexity. A multitude of data types is available in this schema. • The Online Catalog (OC) subschema is a collection of object-relational database objects built inside the OE schema. • A schema dedicated to multimedia data types (Product Media, PM) • A set of schemas gathered under the main schema name QS (Queued Shipping) to demonstrate Oracle Advanced Queuing capabilities.

Oracle9i Reports Developer: Build Reports B-3

Table Descriptions Human Resources (HR) In the company’s human resource records, each employee has a unique identification number, email address, job identification number, salary, and manager. Some employees earn a commission in addition to their salary, which is also tracked. When an employee switches jobs, the company records the start date and end date of the former job, the job identification number, and department. The company also tracks information about jobs within the organization. Each job has an identification number, job title, and a minimum and maximum salary range for the job. Some employees have been with the company for a long time and have held different jobs within the company. When an employee switches jobs, the company records the start date and end date of the former job, the job identification number, and the department. The sample company is regionally diverse, so it tracks the locations of not only its warehouses but also of its departments. Each of the company’s employees is assigned to a department. Each department is identified by a unique department code and a short name. Each department is associated with one location. Each location has a full address that includes the street address, postal code, city, state or province, and country code. For each country where it has facilities, the company records the country name, currency symbol, currency name and the region in which the county resides geographically.

Oracle9i Reports Developer: Build Reports B-4

Table Descriptions Human Resources (HR)

Oracle9i Reports Developer: Build Reports B-5

Table Descriptions Human Resources (HR) Row Counts SQL> select count(*) from countries; COUNT(*) ---------25 SQL> select count(*) from departments; COUNT(*) ---------27 SQL> select count(*) from employees; COUNT(*) ---------107 SQL> select count(*) from jobs; COUNT(*) ---------19 SQL> select count(*) from job_history; COUNT(*) ---------10 SQL> select count(*) from locations; COUNT(*) ---------23 SQL> select count(*) from regions; COUNT(*) ---------4

Oracle9i Reports Developer: Build Reports B-6

Table Descriptions Order Entry (OE) The company sells several categories of products, including computer hardware and software, music, clothing, and tools. The company maintains product information that includes product identification numbers, the category into which the product falls, the weight group (for shipping purposes), the warranty period if applicable, the supplier, the status of the product, a list price, a minimum price at which a product will be sold, and a URL address for manufacturer information. Inventory information is also recorded for all products, including the warehouse where the product is available and the quantity on hand. Because products are sold worldwide, the company maintains the names of the products and their descriptions in several different languages. The company maintains warehouses in several locations to facilitate filling customer orders. Each warehouse has a warehouse identification number, name, and location identification number. Customer information is tracked in some detail. Each customer is assigned an identification number. Customer records include name, street address, city or province, country, phone numbers (up to five phone numbers for each customer), and postal code. Some customers order through the Internet, so e-mail addresses are also recorded. Because of language differences among customers, the company records the NLS language and territory of each customer. The company places a credit limit on its customers to limit the amount they can purchase at one time. Some customers have account managers, whom we monitor. We keep track of a customer's phone numbers. In this day, we never know how many phone numbers a customer might have, but we try to keep track of all of them. Because of the language differences of our customers, we identify the language and territory of each customer. When a customer places an order, the company tracks the date of the order, the mode of the order, status, shipping mode, total amount of the order, and the sales representative who helped place the order. This may be the same individual as the account manager for a customer, it may be different, or, in the case of an order over the Internet, the sales representative is not recorded. In addition to the order information, we also track the number of items ordered, the unit price, and the products ordered. For each country in which it does business, the company records the country name, currency symbol, currency name, and the region in which the county resides geographically. This data is useful for tracking customers living in different geographic regions around the world. Product Media (PM) The company stores multimedia and print information about its products in the database. Examples of such information are: • Promotional audio and video clips • Product images and thumbnails for web publishing • Press release texts • Print media ads • Other promotion texts and translations Oracle9i Reports Developer: Build Reports B-7

Table Descriptions Order Entry (OE) and Product Media (PM)

Oracle9i Reports Developer: Build Reports B-8

Table Descriptions Order Entry (OE) and Product Media (PMP Row Counts SQL> select count(*) from customers; COUNT(*) ---------319 SQL> select count(*) from inventories; COUNT(*) ---------1112 SQL> select count(*) from orders; COUNT(*) ---------105 SQL> select count(*) from order_items; COUNT(*) ---------665 SQL> select count(*) from product_descriptions; COUNT(*) ---------8640 SQL> select count(*) from product_information; COUNT(*) ---------288 SQL> select count(*) from warehouses; COUNT(*) ---------9 SQL> select count(*) from online_media; COUNT(*) ---------9 SQL> select count(*) from print_media; COUNT(*) ---------4 Oracle9i Reports Developer: Build Reports B-9

Table Descriptions

Oracle9i Reports Developer: Build Reports B-10

Table Descriptions Queued Shipping (QS) The sample company has decided to test the use of messaging to manage its proposed B2B applications. The plan calls for a small test that will allow a user from outside the firewall to place an order and track its status. The order must be booked into the main system. Then, depending on the location of the customer, the order is routed to the nearest region for shipping. Eventually, the company intends to expand beyond its current in-house distribution system to a system that will allow other businesses to provide the shipping. Therefore, the messages sent between the businesses must also travel over HTTP and be in a self-contained format. XML is the perfect format for the message, and both the Advanced Queueing Servlet and Oracle Internet Directory provide the appropriate routing between the queues. After the orders are either shipped or back ordered, a message needs to be sent back to appropriate employees to inform them of the order's status and to initiate the billing cycle. It is critical that the message be delivered only once and that there be a system for tracking and reviewing messages to facilitate resolution of any discrepancies with the order. For the purpose of this test application, the company utilizes a single database server and a single application server. The application provides a mechanism for examining the XML messages as well as looking at the queues. To demonstrate connectivity from outside the firewall, both the generation of a new order and customer service reporting are performed using queues. The new order application directly enqueues a queue, while the customer service queries require XML messaging to dequeue a queue. The users associated with this application are: • QS (Queue Shipping) • QS_ES (Eastern Shipping) • QS_WS (Western Shipping) • QS_OS (Overseas Shipping) • QS_CB (Customer Billing) • QS_CS (Customer Service) • QS_ADM (Administration), and • QS_CBADM (Customer Billing Administration)

Oracle9i Reports Developer: Build Reports B-11

Table Descriptions

Oracle9i Reports Developer: Build Reports B-12

Glossary

Glossary .....................................................................................................................................................

A API (Application Program Interface) A language and message format used by an application program to communicate with the operating system or some other control program such as a database management system or communications protocol. C check box An interface element, appearing as a small square, that a user can toggle on or off. column (1) A vertical space in a database table that represents a particular domain of data. A column has a column name (e.g., ENAME) and a specific data type (e.g., CHAR). For example, in a table of employee information, all of the employees' names would constitute one column. A record group column represents a database column. (2} A data model object created automatically for each column expression in a query's SELECT list, or created manually to perform summaries, formulas, or act as a placeholder. CORBA (Common Object Request Broker Architecture) A languageindependent distributed object model specified by the Object Management Group. D data model A relational model that defines what data should be fetched from the database, what values should be computed, and how data should be

ordered in a report. Reports Builder objects that define the data model are queries, groups, columns, parameters, and links. Data Model view Displays a structural representation of the data in a report. The objects do not appear in the report output, but the structure determines the layout style, and the data objects provide the values that appear in the layout objects. database (1} A set of dictionary tables and user tables that are treated as a unit. (2) (Oracle Express) A single file (possibly accompanied by extension files) that contains objects that organize, store, and manipulate data. In Express, examples of such objects are variables, dimensions, formulas, models, and programs. data source A source for data returned by a query, including database objects such as tables, views, synonyms, snapshots, and queries stored as views. dialog box A partial screen or window that prompts you to enter information necessary to complete an operation. E editor A work area in which you perform a specific set of tasks, such as creating a program unit or designing an application. EJB (Enterprise Java Beans) In Java, a component architecture for the development and deployment of objectoriented, distributed, enterprise-level applications.

..................................................................................................................................................... Glossary-2 Oracle9i Reports Developer: Build Reports

Glossary .....................................................................................................................................................

F field (1) An interface element in which you enter, edit, or delete data. (2) A layout object that defines how the data for a specific query column appears. H HTML (Hypertext Markup Language) Acronym for Hypertext Markup Language. A tag-based ASCII language used to specify the content and links to other documents on Web servers on the Internet. End users with Web browsers view HTML documents and follow links to display other documents. I icon A graphic representation of a window or tool. IIOP (Internet Inter-ORB Protocol) A protocol used for communication between Common Object Request Broker Architecture (CORBA) object request brokers. image A bitmapped object that can be stored and loaded into an application. The client cannot modify an imported image. intranet An internal TCP/IP network, access to which is restricted (via a firewall) to individuals inside the company or organization. An intranet provides similar services within an organization to those provided by the Internet, but is not necessarily connected to the Internet. A common example of an intranet is when a company sets up one

or more Web servers on an internal network for distribution of information or applications within the company.

J Java A computer language that supports programming for the Internet in the form of platform-independent "applets." J2EE (Java 2 Platform, Enterprise Edition) An environment for developing and deploying enterprise applications in Java consisting of a set of services, application programming interfaces, and protocols that provide for developing multitiered, Web-based applications. JDBC (Java Database Connectivity) An industry standard for database and Java platform connectivity providing a call-level API for SQL-based database access. JSP (JavaServer Page) An extension to the servlet functionality that enables a simple programmatic interface to Web pages. JSPs are HTML pages with special tags and embedded Java code that is executed on the Web or application server providing dynamic functionality to HTML pages. JSPs are actually compiled into servlets when first requested and run in the server's JVM.

..................................................................................................................................................... Oracle9i Reports Developer: Build Reports Glossary-3

Glossary .....................................................................................................................................................

L layout The area of an editor in which you can create, modify, position, or delete objects. O object (1) An item that can be placed on the layout. The following are examples of objects: rectangle, line, ellipse, arc, polygon, polyline, rounded rectangle, freehand, graph, text, symbol, and text field. (2) In Oracle8, an instance of an object type. An object can be a row in an object table, or the portion of a row contained in a column object in a relational table. Oracle9i Application Server The Oracle9i Application Server (Oracle9iAS) is a strategic platform for network application deployment. By moving application logic to application servers and deploying network clients, organizations can realize substantial savings through reduced complexity, better manageability, and simplified development and deployment. The Oracle9iAS provides the only businesscritical platform that offers easy database web publishing and complete legacy integration while transition from traditional client-server to network application architectures. Oracle9i Developer Suite Oracle9i Developer Suite (Oracle9iDS) combines leading Oracle application development and business intelligence tools into a single, integrated product. Built on Internet standards such as Java and XML, the suite provides a complete and

highly productive development environment for building applications for Oracle9i Application Server and the Oracle9i Database. An alternate name for the top directory in the Oracle directory hierarchy on some directory-based operating systems. An environment variable that indicates the root directory of Oracle products. Oracle9iAS Portal Oracle9iAS Portal is an HTML-based development tool for building scalable, secure, extensible HTML applications and Web sites. Oracle9iAS Reports Services uses Oracle9iAS Portal to control end user access to reports published on the Web by storing information about report requests, the secured server, and any Oracle9iAS Reports Services printer used to print report output. Oracle9iDS Reports Builder (rwbuilder) Creates, develops, and maintains report definitions. Oracle9iAS Reports Services The runtime environment for Reports Developer applications. Oracle9iAS Reports Services executes, distributes, and publishes your reports for enterprise wide reporting. Using Reports Services to deploy your reports results in gains of flexibility, time savings, and processing capacity. Oracle9iAS Reports Servlet An interface between a Java-based Web server and Oracle9iAS Reports Runtime, enabling you to run reports dynamically from your Web browser.

..................................................................................................................................................... Glossary-4 Oracle9i Reports Developer: Build Reports

Glossary .....................................................................................................................................................

P Paper Design view Displays output for paper reports and allows you to make many commonly required, simple modifications to the layout, such as spacing, formatting fields,color, and editing text, without having to open the Paper Layout view. Paper Layout view Displays the layout objects in a paper report and allows you to make many modifications to any layout object. All layout objects have properties that you can modify using the Property Inspector. The hierarchy of the layout objects is determined by the data model. PDF (Portable Document Format) A file format (native for Adobe Acrobat) for representing documents in a manner that is independent of the original application software, hardware, and operating system used to create the documents. A PDF file can describe documents containing any combination of text, graphics, and images in a deviceindependent and resolution independent format. PL/SQL Oracle's proprietary extension to the SQL language. Adds procedural and other constructs to SQL that make it suitable for writing applications. Q query A SQL SELECT statement that specifies the data you want to retrieve from one or more tables or views of a database.

R RDF file A file that contains a single report definition in binary format. RDF files are used to both run and edit reports. RTF (Rich Text Format) A Microsoft standard for encoding formatted text and graphics. S SELECT statement A SQL statement that specifies which rows and columns to fetch from one or more tables or views. SOAP (Simple Object Access Protocol) A message-based protocol based on XML for accessing services on the Web. SQL A standard interface for storing and retrieving information in a relational database. SQL is an acronym for Structured Query Language. SQL file A file that contains a query stored in text (e.g., ASCII or EBCDIC) format. SQL script A file containing SQL statements that you can run to perform database administration quickly and easily. Several SQL scripts are shipped with Oracle products. SQL statement A SQL instruction to Oracle. A SELECT statement is one type of SQL statement. style sheet HTML extensions that provide powerful formatting flexibility in HTML documents. To view an HTML document that takes advantage of style sheets, display it in a browser that supports style sheets.

..................................................................................................................................................... Oracle9i Reports Developer: Build Reports Glossary-5

Glossary .....................................................................................................................................................

syntax The orderly system by which commands, qualifiers, and parameters are combined to form valid command strings.

T table A named collection of related information, stored in a relational database or server, in a two-dimensional grid that is made up of rows and columns. tabular A default layout displaying labels at the top of the page and rows of data underneath the labels. template A skeleton definition containing common style and standards, and can include graphics. A template provides a standard format to enable quick and easy development of professional standard look-and-feel reports. toolbar A collection of iconic buttons that perform product commands. Usually aligned horizontally along the top, or vertically down the side of a window. tool palette A collection of tools. U UDDI (Universal Description, Discovery, and Integration) An industry initiative for a universal business registry (catalog) of Web services.

URL (Uniform Resource Locator) A URL, a form of URI, is a compact string representation of the location for a resource that is available through the Internet. It is also the text string format clients use to encode requests to Oracle9iAS. W Web browser A program that end users utilize to read HTML documents and programs stored on a computer (serviced by a Web server). Web server A server process (HTTP daemon) running at a Web site that sends out Web pages in response to HTTP requests from remote Web browsers. Web source view Displays the HTML / JSP source for a report. You can use this view to add dynamic content to a Web page using the Report Block Wizard and the Graph Wizard. Experienced Java developers can edit the Web source directly in this view. window A rectangular area of the desktop that contains an application. Each window has an area where you can interact with the application. Windows can be opened, resized, moved, reduced to an icon, or enlarged to fill the entire desktop. wizards Provide an easy step-by-step interface for commonly performed tasks. The wizards in Reports Builder are:

..................................................................................................................................................... Glossary-6 Oracle9i Reports Developer: Build Reports

Glossary .....................................................................................................................................................

Report Wizard: Guides you through the steps to create a basic paper or Web report. Each page of the wizard asks you for information to help you create your initial report. Data Wizard: Helps you quickly define or modify a query for a multiquery data models. Graph Wizard: Adds a variety of charts and graphs, including true 3-dimensional graphs. Implemented in Reports Builder with the Oracle BI graph bean. Report Block Wizard: Enables you to add data to a static HTML page. WSDL (Web Services Description Language) A protocol for a Web service to describe its capabilities.

X XML Extensible Markup Language: A metalanguage using SGML to define and structure data. Reports Builder supports XML output to enable Web publishing as well as electronic data exchange with third-party applications. You can also use XML to build report definitions that can be merged with other report definitions at run time or can be run separately.

..................................................................................................................................................... Oracle9i Reports Developer: Build Reports Glossary-7

Glossary .....................................................................................................................................................

..................................................................................................................................................... Glossary-8 Oracle9i Reports Developer: Build Reports

×

Report "Oracle9i Reports Developer Volume 1"

Your name