Oracle 9i Reports

8.3 Oracle 9i Reports

Previously, we have seen how reports can be generated using tools such as Oracle Discoverer. But if you want to create sophisticated reports, using data from a variety of sources, which can then be published in a paper or an on-line format such as HTML or PDF, consider using Oracle 9i Reports.

Oracle 9i Reports will accept data from a variety of sources, including, an Oracle database, Oracle Express, 9i OLAP, XML, JDBC, or even a simple text file. For example, Discoverer uses XML to send a report to Oracle Reports. The resulting report can be presented in a variety of styles and can be enhanced by adding graphs such as pie charts or bar charts. Each report can be based on predefined templates, or the Template Editor can be used to create your own templates, which means that you can define a standard layout and include, for example, your company logo. Once the report has been defined, it can then be sent to a number of different destinations, including a file, a printer, e-mail, or Oracle Portal.

Oracle Reports is composed of several components:

• Report Builder: a Wizard-based tool used to create reports

• Oracle 9iAS Reports Services

  • Reports converter to change the format of a report

  • Queue Manager

Let us now look at some of the types of reports we can produce using Oracle Reports.

8.3.1 Creating a report using the Report Builder

A report can either be constructed manually or by using the Report Wizard, which will guide you through the steps of creating a report. In Figure 8.6, we see one of the first screens, where we are asked to select the type of layout. Since our report is going to be published on our Intranet and distributed in hard copy, we have selected the Create both Web and Paper Layout option. This means that a single report can have two formats if required—one for paper and another for the Web.

Figure 8.6: Oracle Reports—select the reporting medium.

Our next step is to select the type of report we require, as shown in Figure 8.7, where you can see there are a number of options to choose from; a thumbnail sketch of the style is presented next to each radio button. The title of this report will be "Sales by Customer," and the data will be presented in tabular format. Later we will see examples of reports using the matrix option.

Figure 8.7: Choosing the style of report and title.

In Figure 8.8, we can see the ways that Oracle Reports allows us to query the data source. In this example, we have chosen to use SQL, but it could just as easily be a query to Oracle Express or JDBC.

Figure 8.8: Oracle files—selecting the data source.

Since we are querying the database, we see in Figure 8.9 that there are three methods available for defining the SQL. If you know SQL, then you can type it in manually, or the SQL can be imported from a file. In the example in Figure 8.9, the SQL statement was created using SQL*Plus, saved in a file, and then imported into the Report Builder.

Figure 8.9: Oracle Reports—specifying the SQL query.

For users not familiar with SQL, use the Query Builder, because it makes defining SQL statements very easy and will certainly save you a lot of time. All you have to do is select the tables to include in your report and then tick the items that are to appear. Oracle Reports automatically determines how to join the data and creates the required SQL when you leave the Query Builder. In Figure 8.10, we have selected the CUSTOMER, PRODUCT, and PURCHASES tables and only asked for four columns from all of these tables. Note how Oracle Reports has determined how to join the PURCHASES table to the CUSTOMER and PRODUCT tables.

Figure 8.10: Oracle Reports—using the Query Builder.

Oracle Reports will now validate our SQL statement. Next, we are asked what grouping is required. A report with a grouping can contain one or more levels of subtotals, but in Figure 8.11 there will be only one: on name.

Figure 8.11: Oracle Reports—specifying the grouping levels.

The columns that will be printed in the report are selected next—all the columns in the query or a subset of them may be chosen. In the example in Figure 8.12, we will be displaying our customer's name and the total purchases by customer.

Figure 8.12: Oracle Reports—selecting the fields in the report.

We can now select, in Figure 8.13, whether we require any totals to be computed for data. See how Oracle Reports gives us buttons to request sum, average, count, minimum, maximum, and %total. In our example all we need is the sum of purchase_price.

Figure 8.13: Oracle Reports—calculate totals.

A nice feature in Oracle Reports is the ability to specify how wide the columns should be for our data and what our column headings should be. In Figure 8.14, we are given the option to specify these widths before our report is produced. Therefore, we have taken the opportunity to increase the width of the customer name to 20 characters and change its heading to Customer.

Figure 8.14: Oracle Reports—specify the column widths.

The layout for the report can be determined by selecting one of the predefined templates, or you can define your own template. When you're done, click Finish and you are returned to the Reports Builder. Once here you can save the report and then select Program followed by Run Web Layout to actually run the report. Figure 8.15 shows us the actual report. However, this is in its raw format—before it is customized with company logos and other items that are required. From within Reports Builder you can also modify your report by adding currency symbols, commas, and decimal points. You can also change the typeface and font (bold, underline, italics) of text. You can format numbers to represent a monetary value and add your own logos, graphics, headings, and footers. It is indeed a very comprehensive reporting tool.

Figure 8.15: Oracle Reports—final report.

8.3.2 More Oracle Reports examples

The report shown in Figure 8.15 is a very simple report and not that exciting, but it illustrates how to create a report. Let us now look at some examples of more reports we have created using Oracle Reports from our EasyDW data Warehouse.

Matrix report

In Figure 8.16 we see an example of a report using the matrix layout, where we can view the total amount our customers have spent by day. Looking at this report we can see that our January 1 sales promotion created a lot of business for us, but after that business dropped off and some customers did not buy from us again for another few months.

Figure 8.16: Oracle Reports—matrix report.

Conditional report

The extensive capabilities of Oracle Reports begins to become apparent when we see how defining a condition on our report can be used to highlight when specified conditions have been reached. In Figure 8.17 we have chosen to highlight in red any customer who spent less than $250. We could even go further and specify a number of conditions, all highlighted with a different color. Therefore, customers spending less than $250 are highlighted in red, up to $1,000 in yellow, and over $1,000 in black. Unfortunately, we can't show this, so the red numbers are in italic.

Figure 8.17: Oracle Reports—conditional report.

8.3.3 Building a report for the Web

Everything we have seen so far can be used to publish our report on the Web. One of the nice features in Oracle Reports is that you can design your report with one format for the Web and present it in a different format for paper. We can see the two formats in the Object Navigator window shown in Figure 8.18.

Figure 8.18: Oracle Reports—Navigator and Designer.

Publishing this on the Web can be done in various ways, such as making it available to Oracle 9iAS Portal as a portlet or turning the report into an HTML document. We can turn our document into HTML or PDF format either by the File then Generate option or by using the Oracle Reports Server. In using Reports Builder, as shown in Figure 8.18, click on File and then Generate to File and select the output format; in this case we have chosen a paginated HTML, but we could have selected PDF. Our report is converted and saved in HTML format.

In Figure 8.19, we can see how our report looks in our browser, which is exactly how we designed it in Figure 8.18. Note how we have added a date to the report; changed the format, layout, and fonts for the heading; removed the logo; displayed our total in red; and added some blank lines underneath the total.

Figure 8.19: Oracle Reports in Web format.

Oracle Reports Server

If the Oracle 9iAS Reports Server is started, a report can be exported into reports run-time format and placed on the queue, where it is converted into one of the following formats:

• ASCII

• HyperText Markup Language (HTML)

• HTML Cascading Style Sheets (CSS)

• Adobe Portable Document Format (PDF)

• XML

From the Oracle Reports Queue Manager, a new job is specified and the screen shown in Figure 8.20 appears. Here you specify the report to be converted, which in our example is Sales_by_month, and then the name of the output file, sales_by_month.pdf, since we are making an Adobe Acrobat file.

Figure 8.20: Oracle Reports Server—select the report.

As you can see, there are a number of options that can be specified. You must select the Data Source tab to specify the database, user name, and password to be used to retrieve the data. When we click on the Output Options tab, the screen shown in Figure 8.21 appears, where we specify the output format; this is where we have chosen the Adobe Acrobat format of PDF.

Figure 8.21: Oracle Reports Server—specify the output format.

By selecting PDF and HTML formats, you have the ability to view the reports using a Web browser. Oracle Reports also allows you to deploy a report to:

• File

• Printer

• Oracle Portal

• E-mail

• Cache (for direct output in the browser)

The advantage of using the Oracle Reports Server is that a report can be scheduled to be updated on a regular basis. In Figure 8.22, we have selected the Scheduling Options tab, where we have asked that our report be refreshed every hour. Once the report has been created, it can then be distributed or placed in our portal or Oracle files for users to read.

Figure 8.22: Oracle Reports Server—schedule a report.

When we have finished defining our job, it is automatically placed on the reports queue. When it is time for it to be run, the appropriate conversion will take place. In Figure 8.23, we can see our report, called Sales_by_month, which completed successfully and generated our PDF file.

Figure 8.23: Oracle Reports Server—job queue.

Now that we have our report, it can be distributed via e-mail, Oracle files, or through the portal. Hopefully you agree that there are many easy to use tools available to make this task quick and easy.