Building a Report with the Report Wizard

The Report Wizard creates all of the basic elements of an Oracle Report including the layout of the report, the query that will drive the report, the columns from the query to be displayed on the report, any calculations on those fields, the formatting of the displayed fields, and a template (if desired) to use when displaying the report. It is possible to build a report from scratch without using the reports wizard (by selecting the Build a new report manually radio button in Figure 4-10), but in most cases, it is more productive to use the reports wizard to define your report, and then add enhancements to it manually later. On the Welcome to Report Builder screen, select the Use the Report Wizard radio button and click OK.

The Layout Screen

The Report Wizard begins with a welcome screen. The next page of the reports wizard asks what type of layout will be used for the report. Layouts can be defined as a specification of how a report will be displayed. An Oracle report can have both a web layout and a paper layout, so that the report appears one way when displayed on the web and differently when printed. For now, select the Create both Web and Paper layout radio button and click Next >.

The Report Style Screen

The next page of the Report Wizard allows developers to specify a report style. A report style defines how the report will group the rows returned by the query (or queries) that will drive the report. The most common choices are Tabular, Group Left, and Group Above. One of the most powerful features of the Report Wizard is the capability to go back and change any of the settings after the report is generated (you can also do this manually in the Object Navigator, which we discuss shortly). For now, select the Tabular radio button, name the report Example1, and click Next >.

The Data Source Screen

The next page of the wizard asks for the specification of a data source. Depending on what is selected on this screen, the following wizard page will display options for that particular type of query. This is another example of the powerful features built into Oracle Reports 10 g , as the developer has the ability to pull data from virtually any data source. The data sources included are

• Express Server Query Lets you create a query that selects data from an Express Server. This feature is provided for backward compatibility only as Oracle Express Server, an OLAP database intended for data warehousing, has been discontinued by Oracle. Most of Oracle Express s sophisticated data warehousing technologies have been incorporated into the Oracle Database Server product.

• JDBC Query Lets you access any JDBC-enabled data source. The key word in that sentence is any. As long as a valid JDBC driver is installed on the system and used to execute the report, Oracle Reports can pull data from it. This is an extremely powerful feature allowing reports to be built incorporating data from multiple databases in a single report without having to develop and test ETL (Extract, Transform, Load) programs or purchase expensive gateways. JDBC drivers exist for traditional data sources (SQL databases) such as Microsoft SQL Server, IBM DB2, and MySQL, but also exist for nontraditional data sources such as Microsoft Excel spreadsheets or ACT! (contact management) databases.

  Note  

  JDBC drivers come in various levels, with a level 1 JDBC driver having the fewest number of features and a level 4 JDBC driver having the most. The cost of the higher-level drivers is greater, but is required for all but the most elemental forms of data access. For more information about JDBC drivers, go to http://java.sun.com/products/jdbc/driverdesc.html.

• OLAP Query Lets you create a query that selects from multidimensional Oracle Online Analytical Processing (OLAP) data stored in an Oracle database. For these types of queries, it is common to use a matrix layout on the previous page (report style) of the reports wizard.

• SQL Query The most common selection; highlighted automatically when entering this page of the wizard. Selecting this option will display a page that allows the construction of a query to drive the report graphically. The Query Builder allows developers to select tables and columns to be included in the report while maintaining and displaying all referential integrity constraints. After building the query graphically, the developer has the option of modifying the query (such as adding where clauses, as an example).

• Text Query Allows developers to construct a report that queries data from a text file. Out of the box, Oracle Reports 10 g supports three formats: Apache log file, text files with comma-delimited fields, and text files with space-delimited fields. Additional formats can be included via Pluggable Data Sources (PDS). See Chapter 42 of the Building Reports manual (Oracle Part # B10602-01), Building a Report with a Text Pluggable Data Source, for more information on PDS.

• XML Query Lets you access an XML data file. A Document Type Definition (DTD) file, defining the elements and data structure contained in an XML document, is needed.

The Data Query Screen

Select SQL Query and click Next >. The next page of the Report Wizard is where the definition of the query that will drive the report is specified. The developer has the option of typing out the query manually in the Data Source definition field, running the Query Builder to build the query graphically, or importing the query from a text file. To continue building the example report for this chapter, click the Query Builder button to start the Query Builder. If this is the first time Query Builder has been selected in the current session, a dialog box requesting logon information is displayed. If a connection has already been established in the current Reports Builder session, Query Builder will automatically use that connection information. If a connect has already been established and a different connection is desired, click the Connect button before clicking the Query Builder button (different connection information can also be specified by selecting File, then Connect in the Reports Builder menu). Connect to the HR schema in the database that gets installed with the infrastructure.

Tip  

Alternatively, if you have access to another database with the HR demo schema and do not wish to connect directly to the Oracle Application Server 10g infrastructure database, feel free to do that.

Note  

If you have Oracle E-Business Suite installed at your location, do not try to connect to the HR schema in that instance and attempt to create the report in the chapter against it. The HR schema in Oracle E-Business Suite is very different from the demo HR schema included with version 9i of the Oracle database.

On the Query Builder page, a dialog box with the tables and views owned by the schema, you are logged in, as shown in Figure 4-11. You can select tables, views, or snapshots from other schemas by selecting <LOCAL> in the drop-down box in the dialog and then selecting another schema. Only tables, views, and snapshots you have been granted select privileges on will be available. As the HR user , double-click on Regions , then double-click on Countries , then click Close. A screen similar to Figure 4-12 is displayed.

Figure 4-11: Objects owned by the HR schema

Figure 4-12: The Query Builder screen

The arrow connecting the two tables is a reflection of a foreign key relationship between the countries and regions tables. In this example, there is a one-to-many relationship between the two that can be stated like this: A single region is made up of one or many countries. Now that the tables making up the query have been selected, it s time to select the fields (columns) from those tables that will make up our query. Select the check boxes next to region_name and country_name and click OK. Just because a column is selected on this screen, it does not necessarily mean it will be displayed on the report. The check boxes here only serve to specify which columns will be included in the query. Why would a column ever be selected to be part of the query, but not be displayed on a report? It is possible to alter the formatting of a group based on a value in the database, while not actually displaying the value. For example, a manager may want a report that prints products sold by the company that have the highest margins in a different color without actually printing the margin percentage. The margin percentage might be selected (or derived) from the database and used in a conditional formatting trigger to print the row corresponding to the product in a different color .

The data source definition now contains the following SQL that will be used to drive the report:

 SELECT ALL REGIONS.REGION_NAME, COUNTRIES.COUNTRY_NAME FROM REGIONS, COUNTRIES WHERE (COUNTRIES.REGION_ID = REGIONS.REGION_ID) 

The Data Columns and Data Calculations Screens

Click Next >. The Fields page of the Report Wizard provides the capability to specify which fields will be displayed on the report. Click the double greater-than signs (>>) to move all of the columns to the right-hand pane. Click Next >. The next page in the wizard allows calculations of the fields in the report to be specified. To see the total number of rows, make sure REGION_NAME is selected in the left-hand pane and click the COUNT > button in between the two frames . Click Next >.

The Labels Page

The Labels page allows the developer to change the headings on the report. Oracle will make a guess based on the name of the column you are selecting out of the database. You can also change the width of the columns that will hold the data queried from the database. To see the effect of this, change the label that says Count: to Region count: and make its width 25. Click Next >.

The Template Page

The final page of the wizard allows the developer to specify a template to be used when displaying the report. There are predefined templates, but they are of limited use as they include a graphic that says Your Company, Inc., which is hardly appropriate for a production report. The developer is not limited to these templates, as they can be created or modified. Choose Blue in the predefined template list and click Finish. The report is then executed and the results are displayed on the screen (see Figure 4-13).

Figure 4-13: An Oracle Report displayed in the Report Editor

Note  

Name this report Chapter 4-1 and save it as a Reports Binary (*.rdf) file. An .rdf file is specific to an operating system. In other words, it is not possible to take an .rdf developed on, say, a Microsoft Windows machine and run it on a Linux machine. In Chapter 6, we ll cover the steps to take a report developed on one machine to be run on another machine with a different operating system, as well as discuss saving the report as a JSP.