Reports Templates: Getting the Most Out of Oracle Reports[1]

Dr. Paul Dorsey, Dulcian, Inc.

 

Overview

Reports templates are an extremely valuable addition to Oracle Developer. For the first time, you can generate elegant reports very quickly. A Reports template is a special file with objects that are copied into a report based on pre-selected properties and information governing the way that a layout is created using the wizards. When a template is applied to an existing report, Reports tries to make the appropriate decisions about which objects should be recopied and how to lay out the report.

With the latest version of Developer, you can even create sophisticated reports requiring little manipulation in the Layout Model. Almost all of the manual manipulation necessary in the Layout Model should involve nothing more than specifying an Additional Default Layout and generating it using a template.

Comparison with Forms Templates

Reports templates really serve two purposes. In one sense, they serve the same function as Forms templates. You can predefine elements common to all your reports. You can specify various triggers, add user parameters, attached libraries, as well as put common objects in your report layout. The other purpose of Reports templates is to control the way that the report layout is generated through the Report Wizard or the Additional Default Layout tool. You can specify the fonts and many other properties used in fields, labels, and groups.

One of the big differences between Reports templates and Forms templates is the ability to use referenced objects in Forms templates. If your GUI standards change, you can make that change quickly and easily in Forms and have the change reflected in existing forms. With Reports, once you generate a report based on a template, there is no link back to anything in that template. You cannot make a change that will propagate through to multiple reports. The only way to share anything in Reports is either through database objects such as functions and procedures in packages, or through libraries that can be attached to multiple reports. These are the only ways to make changes to more than one report simultaneously. However, if you build your report entirely through the Report Wizard, you can generate a large percentage of your reports. These reports can then quickly be regenerated using a different template.

Limitations

 Reports templates have some limitations:

·         You cannot place objects in the Body section of the Template Layout Model. This is not a particularly troublesome restriction, but it does force you to place titles, page numbers, dates, and any other template objects in the margin area.

·         You cannot create global objects in the Template Data Model. This restriction can be accommodated by creating user parameters and package variables in your template.

·         There is no longer any way to specify default Header and Trailer objects in the report template, although this functionality existed in Reports 3.0.

Template Structure

There are several sections in the template that correspond to the sections in a report:

A.      Data Model

B.       Layout Model

C.       Report Triggers

D.      Program Units

E.       Attached Libraries

However, these sections are not all the same as the sections for a report. In addition to these sections, there is also a Property Palette for the whole report. If you want to change the default settings in the template’s Property Palette, then every report created with the template will have the new settings. The most likely parameters you will change are the Height and Width of the page. If you are printing in Portrait rather than Landscape format, you will need to change the height and width settings in the Layout Model—Section Property Palette.

1. Report Template Data Model

In a template, by using the Data Model, you are able to specify default system parameters and user parameters. Therefore, it is possible to set defaults for system parameters. For example, you can specify having the report run in preview mode by setting the DESTYPE Initial Value property to “Preview” in the Property Palette. A description of the other system parameters can be found in the online help under the topic “System Parameters.”

You can add user parameters to support the use of lexical parameters or bind variables. You can also add any common parameters to the Data Model that you want to have in all reports, such as date ranges or numbers of records. These parameters will automatically be placed in every report generated using this template. Keep in mind that user and system parameters are the only things you can specify in the Template Editor—Data Model of a report template. You cannot create default template queries or include any other objects you would create in the Report Editor—Data Model of a report.

2. Report Template Layout Model

The Layout Model section of the template is the core of the report template. This is where you specify properties that influence the way that the Report Wizard and Additional Default Layout tool generate report layouts. Using a report template, you can specify general properties for any report. You also have the ability to define specific settings for each of the different report types (Tabular, Group Above, Matrix, and so on). You can specify the look of the different visual attributes used for break groups in a master-detail report; for example, you can make the master font size larger than the detail font size. However, for specific report types or specific levels within a report, you might want to have different formatting for different levels. For example, in a master-detail-detail report for Departments, Locations, and Employees, the Department font size can be set to 20 points, Locations to 15 points, and detailed employee information to 12 points.

In the Template Editor—Layout Model, you can only place new objects in the Margin section of the template. In earlier versions of Reports, you were able to place objects in the Header and Trailer sections of the template; however, that functionality has been removed from the product. When you apply a template to a report, any margin objects in the template will be copied into the report.

The body of the report in the template works differently than the margin. You do not have the ability to create any new objects here. Rather, you make changes to properties of objects in the Object Navigator and passively view the impact of those changes in the Template Editor—Layout Model. You can select and modify existing objects in the Template Editor—Layout Model and bring up the Property Palettes to change them, but you cannot create or delete objects in the Template Editor—Layout Model.

Once you have created the defaults in the Main section of the Layout Model, you can override the defaults in the Override section. The Override feature allows you to change the default settings for the properties associated with each report style. This must be done separately for each report style. When you specify sections in the override area for specific report styles, they will automatically inherit the properties you set in the Main section.

When creating templates, you are working in two areas: the Object Navigator and the Template Editor—Layout Model. The most convenient way is to work in the Object Navigator while keeping the Layout Model open to see the effect of changes made in the Object Navigator.

A. Section

The first area you can manipulate under the Template Editor—Layout Model is the Section Property Palette. This is where you specify a number of properties, including the Height and Width of a page. Unlike all other template properties, the Section properties cannot be overridden. This is a limitation in the product, since you might want to have a Group Left report based on 11” × 8½” (landscape orientation) size paper. To accomplish this, you will need a separate template.

B. Body

In the Body section, you describe how you want the Layout Editor to work by specifying things such as font sizes and colors for different kinds of objects. If you are creating a complex multilevel master-detail report, you can tell the generator how each level should be laid out. This feature enables you to build elegant and sophisticated looking reports very quickly. Within the Templates–Layout Model–Body–Default section, you can specify the parameters for how everything in the report will be laid out. In the Templates–Layout Model–Body–Override section, you can change these settings for specific report types and specific parts of specific report types (that is, the master frame can be different from the detail frames).

i) Body–Default Section

The Property Palette for the Default section governs the behavior of all frames. For example, you can set the amount of horizontal space between fields using the Interfield (Horizontal) property. Anything set here can be overridden in the Override section in order to change a particular frame for a specific report type (that is, master-detail-detail).

Within the Templates–Layout Model–Section–Body–Default area of the Object Navigator, you can set properties governing the way that objects are laid out within a repeating group. This governs how a section of a report will behave. For example, space between frames and fields can be set using the Default Property Palette. The amount of space between records in a repeating group, a useful property, is unfortunately not available for setting within a template.

There are five categories of properties you can set in the Default section:

1.        Frames: The frames Property Palette governs the visual properties of four frame types (Section, Headings, Fields, and Summaries) and how these frames are created. Each time a section of a report is generated, each of these four types of frames will be shown (three if there are no summaries).

2.        Field Labels/Headings: These Property Palette allows you to specify the visual appearance of data labels and column headings. There are three different subheadings (Character, Number, and Date) that you can set. Each of these has its own Property Palette. In practice, the only difference you may want between the three might be the Character Justification property. Usually, “Left” is used for Character Field Labels and for Headings and Date, and “Right” is for Number Field Labels and Headings.

3.        Fields: The same three subsections (Character, Number, and Date) are available for Fields as for Field Labels/Headings. These Property Palettes are used to set the visual properties (such as Font, Color, and Fill Style) for each type of information. You cannot specify a Format Mask here.

4.        Summary Labels: The same properties are available for Summary Labels as for Field Labels/Headings.

5.        Summaries: You can specify visual properties of Summary data fields for Character, Number, and Date types.

 

Since most reports are usually master-detail, you will probably want to have each level of the report look slightly different. There are several strategies you can employ to accomplish this. You can either have the default settings apply to the detail and override the master, or vice versa. It is somewhat more intuitive to have the default govern the detail and override the master. The reason for this is that there will be some reports that are not master-detail and that will assume the default.

C. Overrides

Overrides allow you to specify template properties for specific types of reports (and even for different break levels of those reports) that will replace the properties you set in the Default section. In the Templates–Layout Model–Section–Body–Override section, you can specify particular properties for each report type and, where appropriate, for different break levels within each report type. By default, the Override section will use settings from the Default section. Any of these Default properties can be overridden and applied to any type of report or break level within a report.

The properties set in the Body–Default area can be overridden for a specific report type in the Override area Property Palette under each report type. For example, to override the settings for Tabular reports, bring up the Property Palette for the Templates–Layout Model–Section–Body–Override section in the Object Navigator and make the desired changes.

Overrides allow you to specify what parameters you want to set for each type of report, even if they are differ from the overall default settings. One disadvantage to these overrides is that they have to be set separately for each type of report. You cannot specify how master records work in general. For example, you will need to specify master properties for Group Left and Group Above reports separately, even if they have the same properties. One useful and interesting aspect of the override capability is that the properties from the default section Property Palette for each section are available as well as the properties from the Frames, Field Labels/Headings, Fields, Summary Labels, and Summaries sections. This allows you to override any property specified in the Default section.

A limitation is that you cannot override or have different margin objects for each type of report, since they are set once for the entire template. If you want to use different margin objects for different report types, you will have to make a different template for each type of report.

The properties in the Override section are inherited from the Default section with the traditional object-oriented behavior. Specifically, if you change a property in the Default section, it will be inherited in the Override section. You may want to explicitly set a property in the Override section that has the same value as the property in the Default section. The reason for this is that even if the property were changed in the Default section, it would not be overridden for a specific report. An example is setting frame distances in a mailing-label report. These might be the same as the distances for the default; but even if they were to change in the default, you would not want them changed for a mailing-label report. You accomplish this similarly to the way you do it in Forms. Set the value of the property on the inherited object to something different from the default. Then set it back to the original value. This will break the link to the Default section and fix that value in the Override section to the value entered.

3. Report Triggers

In a template you can create specific default report triggers. What happens when you apply a template containing triggers to a report? If the trigger in the template does not already contain code in the report, Oracle Reports copies (not inherits) the code in the trigger from the template. The impact of this can be seen if the template is changed and reapplied. It will not change the code behind the report trigger, because now the trigger already has code behind it. This is one reason not to reapply the template.

Note that this is different from the way Oracle Designer generates triggers. In Designer, in every trigger, you are allowed to have a generated portion of code as well as a manually created portion of code. The code trigger section in Reports is not that sophisticated. The rule is that if there is no code behind the same trigger in the report when the template is applied, then the template code will be copied to the report. Under no other circumstances will the template interact with report triggers.

The report triggers that you write do not have to compile cleanly. The report triggers may be code fragments. If there are parts of the code that must be modified for each report, having the trigger not compile may actually be useful. It forces you to modify the code as you build the report, in order to resolve the compilation errors.

4. Program Units

Program units behave similarly to report triggers. If you put a program unit in the template and apply that template to a report, it will copy that program unit into your report as expected. If you have an existing program unit with the same name, it will remain unchanged. If the program unit comes into the report through application of the template and you make modifications to the program unit and then reapply the template, all your changes will be unaffected.

Keep in mind that if you remove the template from the Report Wizard declaring that the report is not based on a template, it will remove the program units in the template, even if these were modified.

5. Attached Libraries

As of this writing, the Attached Library functionality is still limited. If there is an attached library in the template when you create a report based on that template, it will attach the library to the report. However, if you delete that attached library in the report and then rerun the Report Wizard applying the template with the attached library, Reports will not bring it back. If you go from a template with an attached library to one without an attached library, it will not remove the old attached library.

If you create a report based on a template with an attached library, it will bring in the attached library. However, if you decide to change templates or delete the attached library, the program may not act the way you think it should. In testing a few different circumstances, there were some instances where Reports did or did not attach the library, or aborted the program. When you are doing anything other than creating a report the first time using the template, check to see if the appropriate libraries are attached.

Building a Sample Template

This section will describe all the activities necessary for the creation of a working Reports template. This template supports the way in which the author builds production reports and should provide a framework for you to build your own Reports templates.

Before building a template, you must be an experienced Reports builder. The goal is to build a tool to increase your report building productivity. Without a clear idea of how to build reports, you will not be able to create a useful template.

An important point to recognize about templates is that you can create a single template to handle multiple kinds of reports. With a single template, you can support different kinds of reports (tabular or form) because the template allows you to specify parameters for each type of report.

The first step is to create a new template by clicking on “Templates” in the Object Navigator and clicking the Create button.

1. Data Model

Within the Templates–Data Model section of the Object Navigator, some settings need to be changed.

A. System Parameters

These are the basic system parameters for a report that include a set of defaults. If you are working in client/server mode and generating reports for printing, you will want to change the destination type (DESTYPE) Initial Value property to “Preview” to see reports on the screen before printing. Set the DESTYPE Initial Value property to “Printer” if you want reports to go directly to the printer. If you want to generate reports for web use, specify the DESTYPE Initial Value as “File,” DESFORMAT Initial Value property as “PDF,” “RTF,” or “HTML,” and DESNAME Initial Value as the default path to the file where report outputs are stored. You can override the default value of any of the system parameters to suit your individual reporting needs. These should all be set to your desired options for the template.

B. User Parameters

A list of user parameters from outside the report can be placed here. These parameters can either be set at runtime using the Parameter Form or passed from Forms through a parameter list. Finally, they can be set through the command line if you are calling reports from another product. There is no limit to or restriction on the datatypes (character/date/numeric) and numbers of user parameters. The parameters suggested here are used by the authors when building a small number of reports. These parameters are passed to the report dynamically at runtime. The available parameters are as follows:

1. DISPLAY HEADER (HEAD_DISP): This is the title of the report. Make this a character variable field with maximum length of 1,000 characters and an initial value of “sample report title.”

2. Start page: The default value is 1, indicating what page number the report will start with.

The remaining parameters are for dynamic report creation:

·         FLEX_FROM: This is to add tables in the “from” clause of the query.

·         FLEX_WHERE: This is for additions to the “where” clause.

·         break1: This is for flexible user-selected break columns in the report.

·         break2: This is for flexible user-selected break columns in the report.

·         break1_disp: This is for labels for the break columns.

·         break2_disp: This is for labels for the break columns.

·         FLEX_DISP: This is a long (2,000-character) display field that carries a narrative description of the flexible criteria passed to the report.

2. Layout Model

There are only a few objects that you may want to add to the layout. Page numbers, dates/times, and report run titles are the most common objects to add. These must be placed in the report margin. If the Template Editor is not open, right-click anywhere within the Templates section of the Object Navigator and select the Template Editor. You will see the default fonts for the way objects will lay out. Click the Margin icon to get to the margin area.

A. Date/Time

To place a date in the upper-left corner of your report, open the Layout Model for the report. Click the Insert Date and Time icon on the top toolbar. This will bring up the Insert Date and Time Wizard, where you can select a date or time format. The DD-MM-YYYY format is commonly used.

B. Report Title

Rather than hard-coding the report title, you can pass the title of the report as a user parameter. This allows you to use the same report layout for multiple reports.

The object F_HEADER takes up most of the width of the top margin. The source is the sample report parameter HEAD_DISP. The title for a particular instance of a report is passed from the form. If the title is static for a particular report, simply make that title the initial value for the HEAD_DISP parameter. Its Horizontal Elasticity property should be “Fixed” so that extra long titles won’t run into the date on the left or the page number on the right. The Vertical Elasticity property can be “Variable.”

C. Page Number

One way to handle page numbering is to use the default page numbering supported by Reports. For most reports, this is sufficient.

To use the default numbering, follow these steps:

6.        Open the Template Editor—Layout Model by right clicking anywhere in the template area of the Object Navigator and selecting “Template Editor.”

7.        Click on the Margin icon on the top toolbar. You will see a change in the toolbar and a thick line around the body of the report.

8.        You can use the Insert Page Number icon on the toolbar to bring up the Insert Page Number Wizard and specify the appropriate options.

Following these steps will generate some boilerplate text in the specified location. Note that this procedure will embed the value of a field—in this case, in the Page Number field. When your report runs, it will display “Page” <page number>. Note that the text in the boilerplate object is “Page &<PageNumber>.” This technique works anywhere in a report. In any text field, you can embed the value of any report field by prefacing the field name with an “&.” This can be useful because boilerplate text can be printed on any angle, whereas text fields can only be printed horizontally. It is also easy to create boilerplate text by hand without using the Wizard.

D. Report Runtime Parameter Display

The final margin field used in the sample template appears in the lower-left corner of the page, below the body. It is used to show a narrative describing the user parameters passed to the report and is printed only on the first page. Make this field extend all the way across the bottom of the page. The Horizontal Elasticity property is set to “Contract”; Vertical Elasticity is set to “Variable.” The source is FLEX_DISP. In this case, a black line border is used on the field.

The following FORMAT trigger is placed on the field to suppress its printing entirely if there is no information being passed:

 v_return BOOLEAN := TRUE;
BEGIN
 IF :flex_disp IS NULL THEN
  v_return := FALSE;
 END IF:
 RETURN (v_return);
END;

3. Program Units and Attached Libraries

All general PL/SQL functions and procedures that you use for reporting can be placed into program units and attached libraries. You should place all the procedures described earlier in an attached library rather than storing them as program units. The reason for this (in addition to the odd behavior of templates with respect to this section) is that once a report is instantiated from a template, there is no object inheritance from the report to the template. If you change the template, the report will not automatically change. Functions and procedures stored locally in program units in the template will be copied to each report you instantiate. If you later find an error in one of these procedures, you will have to update it manually in every report that was built using the template. By placing all this code in an attached library, all you need to do is to change the code in the library and recompile. All of the reports using that library will automatically be updated.

4. Changing Template Default Settings

For each type of report in the Report Wizard, you can modify the layout settings to make the report look the way you want it to after generation. To do this, select Templates–Layout Model–Section–Body–Default. When you click on Default, you will see Frames, Field Labels/Headings, Fields, Summary Labels, and Summaries. You can change the default fonts as well as control the visual attributes for both normal data and summary columns.

You should now save your template and build a simple report based on the newly created template using the Report Wizard. If you change your template and rerun the Report Wizard, the changes you made may or may not be reflected in your report. The reason for this is that Reports is trying to maintain any post-generation changes that are made. But there is no internal “changed” flag on each object. Oracle Reports tries to make logical decisions about what should be overridden and what should not.

If you want all your changes to be reflected, delete all the objects in the Layout Editor and regenerate the layout every time you make a change to the template. The reason you may want to simply delete objects in the Layout Model and not start over is that, for a complex report, you go through a lengthy process of defining the Data Model. Then you can make a number of attempts to generate the appropriate layout by using different report templates or by changing column widths. You may notice that some of your labels are incorrect, or the fields may not be wide enough. It may be easier to use the Report Wizard to regenerate the layout than to manipulate it in the Live Previewer or Layout Model.

There are many other properties that you can manipulate. However, the ones mentioned earlier are the only ones necessary to change from the default settings.

Once you specify the defaults, you can set properties independently for all the different types of reports. For example, the most common type of report is Group Above.

For a master-detail-detail report that will be based on this template, there will be three levels. When you go into the template in the Layout Model–Section–Body–Override area, click Group Above. Then click the Create icon three times to make three sections.

Within Section (Level 1), change the Field Label/Headings and Fields font sizes to 14 points. In the Summary Labels and Summaries area for Section (Level 1), change all the Character, Number, and Date datatypes to 14-point font size.

Change the Level 2 Field Labels/Headings for all datatypes to 12-point font size.

Don’t change the Summaries Character “Font Size” for Level 2 because the Summaries font at Level 2 will be at the bottom of the column for the Level 3 objects and should look the same.

In the Templates–Layout Model–Section–Body–Default area, change the Inter-Field (Horizontal) property to “.1” (rather than .174).

These are the only changes you need to make. It would be useful to be able to change the Vert. Space Between Frames property for the repeating frames, but the template does not give you control over that property. You can make this change manually after the report is generated.

Finally, you can add a black line that will separate the Level 1 sections of a report from the Level 2 sections of a Group Above report. You accomplish this by setting the Borders property in the Layout Model–Section–Body–Override–Group Above–Section (Level 1)–Frames–Section Frame area to “Bottom Only.”

Setting Report Template Standards

There is no one correct strategy for creating report templates. However, to maximize report development efficiency, some basic strategies are useful.

When setting GUI standards in the sample template, the old question of whether to do significant post-generation modification or to make sacrifices to accommodate what the product can generate arises again. Because of templates, you can make much nicer looking reports with the default layout. However, to minimize the amount of manual work done in the Layout Editor, you need to be careful about the GUI standards you set.

In general, relatively traditional visual reporting standards make for reports that are easier to maintain, easier for users to read, and that allow more information to be displayed on a page. Fancy highlighting, contingent formatting, and some lines on a report can add little if anything to the usability of the report and can greatly increase development time. As in Forms, you should try to set standards that do not greatly increase the development time of the report. There are always key managerial reports that mandate very precise formatting and control. Keep in mind that such sophistication comes with a price.

In addition, you must recognize that while building production reports or at least several blocks, half of the time you will be using the Report Wizard to lay out the whole report. The other half of the time, you will be using the Additional Default Layout tool to build a portion of the report. This means that you will need several templates. For example, you might have up to four levels in a Group Above report. If you specified a 4-level Group Above report in the Override portion of your template and try to apply the template to a 2-level master-detail report, the template will apply Levels 1 and 2 only. However, you may want to apply the bottommost levels of detail, not the topmost. Also, in a complex environment, you may want to lay out your report frames one at a time. For this reason, it is useful to create a number of different report templates. For a 4-level Group Above report, you would want a total of six templates:

·         4-level

·         3-level

·         2-level

·         Individual template for Level 1 of each of the other multilevel reports (4, 3, and 2)

 

It is possible to include Override settings for more than one type of report in the same template. However, in practice, this can be confusing to maintain. It is better to have a different set of templates for each type of report that the Report Wizard generates. To satisfy the major portion of reporting needs, you will require the six Group Above templates mentioned earlier, four different Mailing Label report templates for common label sizes, and one template for Matrix reports. A simple Tabular or Form-like report uses the same 4-level template as a Group Above report since you can use the Default settings from those reports. Group Left and Matrix With Group reports are not created frequently enough to make creating templates for these specific types useful. If needed, the existing templates could be adapted to these report types with small manual modifications.

Conclusions

Templates are a key feature of Reports. If you don’t have a set of report templates to support report development, you are not utilizing one of the most important productivity enhancements of the product. With earlier versions of Reports, you could build complex reports quickly, but making them look elegant required extensive manual manipulation. Now such manual intervention is unnecessary—with a template, you can generate sophisticated reports. If you find yourself performing a significant amount of manual manipulation of reports, you are not taking full advantage of Reports templates.

 

About the Author

Dr. Paul Dorsey (pdorsey@dulcian.com) is the founder and President of Dulcian, Inc., (http://www.dulcian.com/) an Oracle consulting firm that specializes in data warehousing, web and client-server systems development and products that support the Oracle environment. Paul is co-author with Peter Koletzke of Oracle Press’ Oracle Designer Handbook, Oracle Developer Forms and Reports: Advanced Techniques and Development Standards and with Joseph Hudicka of Oracle Press’ Oracle8 Design Using UML Object Modeling. Paul is an Associate Editor of SELECT Magazine and is President of the NY Oracle Users’ Group.