Reports in Developer/2000 Release 2.0 - Finally Ready for Prime Time!

By Paul Dorsey, Dulcian, Inc.

Reporting tools are an integral part to most information technology toolsets. With reports in Developer/2000 Release 2.0, you now have an enterprise-wide choice.

In the past, report writing tools tended to fall into three different categories:
1.Easy-to-use but relatively limited report writers such as Crystal Reports
2.Reporting tools that support power users in the construction of relatively complex reports such as IQ or Platinum ProReports
3.Production reporting tools such as earlier versions of Oracle Reports which is capable of generating any report but which could only be used by skilled developers

What was lacking in the industry was the so called "enterprise-wide" reporting tool that can simultaneously be used by novice developers and end users for ad hoc reports as well as by IS professionals for complex production reports.

The way to create this "enterprise-wide" reporting tool is to start with a fully featured production reporting tool and create an easy-to-use "Wizard" on top of the product for building simple reports. I saw this idea implemented several years ago, for the first time, with Easy SQR that ran on top of the SQR reporting tool. Oracle has now implemented this same vision in the Reports component of Developer/2000 Release 2.0 (Reports v.3.0), attempting to give us the best of both worlds. Clearly, Reports 3.0's strengths lie in building complex production reports. With the new developer interface, however, we can use Reports 3.0 for a reasonable percentage of power user reports. The added advantage is that a skilled developer can take these simpler reports and finish them using the more sophisticated parts of the product. This represents an important step forward for the industry in that Reports 3.0 makes it possible for developers and end users to create reports using the same tool organization-wide.

Prior to Reports 2.5, Oracle had a rocky history with report writers. Many people, including the author, believed there were other report writers on the market that were easier to use, more powerful, or both. That perception of poor quality and lack of power in the Oracle development tools has been unjustified since the advent of Developer/2000. Reports 3.0, the reports component of Developer/2000, Release 2.0, is sufficiently powerful and easy enough to use that it should silence the last of the critics.

Reports 2.5 is a very powerful product but requires developers to have an in-depth understanding of the product in order to get the most out of it. Reports 3.0 is just as powerful. There is no report that can be built in version 3.0 that couldn't be built in version 2.5. The difference is that reports can be built more quickly, easily and with less frustration with Reports 3.0 than in any earlier version of this product. Much of what Oracle has done in this new version has been to improve its usability for development. The Oracle development team for Reports 3.0 was very much aware of the difficulties in using 2.5 and earlier versions. Many of the changes in this product reflect Oracle's response to developer demands for an easier to use, more convenient and intuitive product. The Reports 3.0 interface for developers has been greatly improved and numerous features have been added which decrease the time needed to learn how to skillfully use the product.

An additional improvement in Reports 3.0 is that functionality in the product has been extended by including a Reports Server piece which speeds performance, and adds scheduling and support for three-tier architecture.

This article has two purposes. For those using another reporting tool, it should convince you to consider giving Oracle Reports 3.0 a chance. Second, the reader will get a glimpse of the new features in version 3.0 and how these impact the rapidity of developing Reports.

Current Architecture
This section discusses the current architecture of Reports (which is not changing with the new version) along with some development tips and some of the most powerful features of the Reports product which are rarely used.

Data Model and Layout Editor
As in Forms, Reports separates the logical groups of the data from the physical layout of the data. Whereas in Forms, there are blocks, in Reports, there are groups. Groups come from queries. It is important to understand that once a query has created the groups, its job is complete. With respect to the query, the developer has few places to be concerned with after the groups have been created. A query attempts to generate groups that will correspond to the data that you need to put in your report.

Objects in a report layout are organized via frames. For every repeating frame in the Layout Editor, there must be a group. There may be groups that are not associated with any frames. For example, for producing some complex reports, detail groups may be needed even though none of the information from those groups appears directly on the report, but is only used to calculate summary information.

In the Data Model, it is very easy to link multiple queries together. Linking multiple queries together effectively creates correlated subqueries. For every record fetched from the master query, a separate instantiation of the detail query is created. Usually, performance can be increased by reducing the number of queries, ideally, to one. Of course, it is not always possible to do this. However, when looking at the data model, it is important to recognize that all that is trying to be accomplished is to create the information in your report. As long as the same information is getting into the same groups, the queries can be crafted in such a way as to optimize performance.

a) Formula Columns
Formula columns are user defined columns based on PL/SQL functions. They are very useful; however, care must be taken about putting cursors in formula columns. Every time that formula is executed, (which means one time for every row of that type in the report) another query is fired off to the database. An alternative that almost always enhances performance is to place the formula in the database as a function and make it part of the base query, thereby reducing the number of cursors used.

Another trick with formula columns is to use them as indicators. For example, to have a departmental report display the number of men and women in each department, in the Employee group, make two formula columns, one for men and one for women. The value in the column for men is 1 if the employee is a man, and 0 otherwise. Likewise, the value in the column for women is 1 if the employee is a woman, and 0 otherwise. A simple summary count field can then be used in the master group to ascertain the number of men and women. This is all part of the underlying notion of getting the appropriate information into appropriate groups in any way possible.

b) Views
Because information is only being displayed and not updated, views can be used that are arbitrarily complex. Oracle's ability to embed functions in views is a very powerful yet often under-used feature.

c) Maximum Number of Rows Retrieved
The number of rows retrieved in any query can be limited through a property on the query. This is very helpful when testing reports being run against very large databases. It can also be used for reports that, by design, are only retrieving a specific number of records. For example, for a report that returns information concerning the top 25 customers by sales, simply order by sales in the query and limit the maximum number of rows to 25. This property can be changed at runtime using an SRW command.

Flexible Reporting
Perhaps some of the most powerful and rarely used features of Reports are lexical parameters. A lexical parameter is simply a text string that can be incorporated into the text of a query. This allows complete control over a query at runtime. The way I use this feature is to create a flexible reporting system. Specifically, I build a form as a flexible reporting interface front-end. Users choose the report they want to run and then, through a series of pop-lists, multi-record blocks and check boxes, specify the report, sort criteria, and fields on which they want to break along with an arbitrarily complex set of filter criteria. The form then builds the appropriate lexical parameters to modify the base query report that then runs the report according to the user-specified criteria. In this system, all reports are built to support up to two levels of breaks. If breaks are not used, their display is suppressed through the use of format triggers. This procedure could even be used to allow users to specify the columns they want displayed, within limits. Such a flexible reporting system can largely supplant the need for an ad hoc query tool without an organization having to worry about users generating reports that are not the reports they intended, as is frequently the case with ad hoc query tools. In these systems, I also make it possible for users to save and retrieve their report settings thus enabling them to run standard reports very quickly, even those requiring complex settings.

Lexical parameters are, arguably, the single most important feature in Oracle Reports. The first time I built this kind of system, the original report analysis estimated the required number of reports to be 200. Using lexical parameters and a flexible front-end, I was able to satisfy the reporting requirements with 12 reports. An added benefit arose when the database structure changed significantly three times during the course of the project. Each time, it was possible to re-write and test the entire reporting system in about three days. At another client-site where I implemented this style of reporting system, the users' needs were not well defined. They made many modifications, asking for numerous additional reports beyond the original specifications. Because of the flexible nature of the reporting system, in all but one case, all that needed to be done was to show the users how to use the system we had already built in order to generate their new report. In only one case did we have to add an extra column to one of the reports. Everyone building production reporting systems should be using lexical parameters and a flexible Forms front-end. Details on this method were presented in a paper entitled "Using Developer/2000 to Build a Flexible Reporting System" by Joseph P. Strano at the ODTUG '96 conference and can be obtained by e-mailing Dulcian, Inc. at cfisher.dulcian@worldnet.att.net.

PL/SQL Functions
Oracle delivers a set of PL/SQL functions to support some extra functionality in Oracle Reports. The SRW package from SQL Report Writer (the original name for Reports) delivers these functions, which every Reports user should be familiar with. This package provides the same sort of extension to Reports that Forms specific PL/SQL provides to Forms. Some examples include the following:
 The function to change the maximum number of rows in a query is SET_MAX_ROW.
 To get the current page number in the report, use GET_PAGE_NUM.
 Data returned in a specific field can be overwritten using SET_FIELD_NUM for number fields, SET_FIELD_CHAR for character fields and SET_FIELD_DATE for date fields.

Note that you can use packaged variables for globals in Reports. A complete list of SRW functions can be found in both the Reports documentation and the on-line help.

New Features in Reports 3.0 and Their Impact on the Rapidity of Developing Reports There are many new features of Reports 3.0 that make the product easier to use and increase the speed at which report development can be done.

Improvements in Developer Interface
The Oracle Reports 3.0 design team must be given credit for making developers' jobs easier. For the first time in my use of this product I find myself pleasantly surprised by some of the changes made from earlier versions. For example, developers can now change font size and style from the toolbar as in most Windows packages. In addition, the interface includes icons to quickly apply standard format masks such as currency, percent and adding/removing significant digits after the decimal point. Overall, there seems to have been a consistent and concerted effort to try to make the product more intuitive.

Parameter Form Builder
There is now a Wizard to help build parameter forms. It is very simple to use. All that is necessary to know when building a parameter form is to select Tools/Parameter Form Builder and the Parameter Form Builder Utility pops up.

Report Wizard
Forms 3.0 now includes a useful Report Wizard with a simple method of selecting various report types. This Wizard walks the developer through all the major steps of report writing allowing them to build simple reports with a point and click interface.

Query Builder
There is now an integrated Query Builder that allows developers to build their queries through a point and click interface. This Query Builder is borrowed from Oracle's Data Browser product which is a good example of the kind of integration becoming visible in all Oracle products. Not only does the developer get a consistent look and feel, but innovations in one product may easily show up in another.

Property Palettes
Another example of Oracle product integration can be seen in the improved Property Palettes (formerly knows as Property Sheets). Property Palettes use the same metaphor as the Object Navigator so that groups of properties expand and collapse. Moreover, Property Palettes behave the same way in both Forms and Reports. For example, the wonderful feature of being able to use a single Property Palette for multiple selected items which allows developers to change one property and have it affect multiple objects simultaneously is now available to us in Reports, just as it has been in Forms.

Useful Additional Icons for Navigation
In past versions of Reports, the keystrokes used to bring up the Data Model or Layout Editor were inconvenient. With Reports 3.0, icons in a special section of the toolbar enable quick navigation to different parts of the product. This makes for a much more convenient interface that is also economical with respect to screen real estate. Reports can effectively be used on a 1024 x 768 monitor whereas Forms often demands a 1280 x 1024 monitor or better for development.

Templates
Templates are going to be implemented in the new versions of both Forms and Reports. Templates in Reports 3.0 can include not only common objects in reports such as logos, report headers, page numbers and dates, but templates are also "smart" insofar as they contain the default property settings that govern the generation of the report. Specifically, fonts are specified for both data and labels. You can specify different fonts for primary data frames as well as group by frames. Oracle provides several attractive looking templates and also gives developers the ability to create and save their own templates.

These templates will help to greatly decrease the time spent fine tuning the layout of reports. Up until this time, in my own reports' development, I have tended to stay very close to default layouts in order to minimize development time. I am greatly looking forward to this new feature that will allow developers to easily specify and reuse relatively complex layouts.

Live Previewer
The new Live Previewer is one of the most highly touted (and rightly so) features of the Reports 3.0 product. Now, it is possible to make any change to the Layout Editor and immediately see its impact in the Live Previewer without re-executing the query. Some changes to the layout can even be made right in the Live Previewer. The existence of the templates and the Live Previewer together mean that, for simple forms, it is not even necessary to understand the complexities of frames in the Layout Editor. This gives power users and novice developers the ability to generate simple reports. Unfortunately, for complex production reports, it is still necessary to understand how frames work in the Report Layout Editor.

Don't underestimate the impact of the Live Previewer feature. Most of the time spent building a report after the data model is created is spent waiting for the report to re-execute. In past versions, even small changes made in the Layout Editor meant re-execution of he report. This allowed you to see the impact of the change. With the Live Previewer, all of that execution time is eliminated so that the time required to build the layout of an elegant looking production report will be reduced by 50 percent or more.

Reports Server
The Reports Server part of Oracle Reports brings the product fully into the world of three-tier architecture. We have all noticed that running Oracle Reports on a client machine means that the client machine is effectively tied up while the report is running. Reports can take hours to run across a slow network connection. The Reports Server allows us to have all reports run from a central Reports Server box. Of course, it also supports the standard scheduling options. One of the nice features is the ability to cache a report on the server once it's been run. If someone else tries to run that same report within a specified period of time, rather than rerunning the report, the Report Server can automatically distribute the recently generated version.

The multi-threaded Reports Server can greatly improve the through-put of the reporting requirements for a company. Not only can reports be run on a dedicated and presumably much faster server than on client machines, but it is also possible to set up a number of report engines that will simultaneously process several reports so that idle time while waiting for information to come back from the DBMS can be virtually eliminated.

PL/SQL Version 2.x Support
One of the major reasons for the delay in the release of Developer/2000 2.0 has been Oracle's efforts to bring PL/SQL version 2.x support into the product. There are significant benefits to having PL/SQL v.2 in Forms but there are also notable impacts on Reports. For example, one of the standard tricks used in Forms is to bring a small table into a record group so that lookups can be done locally. With PL/SQL v 2., this same strategy is now available in Reports using PL/SQL tables.

Saving Reports as PDF Files
One of the long-standing, unfulfilled needs of report writers has been the ability to modify the report after generation. For years, we have looked forward to Reports generating RTF formats that we could bring into our favorite word processor to edit after generation. The nature of a report, with its multiple fields in arbitrary placement throughout, prevented easy adaptation to an RTF file since RTF files just do not easily support all of the different kinds of formatting possible in a report. However, Adobe Acrobat files support all of the complexity of a report and allow editing of that report after generation through the use of PDF files. This new ability in version 3.0 to save reports as PDF files may broaden the ability of Oracle Reports to support very complex reporting requirements.

Web Reporting
Reports can now be generated as HTML files so that they can be easily published on the Web. Coupled with Oracle Forms 5.0's ability to generate *Java* applications, this will enable us to build complete client/server applications. Then, using a simple recompilation and some adaptation these applications can be redeployed in an Internet or intranet environment.

Reports 3.0 also supports the new HTML style sheets which will provide developers with greater flexibility in the professional look of their reports. More than just simple Web functionality is also available in Reports 3.0. We can now place Hyperlinks, URLs and JAVA applets in Web Reports.

Conclusions
Oracle Reports version 3.0 represents a dramatic improvement over Reports 2.5. While maintaining the same powerful ability to generate virtually any report imaginable, the developer interface for the product has been greatly improved. Reports 2.5 was a very effective tool for building complex reports; but these reports were often limited to a relatively plain appearance. Reports 3.0 allows developers to build complex reports very quickly with a more elegant look. The Live Previewer will greatly decrease the amount of time wasted waiting for a report to regenerate again and again while developing it.

Even with all of these added features, it will still be possible to import all Oracle Reports versions 2.0 and 2.5 files seamlessly into Reports 3.0. If you want to interface Reports 3.0 with some other development product (such as Delphi or Power Builder) which does not deliver as sophisticated a reporting engine as Oracle Reports, Reports 3.0 includes an OCX to make this interface possible. Thus far, Reports has been a "for experts only" tool. With the newly available templates and improved user interface, even novice developers and power users can effectively use Oracle Reports. For the first time, this allows Oracle Reports to compete with reporting tools that are aimed at the lower end of the market such as Crystal Reports, while at the same time providing the in-depth functionality that makes it possible to support the most complex production reporting requirements.

If you have stayed away from Oracle report writing tools in the past because of the difficulty of learning and using the product, it is now time to rethink your decision and take a closer look at what Reports 3.0 has to offer.

 

 

Oracle Consulting Services | Application Development | Custom Training | Data Migration | Data Warehousing

Database Administration | About Dulcian | Papers & Presentations | Related Links

Publications | Employment Opportunities | Products | Home | Contact Us

©2000 Dulcian, Inc.