Chapter 25

Enhancing Data Content in Report Builder

In this chapter, you will cover the following areas of enhancing reports in Report Builder:

• Using the Data Model to create queries and groups
• Using the Data Model to create columns

The data in a report is what gives the report content. But raw data is useless unless the report developer gives the data meaning somehow. The purpose of the Data Model is to help you add meaning to your data. The Data Model displays your report queries, which extract information from the Oracle database for use in your reports. It also displays the columns of your reports in groups to organize the report columns. Both of these items are displayed pictorially in the Data Model. This chapter will cover your use of the Data Model to define and modify queries, groups, and columns in your report. Every report style has groups, but each report style may manipulate the groups differently. This chapter covers elements comprising 15 percent of OCP Exam 5 content.

In this section, you will cover the following points on using the Data Model to enhance reports by creating queries and groups:

• Data Model objects and their relationships
• Creating groups to modify report hierarchy
• Changing order of data in groups
• Using group filters to eliminate data from reports
• Creating data links to link data from different queries

Everyone wants reports in an organization. Analysts want to see information to make recommendations, and managers want it to make decisions. Unfortunately, everyone wants to see it in polished form, which is usually not the way it is stored in an Oracle database. When developing your reports, it is often helpful to look at data in the report in some form other than SQL statements, because, although the SQL statement assembles the data you want out of normalized databases, you may want to have a visual representation of the data you create reports from. The Data Model feature of Report Builder allows you to view the information included in your report using visual features. This section will cover the use of Data Model to show data relationships in your report. This section identifies the objects you will see in Data Model, including queries, groups, summary columns, and several others, and instructs you on how to modify each of these elements to add meaning to the overall report.

Your examination of the Data Model view on your report will show you there are several items Data Model displays. These items include system parameters, user parameters, queries, groups, formula columns, summary columns, placeholder columns, and data links. Incidentally, all of these items appear under the Reports | your_report_name | Data Model node in the Object Navigator module within Report Builder. Note that each report may not have every item defined within the Data Model for that object. Within Report Builder, you can see a pictorial display of the Data Model objects by clicking the Data Model button in Report Editor, the second button in the top left-hand corner of that tool. Figure 25-1 shows you the items under the Data Model node in the Object Navigator, while Figure 25-2 displays the Data Model view on your report’s data definition within the Report Editor.

Summary column

To understand the rest of this chapter, be sure you understand the use of each object available in the Data Model. The following subsections should help you understand each component you will find in the Data Model, what each component is used for, and their relationships with one another. Bear in mind another crucial fact about objects in your Data Model appearing in your report as part of Figure 25-2. Most have had their labels altered for readability. The Property Palette is used to change labels for Data Model objects. Simply double-click on the little icon just in front of an object name and the Property Palette will appear for that object. Oracle Reports generates labels automatically for every object appearing in your reports. In each of the following lessons, pay attention to the format for the label generated automatically for each object.

System parameters in the Data Model are parameters created by Report Builder that accept values at the time the report is run to define how your report will run. Some system parameters include BACKGROUND, COPIES, CURRENCY, DECIMAL, DESFORMAT, DESNAME, DESTYPE, MODE, ORIENTATION, THOUSANDS, and PRINT JOB. User parameters are those parameters in the Data Model that you can create for your report. You can define an initial value and a List of Values (LOV) for your user parameters. System and user parameters do not appear in the Data Model view of your report. Instead, you examine and change their values using the Property Palette.

These items in the Data Model are the data definitions that your report will capture and present on paper. In Figure 25-2, the query is shown as the small box identified with the label ALL_TABLES Query. The automatically generated label for queries is Q_n, where n is a number generated for each query in your report, starting with 1. You have already been exposed to methods for defining your report query using the Report Wizard—manually, import from file, or Query Builder. With the Data Model view on your report, you can generate queries using another method as well—the Data Wizard. This tool walks you through the critical steps for defining the information you want to retrieve from Oracle for this report.

These items in the Data Model are objects used to store columns displayed in your report. Each of the boxes in the Data Model that contain columns from your data definition are groups. A report may have one or more groups, depending on how you are grouping the column output from the query. For example, a tabular report may have only one group, which contains all columns defined as output for the report. On the other hand, a group-left report may have three or more groups, one for each of the levels of grouping you want to enforce on your query.

In the example presented in Figure 25-2, you have a data definition that obtains data from the ALL_TABLES Oracle database dictionary view, grouping that data by schema owner and by whether that table is cached. There are several groups displayed in that figure, each containing some columns. Each of these groups has a label in the figure, such as G_cache, G_owner, and so on. The automatically generated label for groups in your Data Model view is actually G_COLNAME, where COLNAME is the name of the first column in the list of columns appearing for the group. For this example, you see the TABLE_NAME, TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, and PCT_INCREASE columns appearing in the third, or main, group. Each group stores data from different grouping levels in a hierarchical way, more about which will be explained later.

These fields in the Data Model allow you to define new columns in your report output that perform a calculation on other data columns in the report. Creating formula columns requires you to determine what group the new column will be added to, actually adding the new column, and defining the process or formula Reports will use to calculate the new column using PL/SQL. There are no examples of a formula column in Figure 25-2, but later figures will show them with labels like formula column group level. The automatically generated label for these columns in Oracle Reports will have the format CF_n, where n is a number starting with 1 to uniquely identify the formula column.

Alternately, you can define formula columns at the report level that appear in separate objects from groups. The automatically generated label format for the report-level formula column is the same as that used for group-level formula columns. The steps involved in this process will be identified later in the chapter. This functionality in an Oracle report is roughly akin in concept to executing a SQL statement such as the sample code provided in the following code block:

SQL> SELECT empid, lastname, firstname,
2> salary, salary*1.08 AS RAISE
2> FROM EMPLOYEE;

These items in the Data Model perform aggregate calculations on data in other columns, such as sum( ) or avg( ) operations. These columns should not be confused with formula columns, which perform a calculation on a column value for every row of data in the output or the report. The output from a SQL statement similar in concept to the one provided in the code block above illustrates a calculation on a column value for every row of data in the data selected. In contrast, your summary column is a calculation performed at once for a particular column on every row of output.

Recall from the discussion of using Report Wizard in Chapter 22 that there are several different types of summaries available in Oracle Reports, similar in concept to group functions sum( ), count( ), avg( ), min( ), or max( ). Other options offered through manual configuration using the Data Model view in Report Builder to create a summary column and then editing that column in the Property Palette include first, last, standard deviation and variance. For the report Data Model shown in Figure 25-2, notice the small box off to the left on the model for this report. That small box is where the summary column is defined.

Summary columns can be defined at the report level and at the group level. At the report level, they appear as separate boxes from the groups and queries of the report. One is shown in Figure 25-2 with a label generated from Report Wizard. That label is CountownerPerReport. The automatically generated label for these columns in the Data Model view is CS_n, where n is a number from 1 up. At the group level, the summary column will appear as part of the group, along with other columns in that group. The label for this object is automatically generated in the same way as those labels for the report-level summary columns.

These columns in your Data Model view define a location where Oracle Reports will populate a report-wide or group-wide value of your choosing. Placeholder columns are similar to formula columns in that you can calculate the value in your placeholder column with a formula using PL/SQL code. Alternately, you use a special set of triggers built into Oracle Reports that fire at predefined times in the report execution to write a value to a placeholder column. There are no examples of placeholder columns in Figure 25-2, but later figures will show data models that have placeholder columns in them. Placeholder columns have the automatically generated label CP_n, where n is a number starting with 1 to uniquely identify the column.

Finally, these items in your report are means to draw together multiple queries designed to obtain data from the database in a master/detail format. When links are used, for each iteration of the master group, the detail query is executed. Three types of links exist in Report Builder, depending on the way they are created. The first is a query-to-query link, the next is a group-to-group link, and the last is a column-to-column link. Links are represented as a line between two queries with a special label appearing in the query Data Model object. See the discussion on linking queries that appears later in this section for more information and examples.

As stated, every report contains at least one group, called either the main or default group for the report. For example, a tabular report contains a group that simply lists all columns in the report output. However, many types of reports will contain more than one group. Some of the reports that contain multiple groups include group left, group above, and matrix with group reports. Each of these types of reports uses groups to define report hierarchy. What does this mean? Take another look at the Data Model contents for your group-left report in Figure 25-2. The image clearly suggests a hierarchy of report data content.

At the top of that hierarchy is the query itself, which drives the entire data definition for the report, without which your report would be a blank sheet. Under that query is the first group, containing one column for the schema owner of tables in the ALL_TABLES dictionary view. This group is considered the level-1 group in the hierarchy of returned data in the report. The data in your report gets broken out by every unique value for every level in the hierarchy, thus giving your report a break on each schema owner with tables in the database. Below that group is another group, the level-2 group, storing the CACHE column from your dictionary view. At the bottom of the list is another group containing all other columns appearing in the report.

You can create groups to manage and alter the hierarchy of data appearing in group reports. Say, for example, that you want to add a new column to the hierarchy of your group-left report using data from the ALL_TABLES view. For this new level in the hierarchy, you want to group tables listed according to the tablespace in the Oracle database in which the table is stored.

Adding this third level to the hierarchy is accomplished either through the use of Data Model alone or with the use of the Report Wizard. First, you will cover the use of Report Wizard to add a third level of hierarchy on your report for breaks on TABLESPACE_NAME. From the Data Model view on your existing report in Report Editor, click on the Tools | Report Wizard menu item in the main menu of Report Builder to open that tool. From there, click on the Groups tab, appearing third over from the left whenever the report style selected for operations is a group report of some kind. If you don’t see the Groups tab, then you are probably not working with a group-left, group-above, or matrix-with-group report. Since use of Report Wizard in Chapter 22 focused mainly on its use for generating tabular reports, a reprisal of Report Wizard showing the Groups tab is shown here in Figure 25-3 to help you understand the points made here.

Select the TABLESPACE_NAME column you would like to define as the third hierarchical level from the left window in the Groups tab, and add it to the right window with the Right Arrow button appearing between the two windows above the Left Arrow button. The TABLESPACE_NAME column then appears in the second window on the right side of the Groups tab under the level-3 header. Then, click the Finish button and log in to the Oracle database to allow Report Builder to generate your report, showing the output in the Live Previewer. When your report has been successfully altered, return to the Data Model. You should now see a new level-3 group appearing between the level-2 group and the main group of columns in your report. Figure 25-4 shows what you should see in your Data Model view of the report.

The second way to execute this task is within the Data Model itself. Take another look at Figure 25-4 and locate the new level-3 hierarchy for grouping data you created using the TABLESPACE_NAME column. When you locate it, point to it with your mouse and click and drag it back to the main group level of your report. When you release the mouse button, the TABLESPACE_NAME column will be returned to the bottom of the main group. Click on the now-empty level-3 group once to activate it and then press the Delete key on your keyboard. Now, point to the TABLESPACE_NAME column again and click and drag it off the main group. When you are off the main group and between the level-2 group and the main group, release your mouse button. Data Model will create a new level-3 group to store the TABLESPACE_NAME column as it looked in Figure 25-4 before you made these changes. Manually creating hierarchies has a restriction in that you can only drag one level up or down at one time.

What does changing order of data in groups mean? Actually, it can mean several things, from changing the order of data in a column, which is done with the Break Order property in the Property Palette, to changing the order of columns in your groups on the Data Model. Within any group of your report containing multiple columns, you may want to change the order of the columns in your group. This task is accomplished in one of two ways, either using the Data Model by itself or with the use of Report Wizard. First, look at the group you want to reorder columns in within the Data Model view of your report in Report Editor. For this lesson, you will modify the main group of your report developed thus far using the ALL_TABLES view. Take note of the current column order in the Data Model, which lists data columns you select from the ALL_TABLES view in your data definition statement for use in your report. In this case, your data definition statement appears in the following code block and corresponds directly to the fields displayed in your report.

select OWNER, CACHE, TABLESPACE_NAME, TABLE_NAME,
INITIAL_EXTENT, NEXT_EXTENT, PCT_INCREASE
from ALL_TABLES;

Changing order of data in groups in your Data Model can be accomplished either directly in the Data Model or using Report Wizard. Note that it’s really difficult to use Data Model by itself to change column order in groups if you select data from Oracle in your data definition columns that you don’t display in the report. Although you are not required to ensure your report’s data definition corresponds exactly to the fields you will display from the Oracle database (because you can limit displayed data with Report Wizard), it is advisable to do so because your Data Model groups show everything in the data definition, not just the fields displayed. For select * from tbl_name statements, your list of columns in the main report group won’t correspond to your displayed fields, and you may have to refer back and forth between the Data Model and Report Wizard when changing the order of data in groups if you want to use Data Model.

First, attempt to change column order in your report with Report Wizard. Open Report Wizard from the Data Model now. Say that you want to put the TABLE_NAME column last in the group. From within the Data Model view of your report using the Report Editor, open the Report Wizard and click on the Fields tab. This tab shows the fields in your report. Figure 25-5 shows the contents of the Fields tab within Report Wizard containing a list of all your columns in the ALL_TABLES report.

Recall which columns are part of your main group level in the Data Model view: TABLE_NAME, INITIAL_EXTENT, NEXT_EXTENT, and PCT_INCREASE. Remove those columns from the Displayed Fields window by clicking on the column name once to highlight, and then clicking the Left Arrow button at the bottom between the Available Fields and Displayed Fields window. Or, to remove all columns and start ordering from scratch, click the Left Double Arrow button between the Available Fields and Displayed Fields windows to remove all fields from display. Then, add the fields again by highlighting each field in turn and clicking the Right Arrow button between at the top of the Available Fields and Displayed Fields windows. You can drop and drag columns in the Report Wizard as well. Once complete, click the Finish button in the Report Wizard interface and Oracle Reports will generate a new version of your ALL_TABLES report, showing the output in the Live Previewer with your column order changed in the report output. If you want, then click on the Data Model button in Report Editor and you will see the columns in your group in the new order you identified.

You can change column order in your group within the Data Model as well simply by clicking and dragging the column name from one location to another within a group, or between different groups. This method is a little tricky, however, because there is a tendency for the Layout Model view and the Data Model view of your report to get out of sync when you start moving columns and groups around in the Data Model. Your best bet if you start to get into trouble is to open the Report Wizard on your report and make whatever changes in the Groups or Fields tabs you need to, then click the Finish button and let Report Builder do its thing to fix your report. Furthermore, even if you change order on columns in your Data Model group strictly within Data Model, you won’t ever see the changes in field order on your report Live Previewer view unless you open Report Wizard and click the Finish button for your report.

The Break Order property specifies the order to display column values in your Oracle report. It applies only to columns that identify distinct values of user-created break groups created by you in the Data Model view of the report. The order of column values in a default or main group is handled within the data definition by the order by clause of the SQL select statement. For column values in user-created groups, you would use Break Order to specify how Reports will order values displayed in the break column. You can specify Ascending, Descending, or None as values for the Break Order property in the property sheet for your columns. This is accomplished with the use of the Property Palette. The default value is Ascending, and it is required if the Set Break Order property is set to Yes for columns.

The mention of order by gives rise to some considerations you should use when specifying order in your reports. The Break Order property will take precedence over the order by clause in all groups except for the main or default group of the report. Consider another example, using the Australian Outback database from earlier in the text. Suppose that you created a group-left report with KGROO and JOEY. In the report query, you have the order by KGROO clause, where you also specify Break Order Descending for the KGROO column. Although you specified Ascending order (the default) in your order by clause, the listing of KGROO data is in descending order, as you specified in your Break Order property. The output of your query might appear similar to the following block:

KGROO JOEY
------ ----------
FLUMPY SKIPPY
KANGA
DUMPY RED
SUPIE
ROO
FORD
ABE MILLER
CLARK
KING
HOPPY
FLOPPY

In addition to use of Break Order being disallowed on the default or main report group, note there are some other restrictions to use of Break Order. If you use Break Order in one hierarchical group, you must use it in every group, though you needn’t set it the same way (Ascending or Descending) for all groups. Break Order also cannot be used on Oracle database columns of LONG or LONG RAW datatype. Break Order also only affects the ordering of the break column, and will not change order of columns in the break group. For example, suppose that you break on KGROO and for each kangaroo you list offspring via the JOEY column. If the Break Order is Ascending, then kangaroos are printed in ascending order, but the names of offspring list in the order specified by the query. In other words, to change the order of JOEYs for each kangaroo, you need an order by clause in your select statement. Other restrictions are that a summary column cannot be a break column and cannot have Break Order set, and a formula column that depends upon a summary column cannot be a break column and cannot have Break Order set.

You may not always want to show all the data available in your database as it relates to your reporting needs. There are many ways to limit the data returned in your report. One way is to do so is at the group level using filters. A filter is a predefined operation or a PL/SQL block allowing you to limit the data returned by columns in your group. There are two predefined group filters available to eliminate data from reports. These filters can eliminate the first n records in a group or the last n records in a group.

To use the two predefined group filters to eliminate data from a group, execute the following tasks. Using the Data Model view in Report Editor on the main group in your report, double-click on the header label of the group. This action opens the Property Palette for the group. There are several items in the Property Palette that correspond to groups, one with the title Groups. Under that item there should be a property that is called Filters. Click on the value for that property, which at this time should be None. A list box will be created, where you can click on the drop-down button to see all list items. Choose either the First or Last options on this list.

A new item will appear under the Filters property called Number of Records. You can enter whatever positive integer you wish corresponding to that property. Figure 25-6 shows the Property Palette as it will appear if you want to filter out all but the first five records to show for this group. You can then switch views in the Report Editor to the Live Previewer and see the effects of your filter in action. Return to the Data Model view on your report in the Report Editor now. Notice first there is a change in the color of the dot at the end of the line leading from the next-highest group level to the main level. This change is meant to indicate that a filter exists on the group.

The third type of filter is a user-defined filter, consisting of a PL/SQL block that tests whether the record from the group will appear in the report. If the block returns TRUE, then the record will appear in the report, if the block returns FALSE, the record will not. There are three different ways to define a group filter, two of which use the Data Model, Property Palette, and PL/SQL Editor. The second uses only the Data Model and the PL/SQL Editor. The final option uses the Object Navigator and PL/SQL Editor modules.

Return now to the Group item in the Property Palette by double-clicking on the header area for the main group again. A third option exists in the drop-down list for the Filter property, called PL/SQL. After defining the PL/SQL filter value for this property, a new property appears below it called PL/SQL Filter. A button appears immediately to the right of this property when you click on the property name. Click the button, and the Program Unit Editor will appear with template information to help you define your PL/SQL code for the group filter. Figure 25-7 demonstrates the contents of the PL/SQL Editor for a group filter that only shows records for tables where the value in INITIAL_EXTENT is greater than 1,048,576. Once complete, click the Compile button, and when your PL/SQL block is successfully compiled, click the Close button.

There are two other ways to open the PL/SQL Editor within Report Builder. The first is with the Report Builder main menu. With the Data Model view of your report open and with the Report Editor module in the foreground, click on the group you want to define a filter on once to activate it and then choose the Program | PL/SQL Editor menu item. You will then see either your existing filter on this group or the template for developing a new filter. The other alternative is to drill down through the Reports | ALL_TABLES | Data Model | Groups items to the main group in the Object Navigator module. Notice a little gray dot to the left of the group name in the Object Navigator. If you already have a filter defined for the group, that gray dot will have a small "p" in it. Double-click on the little dot next to the group name to invoke the PL/SQL Editor module. Once invoked, your actions to create or modify your group filter are the same as before.

In some cases, you may want to have your report display data returned from multiple queries. For example, you may want the report to select certain data from one part of the database, then use that result set as master records to drive a detail query to pull data based on that master set of records. To do this task, you must create a second query for your report and then tie the two queries in the report together by means of a data link.

Recall from earlier in the discussion you learned that a data link helps you to put multiple queries together in a report. You can create a data link in a report that has multiple queries that operate on intermediate forms of the data that will eventually be returned by the report by first clicking on the Data Link tool button in the Data Model view to activate the tool, moving your pointer into the Data Model view on the report, and clicking and holding on one of the specific areas in the parent or master query, then moving over to the appropriate area of the child or detail query, then releasing your mouse button to draw the link from the parent to the child.

There are three different types of data links between two different queries in your Data Model view on the report. The first is query to query, the second is group to group, and the third is column to column. The type of data link you want to define will influence how you actually draw the link from one query to another. An explanation of each type of data link, along with instructions on how to draw the link, is now given.

This type of data link uses data returned from the master or parent query to drive the child or detail query. The link is made at the query level by drawing the data link from the master query to the child query. Query-to-query data links can only be drawn when a foreign key in the table containing columns from the child group references the primary key in table of the parent group.

This type of data link uses data in the parent or master group to obtain data in the detail group. The link is made at the group level by drawing the data link from a group in the master query to a group in the detail query. Be careful not to touch the columns in the master or detail group when drawing group-to-group data links, or you will wind up drawing the next type of data link, a column-to-column data link. In order to draw a group-to-group data link, you must have common columns shared between the group.

This type of data link uses data in the master column to join with data obtained in the detail column. The link is made from a column in a master group to the same column in a group of the child query. In order to make this link, there must be a column shared between the two queries.

It can be difficult to distinguish one type of link from another once the link is drawn. Sometimes the properties of the data link can help you do so. This lesson covers each of the properties of data links, describing the properties, their alternative values, and their usage on the report. The following are the properties of a data link that will appear in the Property Palette for query-to-query, group-to-group, or column-to-column data links.

This property defines the SQL statement clause that defines how the data from one query will relate the master group to the child query. There are three options, where, having, and start with. These three options relate to the original discussion of SQL statements that was covered in Unit I of this text. Of important note is the fact that your use of start with or having in the data link implies the detail query must contain a connect by or group by clause in its data definition, respectively.

This property defines the comparison operation between master and detail column in a data link. Valid values are =, <, >, <=, >=, <>, like, or not like. The meanings of these comparison operations were covered in Unit I. The default value for the comparison operation defined in the Condition property is =.

This is the read-only group from the master query that is defined as such in your data link, whose data is read as the driver for the child query. The value for this property is a label corresponding to the name of the parent group.

This is the read-only column from the master query that relates to the column in the detail query of the same name and data content. It can be a database, summary, or formula column.

This is the read-only detail query in your data link. There is a mandatory one-to-one relationship between detail queries and their master groups. In other words, no detail query can have more than one master group linked to it.

This is the detail column in the database link that relates to a read-only master column. These are always database columns, and should not contain lexical references. A lexical reference is a placeholder in a SQL statement that allows you to substitute a value dynamically to determine which records your query collects. An example of a lexical reference in a SQL statement is select * from EXCH_RATE where TO_CURCY = ‘&TO_CURCY’. The key indicator of a lexical reference is the ampersand. More about lexical references appears in Unit I.

In this section, you will cover the following points about enhancing reports using the Data Model to create columns:

• Identifying Data Model columns
• Using database columns to display file contents
• Creating and using formula columns
• Creating and using summary columns
• Creating and populating placeholder columns

In addition to the data columns you draw from Oracle tables to populate your reports, Oracle Reports allows you to create several other columns. These reporting columns help you to add meaning to your reports through several ways. Some of these ways include column summaries like counts, totals, or other column group functions, which you have already seen in use from your mastery of Report Wizard. This section will cover explaining the different types of columns you may have in your report, when to use them, and most importantly, how to use them.

Some mention and brief explanation of each type of column available in the Data Model view on your report has already been made. To reiterate, several types of columns can appear in your Oracle report. They include database columns, summary columns, formula columns, and placeholder columns. These items have already been introduced earlier in the previous section. However, for reinforcement, they appear here as well. The following bullet list explains each type of column and the use for each. Figure 25-8 shows a Data Model view on a sample report that uses database columns, summary columns, placeholder columns, and formula columns. This figure will help you identify what these items look like within the Data Model view in Report Builder. Remember, most of the labels for these objects have been changed for readability.

This is a column storing information that came from the database. Your report will contain predominantly these types of columns. Database columns will appear in every group, usually. It is a column in the report as much as it is a column in your Data Model view of that report. Within the Data Model view displayed in Figure 25-8, database columns are shown as part of each group, with the column name as it appears in the database written into the group and a small triangle next to the column name.

This is not so much a column in your report as it is a column in your Data Model view of the report. Your summary column contains a value displayed at breaks in report output, as driven by groups of database columns. This value is the result of a grouping function such as the avg( ) or count( ) function in Oracle. A summary column shows whatever summarization you have defined in Report Wizard, either for the report level or for the group level. In Figure 25-8, your group-level summary is shown within the various groups, such as the group labeled Level1, as the summary column group level label. At the report level, another summary column is shown as a separate box in the Data Model view, with the summary column report level label.

This column is more of a derived column. You have an underlying set of data, such as that from a database column, where you take your underlying data and apply a formula to it. You can place your formula column in a group as an additional column of data output from that level. The result is then displayed in your report, just as any other database column. Alternately, you can put your formula column at the report level as a separate box, although the line between formula column and placeholder column gets a little blurry for report-level columns with trigger- or PL/SQL-driven data sources. Examine the group labeled main level in Figure 25-8 and find the formula column named formula column group level.

Ultimately, the only consistent difference between a formula column and a placeholder column comes down to the properties of each column type. In a later discussion in this chapter, you will be shown the properties of each column type. A formula column has one more property in its Property Palette, which allows you to define the break order for the formula column within the report or at the group level. Usable break orders include None, Ascending, or Descending in the Property Palette.

You can create a placeholder column at either the report level or within any group on the report to show data obtained either from report triggers or derived from formulas. In fact, there is little difference between placeholder columns and formula columns, save for the absence of the Break Order property available in formula columns that is not available in placeholder columns. Your placeholder columns at the report level appear as separate boxes such as the one in Figure 25-8 appearing with the label placeholder2, while placeholder columns appearing as part of groups in the Data Model will be listed within the group, such as the placeholder column labeled placeholder1 shown in Figure 25-8 in the group labeled main level.

An interesting feature columns have is the ability to read file contents—not just text files, either. You can read the contents of certain image files, Web pages, sound, video, and more. This feature allows you to develop multimedia reports. Here’s how. Inside the Property Palette for database, placeholder, and formula columns is a property called Read from File. This property can be assigned Yes or No, depending on whether you want the column to draw its contents from file or not. To make any database or user-defined column read the data it displays from file, double-click on the column to invoke the Property Palette, click on the Read from File property, and select Yes from the drop-down list of valid values.

A new property called File Format will appear when you tell Report Builder to read data from file for this column. The File Format property tells the tool what format the file takes. There are several file formats Report Builder accepts for use in your report content that allow you to display far more information in your report than text, numbers, and the like. Those types are listed in the drop-down box next to the File Format property, and they are identified and described here as well:

• Text The mainstay file format. Information stored in text format, usually ASCII, or whatever text type your operating system supports.
• Image Picture image stored in .bmp, .cal, .gif, .jfif, .pic, .pcx, .ras, .oif, or .tif format. You can create images in most of these formats with a graphics editor program such as Oracle Graphics, also part of the Developer/2000 suite, and then import or export them with Report Builder.
• CGM Line drawing image file format stored with extension .cgm. You can generate these files using Oracle Graphics and import or export them with Report Builder.
• Oracle Drawing Format Another line drawing image file format stored with extension .odf. You can generate these files using Oracle Graphics and import or export them using Report Builder.
• Sound Listenable sound files playable in your computer. Report Builder supports the use of these only when you create buttons to display the object. More about this activity is covered in the next section. Report Builder supports the .afc, .aff, .au, .snd, and .wav format sound files. You create sound files using an external sound software product. This file is stored in the database in binary as a LONG RAW or BLOB datatype.
• Video Viewable moving picture files. Report Builder supports the use of these only when you create buttons to display the object. More about this activity is covered in the next section. Report Builder allows the use of .avi or .moo format video files. You can create video files using external video software. This file is stored in the database in binary as a LONG RAW or BLOB datatype.
• OLE2 Any file format that conforms to object linking and embedding standards.
• URL Image Uniform Resource Locator or link to an image on the Web, stored in any of the acceptable image formats.

To use the Read from File property for database columns, the way to specify a filename for the database column to use is to have your report obtain it from the column on the database. For example, If you choose to have your report’s database column read information from file, you may receive errors when you try to run the report, saying that a particular filename corresponding to data returned from the database column was not found. It seems like a logical remedy that your database column should contain text strings corresponding to filenames and their paths. This will work, but it is not very portable. The correct thing to do is to ensure the registry REPORTS30_PATH contains the directory to search. Also, remember that the Read from File property only appears in the Property Palette when your database column datatype is CHAR or VARCHAR2.

Jump ahead a little so that we can describe what you need to do to read data from file if you are using formula columns. If you’re confused, reread this lesson after covering the rest of the section. For this lesson, return to the running ALL_TABLES report example. You will change your report to include information from a small text file that explains the meaning of the CACHE column. For your information, table caching is a DBA feature in Oracle that allows you to keep the contents of small lookup tables in memory for better database performance. The file you will read from, then, is a small and simple text file explaining this fact.

You set up this to happen in your report in the following way. First, click either on the Formula Column button once or move your pointer to the Level1 group containing the CACHE column and click within the group again to put the user-defined column in place. Double-click on the column you added to open the Property Palette for it, and change the Data Type property for the new column to CHARACTER. Change the Read from File property to Yes. Ensure the File Type property is set to Text.

The final task is to explicitly define the filename to read from. Before continuing, ensure that the file already exists in the file system. The filename Report Builder uses to populate data for a column is always the contents of that column. Thus, if you want to read data from a file, your column should already contain the filename for Report Builder to use. This is accomplished for placeholder or formula columns by using the PL/SQL Formula property to assign the placeholder column a filename. Click on this property to show the Definition button, then click that button to open the PL/SQL Editor module. In the body of your PL/SQL block, identify the filename you want the report to use as the value the block returns. Figure 25-9 displays the finished block of PL/SQL where the cachedsc.txt file is used in your report. The existence of your file is checked at compile time, so if your file is not present when you try to compile or look at the report using Live Previewer, you will receive an error.

Remember also an important point about portability. You may want to use an environment variable such as REPORTS30_PATH to identify where your files reside. Otherwise, if your files reside outside Report Builder’s usual search locations such as your machine’s path, Report Builder will not be able to find your file unless you specify an absolute pathname, which may not be portable from machine to machine.

You can create and use formula columns in two different places on your report: at the report level or at the group level. If your formula column appears at the report level, you will see it as its own object in the Data Model for the report you are writing. Formula columns at the group level appear along with other columns in the group.

Definition of a formula column for a report occurs in the following way. From within the Data Model view on your report, click on the Formula Column button on the left-hand margin of this view in Report Editor. This button has a database symbol on it with a small black "+" in the foreground. Then, move your pointer into the report Data Model definition area and click on the open area of the report, where no group or query appears. This area is sometimes referred to as the open canvas. After clicking once on the open area, a new object will appear with a formula column label automatically generated in it. This is your report-level formula column.

Double click on the new object to make to Property Palette appear for that object. There are several properties you can define for the formula, the number of which appearing in the Property Palette is directly related to the datatype of the column. The last property appearing in the Property Palette in Figure 25-10 is called PL/SQL formula. This property is the actual "formula" constituting your formula column.

Say for example that you want your formula column on the report level to retrieve the name of the Oracle instance this information came from and display it in the report. This information comes from the NAME column in a view in Oracle called V$DATABASE, that is owned by the SYS user. Click on the PL/SQL formula property once to make the definition button appear, and then click on that button to invoke the PL/SQL editor module to develop your formula. In this case, your formula will be a select statement like the one appearing in the PL/SQL editor module in Figure 25-11. Once complete, you can press the Compile button and Oracle Reports will compile your code.

You may also want to create a formula column at the group level that generates data shown within the report. For example, your ALL_TABLES report lists the size of your tables’ initial and next extents, as well as a percentage increase for each subsequent extent. There is enough information here to define another column, called THIRD_EXTENT, but you need to write a formula column to do it. In the Data Model view on your report, click on the formula column button and create a new formula column in the main level column group of your report by clicking once in that group. Then, double click on that column to invoke the Property Palette. Click on the PL/SQL formula property and press the property definition button that appears. In the PL/SQL editor module, define your formula using the PL/SQL code appearing in Figure 25-12. Then, click the Compile button, then the Close button, and you have defined your group-level formula. Don’t forget that you must prefix any database column in your formula PL/SQL block with a colon (:) in order for Oracle Reports to use the column reference as a bind variable.

Summary columns in your report can be defined at two levels as well, and those levels are also report and group. One method for defining summary columns has already been identified, the method that uses the Report Wizard Summary tab. The other method for defining summaries on reports uses the Data Model view on your report in Report Editor and the Property Palette.

To define a summary column at the report level, execute the following tasks. First, click on the summary column button in your Data Model view of the report, the button that is shown with a database icon and a sigma (S) symbol. Then, move your pointer over to the open canvas and click once. Your new summary column object appears. Double-click on the new object and the Property Palette appears. You can define several different types of summaries for the report, as identified in the previous section, using the list box that is accessible as soon as you click once on the Function property. You must identify a source for the summary function to use as well. This is usually going to be a database column from the report, although the source can be any user-defined column as well.

To define a summary column at the group level, execute the same tasks you would for the report-level summary column, but instead of placing the column by itself away from the groups and query object defined for your report, you will place the column inside a group. You then define the actual summarization operation and the source for your summary data as you would for a report.

Placeholder columns and formula columns are strikingly similar in their capabilities. You can set values for both using either formulas or report triggers. Placeholder columns are designated for use when you want to display results from formulas at the report level or at the group level, or with the use of a report trigger such as the BEFORE REPORT trigger if the placeholder is for use at the report level. Recall that the placeholder column (like other user-defined columns) will appear as its own object in the Data Model view if the column is placed at the report level, but will appear in a group if created for the group level.

You can populate a placeholder column with results from a report trigger if that column is placed as its own object at the report level, outside of any group. First, find the Placeholder Column button on the left margin of the Data Model for your report in the Report Editor. This button appears with a Database icon on it along with a small "?" in the corner. Click the button once, move your pointer onto the open canvas of your report, and click again. A new object appears to represent your placeholder column, denoted with an appropriate label. Double-click on that new object to open the Property Palette for the object. You can identify the datatype for your placeholder column as either CHARACTER, DATE, or NUMBER. Then, click on the PL/SQL Formula property, at which time the Definition button appears. Click that button now, and the PL/SQL Editor module appears in your display, in much the same way as it is shown in Figure 25-13.

Notice in Figure 25-13 there are several drop-down boxes at the top of your display, including a few with identifiers, Type, Object, and Name. The Object box is the one you are concerned with first. The following setup is required to get a report trigger feeding data into your placeholder column. If the content of the Object box do not read "Report," then you should pull down the list and make the appropriate change. After that, you may notice some changes in the Editor module. To use a report trigger to populate your placeholder column with values, you should pull down the Name list and choose the appropriate report trigger to use, such as BEFORE REPORT.

After making this choice, you will notice that some template PL/SQL code appears. The code will be used by you to define a function that returns a TRUE or FALSE value. In some situations, it may be useful to leave the report trigger definition as a function and let it return TRUE, then treat the rest of the PL/SQL block as a procedure where you execute a series of tasks on the database. For example, if your report is very complex with several different intermediate sets of data generated to produce the final result, you may want to create a temporary table to house the intermediate results with a BEFORE REPORT trigger and then remove the temporary table at the end with an AFTER REPORT trigger.

You cannot change the return datatype for this report trigger from BOOLEAN. However, you can reference the placeholder column label as a bind variable within the PL/SQL block you define for your BEFORE REPORT trigger, as demonstrated in the simple example from the PL/SQL block in Figure 25-13. Be careful about the datatype of the value you assign using bind variables at this point. Oracle Reports will not check to see if the datatype for your placeholder column and the assigned value match. If there are inconsistencies, such as a CHARACTER value assigned to a placeholder that is incompatible with the DATE format, you will receive an error on your report during run time.

The second method you can use to populate your placeholder column is with the use of a formula. This method is done for report-level and group-level placeholder columns. The method used to create your report-level placeholder column has already been covered. To create a group-level placeholder column, click on the Placeholder Column button. Move your pointer to the group in which you want the placeholder column to appear and click again. You then double-click on the placeholder column you just placed either into the group or on the open canvas and the Property Palette for that placeholder appears. Click on the PL/SQL Formula property and then on the Definition button to open the PL/SQL Editor. If you wish your formula to operate on the report level, select Report in the Object list; otherwise, select Column. The datatype returned will match the datatype you defined for the placeholder column in the Property Palette. Make sure you specify a value for the formula function to return as well.

This chapter covers modification of the data definition of your report through the use of the Data Model view on your report available through the Report Editor. Using the Data Model to create queries against the database to obtain data to make the report from is a topic covered, along with an understanding of how queries are represented within the Data Model. Understanding the concept of groups as another object in your Data Model view is another topic covered in this chapter. Finally, the chapter covers use of the Data Model to generate certain types of user-defined columns. This information comprises 15 percent of content in OCP Exam 5 of the Application Developer Track.

The first area of using the Data Model covers the objects in the Data Model and their relationships with one another. Several objects appear in a Data Model view on the report. The first items are system and user parameters, which impact how the report is run and certain aspects of how the output is produced. Another item in a Data Model is the query or queries that comprise the data definition. These objects are displayed as small boxes having only a query label on them. The next item in a Data Model is a group, which contains a list of columns in the data definition of the report. Groups are directly related to queries as evidenced by the line that Data Model draws between them. A query may have one or more groups as well.

Within groups there are several more objects in a Data Model view, called columns. Columns include database columns, or those columns containing data returned from the database by the query. Groups may also include user-defined columns. Examples of user-defined columns you have in a Data Model include summary columns, placeholder columns, and formula columns. Instead of drawing data directly from the database with these columns, the user-defined column allows you to define a process that acts on the data in a database column and produce output of a different sort for the user-defined column. Finally, you can have links between multiple queries in your report that allow you to draw data together from different sources in the database. Each object in a Data Model is also represented as a drill-down item in a report’s representation in the Object Navigator module of Report Builder.

Column groups in the Data Model offer a number of interesting options, such as the ability to define data hierarchy in the report. A report hierarchy can be created in group reports by placing different columns into different levels and creating breakpoints in the report output based on unique values for different columns at different levels in different groups. This activity in the Data Model is similar in concept to a regular select statement containing a group by clause, but offers the additional benefit of only one iteration of the higher-level data being printed for many lower-level records.

There are two methods used to create columns groups. One is using Report Wizard’s Groups tab, the other is to draw the group using tools in the Data Model and then populate the group with database and user-defined columns. Understand, however, that you cannot create hierarchy in a report using groups for those reports that are not group reports.

Changing order of data and data order in groups has many meanings, which were covered in the chapter. You may want to change the order of data in a column from Ascending to Descending, or vice-versa, using the Break Order property appearing in the Property Palette for that column when you double-click on the column name in the group on the Data Model view of your report. Appropriate use of Break Order is covered extensively in the text, and you should be sure you understand this concept before taking the OCP Reports exam. You may also want to change the order of how columns appear in the report by changing them in their group. This can be handled either by clicking and dragging on column names within groups or with the Report Wizard. Note, however, that even if you change the order of columns in your group, this does not necessarily change the order of columns shown in the report.

The Data Model allows you to restrict data returned from your reports at the group level with the use of group filters. There are two predefined filters you can apply to data groups, and they allow you to filter the first n or last n number of records of output from the report. These predefined filters are defined with the use of the Filters property found in the Property Palette when you double-click on the group object for which you want to define a filter in Report Builder. Otherwise, you have the power of PL/SQL available to you to define your own filter, first selecting the PL/SQL option for the Filters property of this group and then generating a PL/SQL function for the PL/SQL Filter property appearing below Filters when you select PL/SQL. Remember that if you want to refer to a column in the data definition from within your filter or any other PL/SQL block in the report, you must precede the column name with a colon (:) in order to allow Report Builder to refer to the column as a bind variable.

Finally, you can use data links in your Data Model to create reports that combine data returned from multiple queries, where a master query’s resultant data set drives the operation of a detail query. Creating data links requires you to define one query in the Data Model as the master query and another as the detail query. There are three types of data links in the Data Model, each indicating the level from master to detail. The first is query to query, where the link is drawn from the master query object to the detail query object. The second is group to group, where the data link is drawn from the group in the master query to the group in the detail. The third is column to column, where the data link is drawn from a column from a group in the master query to a column in the detail.

In many ways, a link between queries in the Data Model for a report is similar to a join operation in a SQL statement. The properties in the Property Palette for a data link that support this concept include SQL Clause, which can be set to where, having, and start with. Another property fitting the bill here is Condition, which defines the comparison operation between master and detail information in the link. Be sure to review the material in the chapter that covered some important restrictions for these and other properties for data links.

The other substantial discussion of this chapter covered use of the Data Model to create user-defined columns. There are three types of user-defined columns in a report: summary columns, formula columns, and placeholder columns. The first area covered in this section was how to identify different columns in a Data Model. Database columns are identified in a Data Model by their presence in groups. Next to a database file, there will be a small triangle. User-defined columns are identified in a Data Model by their presence in groups or as stand-alone columns operating at the report level. One way to distinguish user-defined columns from one another is to look at the Report Builder–generated label for the column. For formula columns, the label will be prefixed with CF; for summary columns, the label will be prefixed with CS; and for placeholder columns, the label will be prefixed with CP. However, since you have the ability to change the name of a column by altering the appropriate property, the only foolproof way to distinguish one type of user-defined column from another is to open the Property Palette for the column and read the identification information for the column from the top of the Property Palette.

A feature of database and formula columns is their ability to read data from a flat file and incorporate that information into your report. This feature is enabled through the use of the Read from File property appearing in the Property Palette for those columns. This property is set either to Yes or No. If set to Yes, you will need to define a file format type for the File Format property, which you can choose from several options that Oracle Reports accepts. These options include text files, several formats for image files, CGM format files, Oracle drawing format files, several types of sound files, a few types of video files, OLE2 object files, or image files on the Web accessible via URL. The Two-Minute Drill and chapter content both give more detailed information about exactly which formats Oracle Reports accepts. The last item you need to address when using the Read from File feature for your database or formula column is defining the filename the column should draw its data from. For database columns, the filename is whatever the content of the column as returned from the database reads. For formula columns, the filename can either be the content of the column or it can be defined using PL/SQL functions. Either way, the filename will be a name appearing in the column, not a property defined in the Property Palette.

The chapter covered how to define and use formula columns. Formula columns can be defined either at the group level or the report level. Typically, a formula column is used when you want to modify data in a database column and show it in the report, although you have already seen that formula columns can also be used to display information from files. Defining formula columns is a two-part process in the Data Model. The two parts are defining the object and defining the formula. The first task is accomplished with the help of the Formula Column tool button in the Tool Palette on the left margin of the Data Model view. You click the tool button and click in the group you want the formula to appear, or draw the report-level formula column somewhere in the open canvas of the report. The second task is handled using the PL/SQL Editor module to write the program that will comprise the formula. To invoke the PL/SQL Editor module, you click on the PL/SQL Formula property in the Property Palette once to show the Definition button and then click the button to open the appropriate module.

You also covered how to create and use summary columns both in this chapter and earlier in the material covering Report Wizard. There are several different methods you can use to summarize your database and user-defined columns, corresponding to different built-in SQL functions like sum( ), count( ), and several others listed as buttons in the Totals tab of Report Wizard. There are a few summary functions in the Function property available for the summary column in the Property Palette that aren’t available through Report Wizard, so be sure you understand how to define summary column behavior in that way also.

The final item covered in this chapter is the use of the Data Model to define and modify placeholder columns, and the use you may find for placeholders in your reports. As mentioned, formula columns and placeholders seem to be quite similar, though in Developer/2000 2.0, formula columns seem better supported for doing the things Oracle says they can do than placeholder columns. You can, however, populate placeholder columns with data from report triggers as well as through straight PL/SQL functions.

• Query objects Represent the query serving as the basis for report data definition
• Group objects Object containing report database and many user-defined columns
• User and system parameters Options set at run time that affect report output
• Database columns Report columns containing data from the database
• Summary columns User-defined columns that summarize data in database or other user-defined columns.
• Placeholder columns User-defined columns that can be populated either using PL/SQL functions or report triggers
• Formula columns User-defined columns that perform activities on database columns and show the results in a separate column.

• You can see information about your Data Model objects in the Object Navigator under the Data Model node.
• Groups can be used to model report hierarchy. The columns in a level 1 group will cause breaks in your report on unique values in your level 1 group.
• Hierarchy can be created with the drawing of groups directly in the Data Model and populating those groups with columns, or with the Report Wizard Groups tab.
• Order of data in a column can be Ascending or Descending, as set with the Break Order property for the database or user-defined column on the Property Palette.
• Order of columns in a group can be changed either by clicking and dragging columns around in a group on the Data Model, or by changing the order of fields in the Fields tab on the Report Wizard. This second option is possible only if the columns in the data definition match the displayed fields in the Fields tab.
• Group filters can be used to eliminate data from reports. They are defined using the Filters property, available in the Property Palette when you double-click on a group.
• There are two types of filters: predefined and user-defined. The two predefined filters are first and last, where you can specify to see the first or last n records, respectively. User-defined filters are written by you using PL/SQL.
• You can reference columns in a PL/SQL function such as that which you would define for a filter by prefixing the column name with a colon, so that the PL/SQL block can refer to it like a bind variable.
• Filters are indicated in the Data Model view by changing the color of the circle at the end of the line leading to the group that has a filter defined on it.
• Two or more queries can be put together for use in a report with the use of data links. There are three types of data links, listed here:

• Query to query
• Group to group
• Column to column

• There are four types of Data Model columns. They are listed and defined below:

• Database columns Columns of data in the report from the database
• Summary columns Columns that perform a summary operation (like sum( )) for all data in a column
• Formula columns Columns that perform an operation on another database column
• Placeholder columns Columns that obtain their data either from a formula or a report trigger

• The prefixes on default labels for a user-defined column can identify the column type. However, since you can alter the label, the surest way to identify a column type is to open the Property Palette for that column. The prefixes on default labels for various user-defined columns are as follows:

• Placeholder: CP_
• Summary: CS_
• Formula: CF_

• Database columns are identifiable in the Data Model group by a small triangle next to the column name.
• Database and formula columns can display the contents of files. This feature is configured with specification of three details:

• Read from File property set to Yes in the Property Palette
• File Format property set to Text, Image, CGM, Oracle Drawing Format, Sound, Video, OLE2, or URL Image.
• Filename determined by the content of the database or formula column, coming from the database or a PL/SQL function, respectively. Your database or formula column should specify absolute pathname so that Report Builder can find the file.

• The existence of your file is checked at report compile time, so if the file is not present, you will receive an error.
• If report portability from machine to machine is an issue, consider using the REPORTS30_PATH environment variable as an alternative to specifying absolute path for filenames to be read by database or formula columns in reports.
• Formula, summary, and placeholder columns are created in a four-step process. The steps are as follows:

1. Click on appropriate tool buttons in the Tool Palette on the left margin of the Data Model.
2. Move pointer to open canvas in Data Model for report-level column, or to appropriate group for group-level column, and draw report-level column or click in the group to place group-level column.
3. Double-click on new user-defined column to invoke Property Palette for that column.
4. Modify properties as appropriate, such as those to define summary functions, PL/SQL formulas, or report triggers.