Chapter 24
Oracle Reports: Introduction to Report Builder
Modifying Reports using Live Previewer
*Describing the Live Previewer *
Exercises
*Modifying Display of Report Data with Live Previewer *
Exercises
*Modifying Position of Report Data with Live Previewer *
Exercise
*Adding Page Numbers and Current Date to Reports *
Exercises
*Managing Report Templates
*Describing the Template Regions *
Using Object Navigator to Create and Modify Templates
*Modifying Templates with Template Editor
*Exercises
*Modifying Template Default and Override Attributes *
Modifying Template Default Attributes
*Using Object Navigator to Define Template Default Attributes
*Using Template Editor to Define Default Template Attributes
*Modifying Template Override Attributes with Object Navigator
*Modifying Template Override Attributes with Template Editor
*Exercises
*Modifying Predefined Report Templates *
Modifying Predefined Templates with Object Navigator
*Modifying Predefined Templates with Template Editor
*Exercises
*Registering Custom Templates *
Adding a Template Image in Report Wizard (Optional)
*Exercises
*Report Storage Methods
*Managing Storage of Report Definitions *
Exercises
*Report Types and Their Portability *
Exercise
*Converting Reports to Different Storage Types *
Compiling vs. Converting
*Using Report Converter on Reports
*It Works for PL/SQL Libraries, Too
*Exercises
*Upgrading Report and Printer Definition Files *
Upgrading PL/SQL Libraries
*Upgrading Report Definitions
*Upgrading Printer Definitions
*Exercises
*Chapter Summary
*Two-Minute Drill
*In this chapter, you will cover the following areas of using Report Builder to manage reports:
The last chapter introduced the use of the runtime component of Oracle Reports to run existing reports and the use of Report Wizard within Report Builder to develop new reports quickly. Now, focus your attention on other topics of modifying and managing reports with Report Builder. This tool allows you to modify your report content from many perspectives such as data content and report layout. The management of report templates, which can be used to standardize the appearance of reports across an application, is covered in this chapter as well. Finally, the chapter covers storage methods for reports. Overall, the content of this chapter comprises 18 percent of OCP Exam 5 content.
Modifying Reports using Live Previewer
In this section, you will cover the following points about the Live Previewer:
The design of a report needn’t stop after stepping through Report Wizard. Once created, you can see what the output of your report will look like using Live Previewer. This interface shows the report in the format it will be printed in, with both the report’s data and template formatting according to your specification. You can modify the report using Live Previewer and Report Wizard, changing the position, format, and content of the report. This section shows you what Live Previewer is and how to use it.
Oracle Reports provides an easy way to see your report as it looks on the page without wasting a single sheet of paper. Live Previewer gives you that functionality, along with a full set of tools to modify the report format and content. When you complete your report design with Report Wizard and click the Finish button, Oracle Reports builds your report against the database and shows the result with the Live Previewer. Figure 24-1 shows the output of your simple tabular report on kangaroos and joeys using the Live Previewer.
Figure 1: Showing your report in the Live Previewer
Live Previewer is accessible when you run your report. There are several features about Live Previewer you should understand. On the top of the window there are two rows of control buttons you can use for switching between different viewing modes for your report. Some buttons exist on this row for opening and saving your files as well. Also possible with buttons from this row are data refreshing, printing, and sending email, activation of wizards, zooming in or out, adding certain information to the report, and other things. Below this row of buttons is another row. These buttons in the second row across the top of the interface are designed for formatting data in the report. Options available include changing font type and size, bold, italics, underline, money format, percentage, and others. Down the left side of Live Previewer is another set of buttons. These buttons handle adding other types of report formats, such as background and fill color, rectangles, lines, and other shapes to highlight the data produced by the report.
To the right and downward on Live Previewer are two sets of rulers, designed to show you where you are on the actual page of the report. Within the rulers is the actual report data, plus any added items from the template you used for the report. Along the bottom panel of the Live Previewer window there are three items. The first shows you the zoom magnification you are currently using to view the live report. The second and third tell your location on the current page of the report, horizontally and vertically, corresponding to the rulers that appear on Live Previewer.
Modifying Display of Report Data with Live Previewer
The working environment for Report Builder is highly customizable, allowing you to modify the appearance of a live report depending on your needs. The View menu has many different options at the bottom of the pull-down menu that designate which features of the Live Previewer will appear. Those that will appear display a check mark next to them, while those that won’t do not have a check mark. To eliminate the rulers from Live Previewer’s output window, for example, click on View | Rulers to remove the check mark, and thus the ruler, from the display.
Modifying display of report data is possible with formatting options presented in the second row of buttons on the Live Previewer display as well. To modify the formatting display of report data, first click on the cell you wish to modify in the body of the report within the Live Previewer. You will be able to tell that the cell is activated by the outline appearing around the cell. You can then make changes to font type and size using the Font and Point Size list boxes in the second row. You can add bold, italics, or underline by clicking the appropriate buttons, which stand to the right of the Font type and Size list boxes in the display.
You can change the text in each cell to be left-, center-, or right-justified by clicking on the appropriate buttons, which stand to the right of the special text format buttons. You can add a dollar sign, decimal places, percentage sign, and commas every three significant digits using the special numeric format buttons to the right of the justification buttons. You can change the appearance of your report with the use of the Fill Color, Line Color, and Text Color buttons appearing vertically along the left side of the Live Previewer interface as well.
Another way to change the display of report data in Live Previewer is done with the pull-down menus on the main Report Builder module. The format menu offers every option you have a button for in the second row of the Live Previewer. For example, if you want to make the data in a cell right-justified, you click on the cell once to activate it, then click on Format | Justify | Right menu option to set the cell to be right-justified. Your examination of the Format menu will show other formatting options that are possible as well.
TIP: You may find several questions on OCP Exam 5 that ask about the easiest way to modify layout when looking at a report in Live Previewer. Whether you think its true or not, make sure you understand that the OCP test writers think the easiest way to modify report format when looking at a report in the Live Previewer is always to make the change using Live Previewer! Be mindful of this principle when answering the OCP Exam 5 questions.
Modifying Position of Report Data with Live Previewer
You may want to change the location of data within the report in order to make it easier to understand, in addition to changing the display of data in the report as well. This functionality is possible with Live Previewer. One method available for changing the position of report data is to return to Report Wizard and reorder the column output in the Fields tab. If you want to add columns that are not currently part of the data definition for the report, you may have to modify the data definition as it appears in the Data Definition tab as well. You can also change what columns appear in the report or the order in which they are displayed by changing the SQL statement generating data for the query by clicking the Data tab and modifying the data-defining SQL statement. Or, you can change the columns appearing in the report by clicking the Fields tab and adding or removing columns in the data definition, or you can change the totals appearing in the report by clicking on the Labels tab and adding or removing items there.
Another method you may use for changing the positioning of report data is to modify the report style. For example, you may want to change a tabular report to a group-left report, or to a matrix report. These changes are made with Report Wizard, and alter the position of the data in the report. Alternately, you can modify the position of report data using the layout view of Report Builder. This activity is done by clicking on the View | Layout Model on the Report Builder main menu bar, bringing up the layout of the report in the layout model work area so that you can modify the layout of the report. You move the position of your data simply by clicking and holding an item, moving it, and releasing it with the mouse. Figure 24-2 demonstrates the appearance of your report using the layout model.
Figure 2: Report Builder layout model
Adding Page Numbers and Current Date to Reports
A simple enhancement to any report that helps identify when it ran and where you are within the report is the addition of page numbers and the current date. There are two ways to add both these items to your report. The first is with the Insert Date and Time and Insert Page Number buttons appearing in the top row of Live Previewer, toward the center of the row of buttons. The Insert Date and Time button has an arrow with a stopwatch and the number "31" on it, while the Insert Page Number button has an arrow with a page and the number "1" appearing on it. The second method for adding date/time and page number information to your report is to use the Insert | Date and Time and Insert | Page Number menu items in the main menu bar on Report Builder.
Focus now on adding the date and time information to your report. Clicking on the Insert Date and Time button or selecting the Insert | Date and Time menu item both lead you to the same interface, which has "Insert Date and Time" on the header bar at the top. Figure 24-3 shows that interface. At the top of the interface is a list box where you can select to place the inserted date and time information on the left, right, or in the center of the top or bottom of each report page. After selecting where to place your date/time information, you can select the format you want to display the date/time information in. You can select from the available formats in the text box on the center left of the Insert Date and Time interface, or click on the Custom button to create your own format. The Custom Date and Time interface will then appear, where you can enter your custom format in the text box at the top of the interface.
Figure 3: Insert Date and Time interface
Below the text box is an example line where Report Builder will show you interactively what your date/time information will look like based on the formats you select from the options listed in the big display on the interface. Figure 24-4 shows one example you can type into the Custom Date and Time interface and what Report Builder will display for the date and time you configured. When finished, click the OK button to return to the Insert Date and Time Interface. The custom format you just created will then be added automatically to the available formats in the text box on the Insert Date and Time interface. Click ok again on that interface to return to the Live Previewer to see the date/time information you added to your report.
Figure 4: Custom Date and Time interface
Adding page numbers is done in roughly the same way. First, either click on the Insert Page Number button or on the Insert | Page Number menu item. This act brings up the Insert Page Number interface, shown in Figure 24-5. Defining where the page number should appear is done the same way as shown previously for adding the date and time. Once complete, you can define how you want your page numbers to appear. They can either be the page number only, or the page number with the total pages added in as well. Select the page numbering format you want to appear on your reports by clicking on the appropriate radio button and, when complete, click OK. Your page numbering scheme selected will then appear on your report.
Figure 5: Insert Page Number interface
In this section, you will cover the following points on managing report templates:
This section covers managing report templates. The appearance of reports in your organization may be standardized easily with the use of templates. These objects can be used to encapsulate the definition of header and trailer pages for the report, corporate logos, and other visual formatting. The section will describe for you the template regions, default and override sections, modification of predefined report templates, and registering custom templates for use within Report Wizard.
Describing the Template Regions
Recall from last chapter that you can use Report Wizard to create new reports quickly and easily. Your reports can be enhanced visually with the use of templates. These templates allow you to add graphical logo displays, headings, and other items that make the data in your report more readable and understandable. They can also be used to give your reports a common appearance, based on organization, application, or any other set of criteria. You have the option of applying an existing template to your report with the use of Report Wizard automatically.
A template is divided into several regions, according to the information the template supplies about the report. These regions are body region, margin region, header region, and trailer region. The body region is where you define certain aspects of the report body such as report style, text formats, font size, color, and others. The margin region includes top, bottom, and side margins. The header page and trailer page regions denote what you may want to appear on cover and trailer pages on your report. These pages may be used to distinguish it from other printed items on the printer.
Your template may predefine other aspects of reports. Aspects of the template that are applied to the report include execution parameters, report triggers, PL/SQL code, and attached libraries. These facets of a report template are all shown as drill-down items within the report template on the Object Navigator module of Report Builder.
Using Object Navigator to Create and Modify Templates
To create a new template, click once on the Templates node in the Object Navigator module, then click the Create button in the Tool Palette. Once you see the new Templates node, click on the Property icon to the left of the node to view and modify the property sheet in your Property Palette. To modify an existing template, open the template and then click the Property icon to the left of the node to view and modify the property sheet in your Property Palette.
You can drill down into the Data Model node beneath your new or existing template in the Object Navigator to view the system and user parameters predefined for the template. You can also drill down into the Layout Model node of the new or existing template to predefine template items to be included in the various regions of your report. Those regions include the body, margin, trailer, and header. Drill down into the Body node now to see there are two further nodes, called Default and Override. These nodes represent the default and override attributes of the template, an important concept that will be explained shortly. Finally, drill down into the Triggers node to see the five report triggers you can predefine for your template. Figure 24-6 displays the Object Navigator with all the Templates nodes mentioned fully expanded for your better understanding.
Figure 6: Object Navigator with Template nodes displayed
Modifying Templates with Template Editor
A new feature for Developer 2.1 related to modification of templates is the Template Editor. Use of this feature is accomplished by first double-clicking on the Templates drill-down item in the Object Navigator to activate that drill-down item, then clicking on the Tools \ Template Editor Report Builder menu item to open the Template Editor. You should experiment with the Template Editor and an existing template that comes with the Developer/2000 release software by opening a demo template file. Template files have the extension .tdf and are found in the report30/admin/template subdirectory under your Oracle software home directory on the machine where Developer/2000 is installed. This tool in Report Builder is shown in Figure 24-7 with a sample template provided with the Oracle software called corp10.tdf.
TIP: Be sure you understand how to modify templates using the Property Palette for OCP! Though this content is important and will complement your understanding of modifying templates with the Property Palette and Object Navigator, your understanding of the Template Editor will not be tested.
Report Type list box
Open button
Save button
Zoom In button
Zoom Out button
Body button
Margin button
Header button
Trailer button
Figure 7: Template Editor interface in Report Builder
When you open the Template Editor, it will usually put you into the layout model of your template. Figure 24-7 shows the layout model of this sample template file provided by Oracle. Notice in that figure that there is a set of controls across the top of the interface. From left to right, they are as follows:
The Report Type list box identifies the type of report this template enforces. The next four buttons are Open, Save, Zoom In, and Zoom Out. The functionality these buttons provide on your template should be fairly self-explanatory, so you should skip over to the last four buttons: Body, Margin, Header, and Trailer. These buttons are where the action is when you are modifying your templates. The first button navigates you to the body template region. Here, you can modify the layout of data appearing in your template body by datatype. Notice that in the corp10.tdf you have three datatypes defined, CHAR, NUMBER, and DATE, as part of the body region of the layout model within the Template Editor. You can see more template regions by clicking other buttons. Try clicking the Margin button now to look at that region. The change you will see is shown in Figure 24-8.
Figure 8: Margin region shown in Template Editor
Within this perspective on the template using the Template Editor, you have several options for modifying your report template. You can easily see the header items included with this template as well, along with an item defined to display the date the report ran. Overall, however, you should notice Figure 24-8 closely resembles Figure 24-2. Hence, the explanations of different buttons available in the layout model of the Report Editor interface are also valid explanations for the layout model of the Template Editor, too.
Move on now to the header page region by clicking the Header button in the Template Editor. You will notice immediately that there is no header page developed for corp10.tdf. Thus, the display within Template Editor is empty. You still have the controls for changing data formats and the other options you had for your margins region view within the Template Editor interface. Finally, look at the trailer page region by clicking on the Trailer button to the right of the Header button. This view on the corp10.tdf template should look much the same as the header did—blank. Later in this section, we will talk about how to modify your templates using Template Editor.
Other regions where you can define items in your template include parameters. According to your view on the corp10.tdf template, you can define values for parameters in your template. The parameters you define fall into two categories: system and user. The system parameters include BACKGROUND, COPIES, CURRENCY, DECIMAL, DESFORMAT, DESNAME, DESTYPE, MODE, ORIENTATION, PRINTJOB, and THOUSANDS. You have a great deal of flexibility in defining user parameters as well.
Other template regions you may define include report triggers, which can fire at different times in the execution of the report. These times include before the parameter form is processed, after the parameter form is processed, before the report runs, between pages of the report, and after the report runs. You can define and associate PL/SQL program units and attached libraries with a template for automatic usage with your report as well. These other regions can all be found on the drill-down list under Templates in the Object Navigator.
TIP: There are a couple of questions about property value inheritance on the OCP exam. Make sure you review the topic of property value inheritance using the Report Builder online help before taking OCP.
Modifying Template Default and Override Attributes
When you first create a report in Report Builder, the tool will define certain properties for the report automatically. These items may include runtime parameter values, physical page size, report runtime mode, margin positions, header page content, and other things. When you apply a template to a report, Report Builder changes the features for your report by imposing template components to the individual report. The items that are applied include template regions such as parameters, page size, report execution mode, margins, header and trailer page content, and other items. To apply a template to a report, you first must have the report open. Using the Object Navigator, click on the Open button and then select the Tools | Report Wizard menu item. Select the Template tab to switch to that interface, and choose either a predefined template or browse for a template of your creation. Afterward, click the Finish button to apply the template.
Modifying Template Default Attributes
A template has certain items defined within it that are consistent across all report styles available, from tabular to group to matrix. These items are called default attributes or default sections for the report template, and they will appear in the report no matter what style you select for your report, so long as you apply the template to the report. Items that can be defined as default attributes in your report include the following, corresponding to the Template | Layout Model | Body | Default node on the Object Navigator module in Report Builder: Frames, Field Labels/Headings, Fields, Summary Labels, and Summaries.
Using Object Navigator to Define Template Default Attributes
Default attributes for a template are defined in two different ways. The first way is via the use of the Object Navigator drill-downs and the Property Palette. In the Object Navigator, drill down to the appropriate item listed in the bullet point above you wish to change. Then, click on the graphic icon next to the name. Thus, in the case of changing default attributes for summary labels of NUMBER datatype, click on Template | Layout Model | Body | Default | Summary Labels, and then click on the T icon next to the word "Number". You can look at Figure 24-6 to get a better idea.
After clicking on that icon, the Property Palette will appear to display the default attributes for summary labels of NUMBER datatype. You may then make your change. Say, for the sake of experimentation, you want to change the font of numbers displayed as summary data to blue, to contrast with the regular use of black in your report. At this point, you then click on the Text Color property. When you do so, you notice that a button with an ellipsis character appears to the right of the row. Click on that button, and a Colors interface will appear to assist you with the selection of your color. Figure 24-9 displays the Colors interface.
Figure 9: Colors interface on the Property Palette
After making your color selection in the Colors interface, click OK. Then, close the Property Palette and return to the Template Editor module. You should notice that your summary label for NUMBER datatypes now appears in blue, corresponding to your change. Now, since this is a default attribute, it will appear in reports of different styles. In the Template Editor module, use the Report style list box in the upper left-hand corner of the interface to change from default style to tabular style. The summary label for NUMBER datatypes appears in blue as well, as it will in other report styles selected such as group left and form-like. You have now defined a default attribute for your template.
Using Template Editor to Define Default Template Attributes
The second method for altering template default attributes is with the Template Editor. Remember, the Template Editor is part of Developer 2.1, so be sure you understand how to do this with the Object Navigator first. Before anything else, make sure that the style displayed in the Report style list box in the upper left-hand corner of the Template Editor interface reads "Default" so that you are modifying a default attribute. Recall the similarity between the Template Editor as shown in Figure 24-7 with the Report Editor as shown in Figure 24-2. Down the left margin on this editor are several buttons, each with a different function. Notice the last three buttons on the left margin of the Template Editor interface, the ones with the four-color boxes in them. In descending order, they are the Fill Color, Line Color, and Text Color buttons.
For the sake of experimentation, modify the summary label default attribute you just changed to blue using the Object Navigator and Property Palette to red using the Template Editor. Click on the summary label in the template body area once to activate the label, and then click on the Text Color button to show the Tear Off Palette of Colors available for this label. Figure 24-10 shows the Template Editor with the Tear Off Palette displayed as you make your changes to the default attributes of the template. Then, click on the color red within the palette, and your summary label for NUMBER datatypes is now red.
Figure 10: Modifying default attributes with Template Editor
Modifying Template Override Attributes with Object Navigator
Your report may also contain special attributes applicable to the report for only certain report styles. For example, there may be a special feature you want your template to contain that appears on matrix reports but not tabular reports. This component is called an override attribute. Items that can be defined as override attributes include the following sections listed under the Template | Layout Model | Body | Override drill-down item in the Object Navigator module of Report Builder: Tabular, Group left, Group Above, Form-Like, Mailing Label, Form Letter, Matrix, Matrix with Group. Under each of these items are the same sets of attributes you covered in the previous discussion on default attributes, namely frames, field labels and headings, fields, summary labels, and summaries. Override attributes for a template are defined in two ways: with the Object Navigator and Property Palette, or with the Template Editor.
Setting an override attribute on your report template with the Object Navigator and Property Palette is accomplished in the following way. In the Object Navigator, drill down to the appropriate item listed in the bullet point above you wish to change. For this example, assume you want to override the default attributes for summary labels on NUMBER datatypes in your group-left reports from red to green. You would first drill down to Template | Layout Model | Body | Override | Group Left | Section(1) | Summary Labels node, then click on the icon to invoke the Property Palette. Then, click on the row that reads "text color". Next to it is the word "red", for the color you defined as the default text color for summary labels in the template with the Template Editor. Click on the Ellipsis button to the right of the color definition, and the Colors interface appears. Select the color green from the interface and click OK. Repeat this operation for the second section of your group report template. When finished modifying override attributes for the report style, you can inspect the change you made for the override attributes by looking on the Template Editor, under the group-left style as indicated in the list box in the top left-hand corner of the Template Editor. The summary labels for NUMBER datatypes should now be green, as you have defined.
Modifying Template Override Attributes with Template Editor
The second method of defining an override attribute is to use the Template Editor module. Overall, the process is similar to defining default attributes using the Template Editor, but with the following differences. First, instead of ensuring that the style shown in the Report style list box in the upper left-hand corner of the Template Editor module shows "Default", you will want the specific report style for which you are defining an override attribute showing. In this case, it should show "Group Left". Activate the first summary label for NUMBER datatypes by clicking it. Then, click on the Text Color button appearing at the bottom left side of the Template Editor interface. The Tear Off Palette will then appear, allowing you to select the color you wish to change your text to on summary labels for numbers. Click the color, and the Template Editor will show the change.
TIP: If you use the Template Editor method to alter default or override attributes, Report Builder automatically drills down to the appropriate template item in the Object Navigator behind the scenes at the same time. After making your changes in the Template Editor, return to the Object Navigator, and you will see the change in place.
Modifying Predefined Report Templates
Oracle Developer/2000 supplies several report templates with its software release to help you get started with making your reports quickly. You may want to modify the templates that are distributed with the Oracle software for your own business purposes, however. To modify a predefined template, you must first open the template. Templates are found in the reports30/admin/template subdirectory under the Oracle home directory, and have the extension .tdf. Look in this directory on your own file system to see the template files.
To open a template, click on the File | Open menu item in the Report Builder main menu bar. The Open File interface will then appear, and you can select to see the files of type template definition by using the File Type list box at the bottom of this interface. Change to the appropriate directory containing templates on your system using the Look in list box available at the top of the interface. For this exercise, you should choose the conf2.tdf file by clicking its name in the Open File interface and then clicking Open. Once you have opened this template definition file, you can browse the various regions of it using the appropriate drill-down nodes in the Object Navigator or the Body, Margin, Header, and Trailer buttons in the layout model of the Template Editor.
Modifying Predefined Templates with Object Navigator
You are free to modify the predefined template as you wish. For example, you may want to add content for a header page that identifies the name of the company. You can review the appropriate discussions in the previous lesson on modifying templates on what nodes to click on in the Object Navigator module. Also, review the properties you must change in your property sheet using the Property Palette.
Modifying Predefined Templates with Template Editor
To do so, you first move to the header page in the layout model of your Template Editor by clicking on the Header button. Once there, you click on the Text button in the left margin to activate your cursor to draw a text box. The Text button is shown with a "T" on it in the layout model of the Template Editor. Once activating the cursor, draw a text box on the header page by moving the cursor somewhere on the header page, then clicking and holding your mouse until you see a rectangle of the desired size on the header page. When you are satisfied with the size of your text box, release your mouse button and type in whatever you like. Figure 24-11 shows what the header page in your Template Editor now looks like with text defined.
Figure 11: Modifying templates with Template Editor: header page
Once you’ve mastered simple modifications like adding text to your header and trailer pages, you should practice modification of fields in your template. You can modify the default and override attributes of various items in your predefined template as described in the previous discussion. You can add date, time, and page information as well from the margin region of your template by clicking the appropriate button from within the layout model of the template using the Template Editor as well.
In some situations, you may create new templates that you want to make available for easy inclusion and use on reports developed using Report Wizard. Recall in Chapter 22 there was some discussion about how to apply a template to a report. Within Report Wizard, there are three options for applying templates to your reports. The first is using a predefined template listed in the interface. The second is to browse for a template file in your file system. The third is to apply no template. Registering a custom template is the process of making templates of your creation available in that list interface so that you can select it easily from within Report Wizard. This task is accomplished in the following ways, depending on whether you run Developer/2000 on UNIX or Windows.
Registering your template happens in the following way. First, you open your user or global preferences file using a text editor. In Windows, the user preferences file is called cauprefs.ora and the global preferences file is called cagprefs.ora. You can find either of these files on the client machine running Developer/2000 under the Oracle software home directory on your machine (examples include c:\orawin95 or c:\orant for Windows). In UNIX, those files are both called prefs.ora; the only difference is that the user preferences file is located in your user home directory, while the global preferences file is located in the tools/admin subdirectory under the Oracle home directory.
When the user or global preferences file is opened, you then scroll through the file until you find the Reports.RepStyle_Template_Desc definition, where RepStyle is the report style for which your template is defined, such as Tabular, BreakAbove, FormLetter, and so on. You will be adding a description here for your own template to make it available for this type of report. If your template will be available for more than one type of report, then you must add your description to each appropriate area in your preferences file. The following code block shows a sample of what a preferences file contains as far as tabular template descriptions on your machine when you first install Developer/2000:
Reports.Tabular_Template_Desc =
("Corporate 1",
"Corporate 2",
"Confidential Heading",
"Confidential Background",
"Cyan Grid",
"Bright 1",
"NCA Grey",
"NCA Yellow",
"Corporate 1 Landscape",
"Confidential Heading Landscape",
"Confidential Background Landscape",
"Cyan Grid Landscape",
Draft,
"Character Mode")
If you do not find a block of code like the one shown above in your preferences file, then you should make the modification in your preferences file. Your addition should be something descriptive, and it should be in double quotes as the others are. After adding the description for each of the report styles you want your template to be applied toward, you must also add your template’s filename without the .tdf extension to the associated file. The following code block shows the associated Reports.RepStyle_Template_File definition for tabular reports from the preferences file:
Reports.Tabular_Template_File =
(corp1,
corp2,
conf1,
conf2,
gngd1,
brit1,
ncagry,
ncayel,
corp10,
conf10,
conf20,
gngd10,
draft,
char1)
TIP: Be sure to add new descriptions and files in the same order as the definition in your preferences file. For example, if you add new template descriptions at the end of the Report.RepStyle_Template_Desc item set, make sure you add their filenames at the end of the Report.RepStyle_Template_File also!
Once added to the description and filename lists, you then need to move or copy your template file in the appropriate directory. This directory is defined by your reports30_path environment variable defined for your machine, and should be set to the report30/admin/template subdirectory off your Oracle software home directory. Once these steps are complete, you should test to ensure you can apply the template to your reports using Report Wizard.
Adding a Template Image in Report Wizard (Optional)
You’ve probably heard the familiar pitch "for a few dollars more…" used by many salespeople. You can implement the Oracle Reports equivalent of this concept by adding an image from your template that will appear in Report Wizard. It’s one nice way to make your templates a little more professional. To do so, you must accomplish the following. For each report style you want the template used for, create a report and apply the template to it. Then, use a screen capture tool to capture a portion of each sample report (2"× 3" max.), and store the screen shot in bitmap format using the naming convention Repnamex.bmp. In this naming convention, Repname matches the filename specified in the Reports.RepStyle_Template_File list, while x identifies the report style, substituting x for the following characters: a for group above, f for form-like, g for matrix with group, l for group left, m for mailing label, r for form letter, t for tabular, x for matrix. After creating the bitmap file and naming it appropriately, copy it to the directory specified by the REPORTS30_PATH environment variable. Report Wizard will now display your bitmap file along with the description in the predefined template list, making it as easy for you to apply your template as it is to apply those distributed with Oracle software.
TIP: Don’t get too picky about trying to understand this for the OCP exam. As long as you know you make modifications to the cagprefs.ora file and some of the other high-level points made here, you should be fine.
In this section, you will cover the following points on report storage methods:
Understanding the use of Oracle Reports on multiple platforms in an application or system requires that you understand Oracle report storage methods. There are several methods used to store report-related files, some of which you’ve already seen. This section covers each of these report storage methods again in more detail. Report storage starts with report definition, so you will cover that area first. The report types and their portability between platforms and Oracle Reports executables will also be explained. A primer on what Oracle Reports executable components run which report file types will be included as well. Conversion between report storage types is a useful item to understand, and is required in some cases to use certain report types with certain files. Finally, the required tasks for upgrading report and printer definition files from Developer/2000 1.x to 2.0 will be covered.
Managing Storage of Report Definitions
Once created using Report Builder, reports are stored in report definition files. These file types are indicated with certain filename extensions. The following bullets explain each extension you may see used when creating and saving reports, along with the type of report the file stores and a small explanation of how the report was generated:
Other files used by Report Builder to support the processing of reports run in character mode include the following file types that don’t necessarily have a set filename extension applicable to them:
There are certain files used in different versions of Developer/2000 to handle printer definitions corresponding to your reports. Later in this section, there will be a discussion of printer definition files, their use, and their upgrade from 1.x of Developer/2000 to 2.0. Primarily, when you store report definitions, you will be storing files with the extension .rdf. Later, you may want to set these reports up to be run with Reports Runtime, which means you change the format from .rdf to .rep. The discussion of file formats and their portability appears shortly.
TIP: Make sure you know your .rdf, .rep, .rex, and .pll filename extensions and what they mean before taking the OCP exam!
Report Types and Their Portability
Given that you will most likely be developing a lot of canned reports to include in the applications you develop, you may be concerned about portability of your report definition files. For the most part, reports developed in Developer/2000 are portable across different platforms such as Windows NT and UNIX. However, this is not always the case. Several issues exist with report definition file types and their portability across platforms. Consider first the report definition file format, represented in your files with the .rdf filename extension. Files of this format are created in Report Builder whenever you create a new report. When you open your report using Report Builder for modification, this is the type of file you are modifying. This file format is stored as a binary as mentioned in the previous discussion, and is portable from one environment to another.
Now consider the report executable storage format indicated by the filename extension .rep. This file storage format is used for reports that are processed using Reports Runtime only. In other words, you cannot edit or modify a report in the executable format denoted by the .rep extension. This file format has some portability limitations as well. Although you can port these files from one environment to another for the most part without converting it, this fact is not always the case. Situations where the .rep file is not portable include those cases where the report contains compiled PL/SQL code. To port these files, you should first move the .rdf file to the other environment containing the PL/SQL code, then convert the .rdf file to .rep format. The next discussion will cover the use of Report Converter for these purposes as well.
Another type of file format you can consider is the report definition text file, denoted by the file extension .rex. These files are stored in plaintext, not binary format. Thus, they are highly portable between machines using the same character set, such as US7ASCII. You can use Report Converter to change the storage of a report from .rdf to .rex easily.
TIP: Both report definition binary and text files are fully portable. The report executable file is portable only if it does not contain compiled PL/SQL code.
Converting Reports to Different Storage Types
In situations where your report does not port easily to be run from one platform or environment to another, you may have to convert the report definition file from one format to another. You perform conversion of report files from one format to another using the Report Converter, available either as a menu option in Report Builder using File | Administration | Convert, or as a stand-alone application. To run the Report Converter stand-alone, use the r30conv executable. This tool allows you to accomplish many things in porting your reports from one environment to another. In some cases, you can create file formats using the Report Converter tool you would not be able to make using Report Builder. Figure 24-12 shows you the Report Converter tool in Oracle Reports.
Figure 12: Report Converter tool
There is an area of potential confusion related to your use of Report Converter. In the list containing Oracle Developer/2000 tools you access from the Start button in Windows, you may notice a tool called Report Compiler. This tool is the same as Report Converter. The tool both compiles reports and converts them from one format to another. What’s more, it is also the same tool you can access within Report Builder using the Program | Compile menu option. When you start the tool, however, notice that the window it runs in is titled Convert, and when accessed from the command line, the tool is called r30conv. Although this might seem confusing, it is important for you to memorize that Report Converter and Report Compiler are the same tool.
Using Report Converter on Reports
Report Converter is used in the following way to convert reports from one definition type to another. First, be sure "Reports" is shown in the Document Type list box, as shown in Figure 24-12. Then, you select the report type from which you are converting using the Source Type list box. Your options in the list box for this choice are Report Binary File (RDF), Report ASCII File (REX), or Report in Database. Below that list box is a text box where you define the actual report definition file you wish to convert. You must specify the absolute path and filename for the file you wish to convert. If you do not know this information, you may click the Browse button in the Report Converter interface and the Open File interface will help you find your reports. Next, you define your destination file type and filename using the Destination Type list box. Your options as they appear in this list box include Report in Database, Report Binary Run-only File (REP), Report Binary File (RDF), Report ASCII File (REX), and the Report Template Binary File (TDF). This last option is provided to assist with creating template definitions from existing report definitions. Finally, you specify a filename for your new report storage file. When complete, click the OK button and Report Converter will convert your report file.
TIP: You can specify the filename for your destination report file without defining the file definition type extension. Oracle adds that information automatically.
It Works for PL/SQL Libraries, Too
You can use Report Converter to convert your PL/SQL library files from one type to another in the same way as you would for report definition files. To do so, you must first choose the PL/SQL Library option from the Document Type list box near the top of the Report Converter interface. Then, choose the source file definition type from the Source Type list box. Your options include Library Binary File (PLL), Library ASCII File (PLD), and Library in Database. You then identify the location of your file containing PL/SQL code in the Source text box below the Source Type list box. After this task, you define the destination type you want Report Converter to change the source file to in the Destination Type list box list box. The options as they appear in the list box include Library Binary File (PLL), Library ASCII File (PLD), Library in Database, and one other option—Library Binary Run-only File (PLX). This file format is similar in concept and usage to the report executable file denoted by the .rep filename extension. Identify your destination filename in the Destination text box, click OK, and you’re there.
TIP: You can specify the filename for your destination PL/SQL Library without defining the file definition type extension. Oracle adds that information automatically.
Upgrading Report and Printer Definition Files
Upgrade from Developer/2000 from 1.x to 2.0 takes a bit of effort. One area where you may find yourself executing additional tasks to handle upgrades relates to report definition files and printer definition files. Some significant changes occurred in the way Oracle Reports handles printer definitions. Of course, there are changes in report definitions as well. The following discussion helps you upgrade from release 1.x to 2.0 of Developer/2000 with respect to Oracle Reports. Because your reports may contain and use attached PL/SQL libraries, the first topic covered is the conversion of PL/SQL code from PL/SQL 1.x to 2.3, the version of PL/SQL used on the Oracle Server release 7.3 and higher. It is now used in Developer/2000 2.0 and later as well. Developer/2000 1.x used PL/SQL 1.x.
TIP: You can run the Upgrade utility on reports or PL/SQL libraries only once. Even if you tell Report Converter to ignore all suggested conversions, the Upgrade utility cannot be run against the report again, preventing the utility from overwriting any manual changes made by the developer.
Your first task in upgrading your report definition files from 1.x to 2.0 is to upgrade any associated PL/SQL libraries. Because several keywords, features, and enhancements have been added to PL/SQL between these two versions, Report Builder has conversion utilities built in to ease the transition from one version to another. To upgrade a PL/SQL library in Report Builder interactively, simply open the library separately and then save it. The utility will process its upgrade changes automatically. If Report Builder has difficulty doing so, the PL/SQL Editor window will open, displaying both the error message and placing you in the appropriate location to make the changes.
An alternative to converting your PL/SQL programs in Report Builder is to convert them with Report Converter. Review Figure 24-12 to recognize Report Converter. Using the Report Converter GUI opened by either clicking the appropriate icon or Start option from Windows, you first define that you want to convert a PL/SQL library with the Document Type list box, then define your source PL/SQL library filename, then define the source type as .pll, or PL/SQL binary containing source or P-code. If this file is not in that format, a prior conversion to that format may also be necessary. The destination file format should be .pll or .plx, whichever you prefer. The destination filename can be whatever you prefer as well. Click OK to convert, and you’re on your way. Or, if you are a fan of the command line, you can issue the command shown in the following code block. Note that multiple libraries can be specified for the SOURCE parameter so long as each library is separated by a comma and all are enclosed in parentheses.
/home/oracle/> r30conv SOURCE=plsqllib.pll UPGRADE_PLSQL=yes
Once PL/SQL libraries used in your report definition are converted, you are ready to convert your report definition files as well. You will upgrade your reports in batch. The process for upgrading your report definitions can be done either with Report Converter in GUI mode or from the command line. To do so from GUI mode is done as follows. First, you open the Report Converter by double-clicking the appropriate icon or selecting the appropriate Start menu option. Next, select the Report document type from the appropriate list box in that interface. After that, choose your source type. For the destination type, make it the same as your source type. Either enter the filename for the report definition you want to upgrade using absolute pathnames or use the Browse button to find the file you want to convert. Then, enter a name for the report definition you want Report Converter to store your converted report definition in. You can browse your file system to find a destination filename as well.
TIP: A summary of conversion activities is stored in a log file called module.plg.
The alternative to running Report Converter GUI to upgrade your report definition files is to run Report Converter from the command line, passing in the appropriate command line parameters. The following code block displays the appropriate syntax. Note that multiple reports can be specified for the SOURCE parameter so long as each report is separated by a comma and all are enclosed in parentheses.
/home/oracle/> r30conv SOURCE=sales.rdf UPGRADE_PLSQL=yes
Printer definitions allow the printer to understand how to handle special character formatting and printing. You associate these files with your reports running on character mode platforms such as UNIX command line, VMS, and the like. A printer definition file is usually a file that stores printer definition information in plaintext format. The Developer/2000 software release comes with several printer definition files already bundled in that do not require upgrade. You may, however, have printer definition files on your machine specific to your installation that require upgrade from 1.x to 2.0. You do not require printer definition files for printing reports run in bitmap mode in Oracle Reports with Developer/2000 2.0. So long as you convert the report definition, when you run the bitmap report you can print it as though it was written in 2.0 format.
This chapter covers several areas of modifying and managing reports using Report Builder and other Oracle Reports executables. The topics you covered fall into modifying reports with Live Previewer, managing report templates, and report storage methods topic areas as tested on OCP Exam 5. The actual content of this chapter comprises about 18 percent of test questions you will find on OCP Exam 5.
The first area covered was modification of reports using Live Previewer. There are many fine points of your reports that cannot be modified using the Report Wizard, the tool in Report Builder that helps you assemble your reports quickly, covered last chapter. These items include display and position of data in your report and adding valuable information about when the report ran, such as date/time information.
Live Previewer consists of several components. There are buttons for switching between different report viewing modes and for modifying the format of data in the report. These controls are generally found in the horizontal toolbar of the Live Previewer interface. Additionally, there are buttons for adding rectangles, lines, and other shapes, as well as a set of buttons for altering background color, text color, and cell fill color on your report. These buttons are found on the Tool Palette of the Live Previewer interface. Additionally, there are rulers along the top and down the left side of the report viewing area, and displays to show you the current magnification of your report and your cursor’s location on the physical page of the report. The View menu present when Live Previewer is in the foreground on your Report Builder GUI allows you to determine which control items are actually viewable on your display.
Modification of actual report data is performed using the button controls in the toolbar and the tool palette of Live Previewer. The generic approach to this task is a two-step process. First, you activate the field in the report you wish to modify by clicking it once to highlight it. Then, you click on whatever button, list box, or other control in the Live Previewer margins that identifies the change you wish to make to the appearance of report data. Alternately, you can select the modification to report display format you wish from the choices under the Format menu. You can, for example, add currency formatting to your report after activating the cell you want the format to appear in, then clicking the Currency button or selecting the Format | Currency menu item appearing in Report Builder when Live Previewer is in the foreground. The chapter also identifies other format options available with these sets of controls.
Report data position can be modified as well. One way to do so is working with Live Previewer and Report Wizard in combination. If you wanted to change column order on your tabular report, for example, you would reenter the Report Wizard, click on the Fields tab, and add or remove columns from your report definition as you desire, then return to the Live Previewer to inspect the changes. You may also change the position or actual content of data in the report by changing the report style using that tab in the Report Wizard as well. Changes to format can be made with Live Previewer using the layout model of that tool as well. To change to the layout model of the report you created, you can either click on the Layout Model button on the left side in the horizontal toolbar of the Live Previewer interface or by choosing the View | Layout Model menu item while Live Previewer is in the foreground.
Another activity you need to understand for OCP related to Live Previewer is how to add date/time information and page numbering to your reports. Two buttons exist in Live Previewer for this purpose: the Insert Date and Time button and the Insert Page Number button. Clicking on each brings up a special interface to help you define where to place the date/time or page numbering information. The use of each interface is covered in the chapter content. Alternately, you may open the interfaces used to place date/time or page numbering information with the Insert | Date and Time or Insert | Page Number menu options in Report Builder. When adding date and time information to your report, there are some formatting conventions you need to understand, that were covered in the chapter. There will be some review of these formats in the Two-Minute Drill following this Chapter Summary as well.
Managing report templates is the next area covered in this chapter. The first area covered for this section was describing the areas or regions of a template. A template has several components, or regions. These include the template body region, margins, header pages, and trailer pages. You can define template defaults for each of these items quickly and easily. Each region has its own items you may define default information for. The body region of the template is where you can define formatting options for each different style of report you plan to apply your template on, such as text format, font size, color, and others. You may also want to define specific items to go in your report margins such as graphics and logos to appear on your organization’s reports to make for uniformity in style. These items are added or altered by defining content on your template margin region. You may want specific information to appear on the header or trailer page of your report as well, and these items are possible with the definition of template header and trailer regions. You can define default parameters for use when your report executes within your template, or special events to occur at various times during your report execution with the use of report triggers defined in your template. Template definition files in your file system are denoted with a filename extension, usually .tdf.
For each style of report you may apply your template to, there are attributes or sections of the template that will enforce certain formatting aspects on that report style. The attributes your template imposes can be the same across all report styles you may apply your template to, or there might be attributes your template applies to, say, tabular reports but not to form letter reports. A template attribute that gets blanket application to reports regardless of the style of report you attach the template to is called a default attribute, while a template attribute that gets applied to one report style but not another is called an override attribute.
Default attributes are defined in two ways: using the Object Navigator and Property Palette modules of Report Builder or using the Template Editor. The first method requires you to drill down to the appropriate item on your template the Object Navigator, Template | Layout Model | Body | Default. In this area of your report template, there are several attributes that can be defined. Click on the icon shown next to any of them and the Property Palette appears to help you define the default values for that attribute. Alternately, for Developer 2.1 you may want to simply open the Template Editor on your template and modify the value for the attribute using the buttons and controls in the margins on the Template Editor or with the options listed under the Format menu. If you are going to use the Template Editor to change your default attributes, however, ensure that your report style list box in the upper left-hand corner of the Template Editor interface says "Default" so that you know you are modifying a default attribute for your template.
Modifying override attributes occurs in the same ways as the modification of default attributes, either with the Object Navigator and Property Palette or with the Template Editor. To modify an override attribute, you first drill down to the appropriate attribute you wish to change on your template in the Object Navigator, Template | Layout Model | Body | Override. Then, select the type of report you wish to define override attributes for from the set of reports appearing under this drill-down, such as Tabular, Group Above, or Matrix. After that, you drill down into the actual attribute you wish to define for this report only, click on the icon next to that attribute to invoke the Property Palette for that attribute, change your value for that attribute, and close. Alternately, for Developer 2.1 you can use the Template Editor to modify the override attribute. First, select the type of report you want to define override attributes for from the list box in the upper left-hand corner of the Template Editor. Then, define the override attributes either with the appropriate combination of format button controls or choices from the Format menu.
Rather than starting from scratch on your report, you may simply want to modify predefined report templates that come shipped with the Oracle Reports software distribution on the Developer/2000 software release. As stated, the template definition file will have the file extension .tdf to help you identify it. You can find the predefined templates that come with your software in the reports30/admin/template subdirectory under the Oracle home directory.
If you create a new template, and you want the template to be available using Report Wizard, you must do the following things. First, you must add the filename and description for your template to your user or global preferences file. This is done outside of Oracle Reports using a text editor. There are two definitions you need to look for and modify: Reports.RepStyle_Template_Desc and Reports.RepStyle_Template_File. RepStyle gets replaced by the name of the report style you want your template allowed to be applied to in Report Wizard, such as Tabular, FormLetter, BreakAbove, and others. You must add an item to each, where the first contains short descriptions of your report template and the second contains the report filename sans the .tdf filename extension. Make sure you place the description and the corresponding filename in the same position on the list for each definition. For example, if you put your new template description at the end of that list, then you must place your template filename at the end of that list as well. When all descriptions and filenames are added, you may want to add a bitmap image of your report template in use on a report to be displayed in Report Wizard, but this is purely optional. Finally, you must move the template definition file (and associated bitmap images if you set any up) to the appropriate directory on your file system as defined by the reports30_path environment variable on your machine.
The final subject area covered in this chapter is report storage methods. You covered the different report definitions and how they are stored, the different report definition types and their portability, conversion of reports from one storage type to another, and upgrade of report and printer definition files. By far the most common report storage format is the report definition file format, denoted for your files with the filename extension .rdf. This is a binary format file that you can both modify and run either in bitmap or character mode. PL/SQL in your report will be stored either as source or compiled code blocks. This file format type is highly portable. You may store your reports in .rep format as well, the report executable file format. Your .rep file cannot be modified in Report Builder. This is a binary file format that is ready to run using Reports Runtime. This file format is portable so long as the report doesn’t contain compiled PL/SQL blocks. The final report storage format is the .rex format. This format is not created directly by Report Builder, but rather you convert your .rdf or .rep files to this format using Report Converter, another executable available in Oracle Reports. The .rex format is a text file format containing your report definitions that is as portable as any text file from one environment to another.
To convert reports or PL/SQL libraries from one form to another, you must use the Report Converter tool. You first define to document type as being report or PL/SQL library in the appropriate text box of the Report Converter interface, then define the source filename and type using the next two text boxes in the interface. After that, you define the destination storage type and filename (without any extension) using the last two text boxes in the interface. Click OK on the interface to begin the conversion process.
The final topic in the chapter on report storage is the process you may use to upgrade report and printer definition files from 1.x to 2.0. You can do this for .rdf files by simply opening the old file and saving it, as there is an Upgrade utility built into the software release. To upgrade your .rep files that contain attached PL/SQL libraries, you must first upgrade the attached libraries, then the report. Report Converter is used heavily in this process. A log of conversion activities is written to a file called module.plg during each of these processes. An alternative to use of the Report Converter GUI is running it from the command line. Parameters appropriate in this situation include SOURCE to define the source file you would like to upgrade and UPGRADE_PLSQL, which should be set to yes.