Chapter 27

Developing Other Features in Reports

In this chapter, you will cover the following areas of developing other features in reports:

• Using report parameters and parameter forms
• Embedding charts in reports
• Developing and enhancing matrix reports

This chapter covers some of the final elements of basic report development in Oracle Reports, and then moves on to some advanced features and techniques. A final basic element in report development is the creation and use of report parameters, and the use of the Parameter Form tool in Report Builder. After covering that element, the chapter will move on to the creation and use of charts to enhance your reports with visually appealing contents that add value to the overall report. Finally, development of what some may argue is the most complex type of reports—matrix reports—is covered. This final section emphasizes usage of both the Layout Model and Data Model, whose features are covered extensively in the last two chapters for the purpose of creating matrix reports, and turns the knowledge of features you gained in the last two chapters into a case study of their use. The content of this chapter comprises 15 percent of material tested by OCP Exam 5.

In this section, you will cover the following points on using report parameters and parameter forms:

• Controlling report output with parameters
• Creating user parameters in a report
• Creating lists of values for parameter input
• Incorporating user parameters into queries
• Using and modifying system parameters
• Building Parameter Form layouts for value entry
• Customizing Parameter Form layouts

The behavior and output of reports is managed with the use of parameters. Early in the section, you will cover the parameters that are used when executing reports. Some of these report parameters determine aspects about the content of the report, while others determine aspects of how the report runs on the machine. This section covers how to control the output of your report using parameters. In addition, the section covers use of Lists of Values, or LOVs, for parameter input. The use and modification of system parameters is covered as well. You will also learn how to build and customize special objects called parameter forms that handle input of parameter values by users of the report.

Two types of parameters exist in Report Builder. They are user parameters and system parameters. Recall early in the unit the discussion of parameters available for use with Report Builder running from the command line. These parameters are system-defined, and may be used to alter aspects of the report runtime behavior. The system parameters include DESFORMAT, DESNAME, and DESTYPE, used to identify the report destination device format, name, and type, respectively. Other system parameters include ORIENTATION, MODE, PRINTJOB, BACKGROUND, and COPIES, which determine various aspects of the report output, such as portrait or landscape print orientation, character or bitmap runtime mode, number of copies printed, and other features. Finally, system parameters include CURRENCY, DECIMAL, and THOUSANDS, which impact the characters Report Builder will use to represent money values, the decimal point (period in the United States, comma in Italy, etc.), and the character used to break up numbers every three digits to the left of the decimal point (such as 567,332,768,976,456,788).

The rest of the parameters are user-defined parameters. That means you can create and use them at will to alter the runtime behavior of your report. You can rename user parameters and even remove them from the report. System parameters, on the other hand, cannot be renamed or removed from the report. Reasons for incorporating user parameters into your report include wanting the ability to pass values into the report to affect its output. For example, say you are developing a master/detail report on the top 500 account executives in an organization paid expense dollars in a given year. At the master level, you may go against several tables to calculate the total expense dollars by account executive and the locations/companies they visited. In the detail level, perhaps you want to determine the total amount of revenue generated by that account executive’s visit to the location mentioned. You may want to create a user parameter that passes the account executive’s employee ID, date of visit, and location/companies visited by the account executive within the detail report to allow information to be passed down from the master to the detail.

Values for parameters are defined in two ways. They are either passed in as parameters or defined at report run time using a runtime Parameter Form. Later in the section, you will cover how this fact influences any value checks you may institute to make sure appropriate values are defined for parameters. You will also cover how to create parameter forms, values for system parameters, and the creation and use of user parameters.

Usually, your report won’t contain any user parameters until you create some for the report. You create user parameters in the following way. First, you must drill down to the appropriate level in your report to create the user parameter. This is found in the Object Navigator module for the report using the Report | Rpt_Name | Data Model | User Parameters node. Once there, notice that there are no user parameters already defined. To create a new one, double-click on the User Parameters node in Object Navigator. Alternately, you can click on the Create button in the Tool Palette of the Object Navigator module along the left margin. Either of these actions causes a new item to appear as a node in the Object Navigator under User Parameters and also opens the Property Palette for you to use in defining properties on the new user parameter. The following properties can be found in the Property Palette and must be defined in support of your user parameter.

The name for your user parameter. Initially, this will be a value generated by Report Builder, but unlike system parameter names, you can invent a name for your user parameter yourself and make the change directly to the property.

This one is self-explanatory. You click on the cell next to the property in the Property Palette to display the Definition button, which will appear with an ellipses displayed on it. You then click the Definition button to invoke a special interface into which you enter your comment. When finished, you click OK and the comment gets saved into the report. You can display the comment later using the Definition button again.

This property defines the datatype accepted by your user parameter. Three datatypes are possible for this parameter, including CHARACTER, DATE, and NUMBER. All the following properties are relevant to all the datatypes you can choose to assign for your user parameter, except for Input Mask, for which none of these three possible datatypes requires a value. If left blank, Input Mask simply takes the old standby ‘DD-MON-YY’ for dates, though NLS settings may change this, and –9999 for numbers.

You must define a width for your different properties. The default is usually 20, but you can specify any value you like between 1 and 65535. This value specifies how wide the value accepted for the parameter will be allowed to be.

For numbers and dates, your parameter may have a special format that you want to impose on the input to make it usable, readable, or understandable. This is known as an input mask. Report Builder is smart enough to offer a set of masks specific to the DATE or NUMBER datatype. Some examples of date format masks use well-known terminology for date references, where YYYY represents a four-digit year, HH24 represents hours on the 24-hour clock, MM represents a two-digit month, and so on. Some examples of numeric format masks also use well-known terminology for numeric references, such as that used for money formatting, scientific notation, and other formats.

This is the initial or built-in value for the parameter. It can be modified using methods covered in the next lesson. The value set for the parameter in this property must correspond with the datatype you identified in that property as well.

This is a function developed in PL/SQL that fires when Report Builder processes runtime parameter settings and again when the Parameter Form is processed. This function returns TRUE or FALSE, and has the ability to modify the value for the parameter as a bind variable.

This is a special object in the report that allows you to define a set or list of acceptable values to be used with your parameter (this is also abbreviated as LOV). For example, if you wanted to define a parameter that accepted a color of the rainbow as input, you might define a List of Values to go along with it that makes the only acceptable values for the parameter red, orange, yellow, green, blue, indigo, or violet. More about creating a List of Values appears in the following lesson.

As mentioned at the very end of the previous lesson, a List of Values, or LOV, is used as a validation mechanism for values defined in user parameters. To define a List of Values, first you must click on the List of Values property in the Property Palette to show the Definition button. If you then click on the Definition button, you will see an interface that facilitates the creation of the LOV. Your ability to create an LOV then depends on your ability to use the Parameter List of Values interface, which is shown in Figure 27-1. You create your LOV in one of the following two ways: either by specifying a static set of values or by selecting the List of Values from the database. Each method is covered in turn over the course of this lesson.

There are several features in the interface to understand. The middle left-hand corner of the parameter List of Values interface has a pair of radio buttons, one called Static Values, the other called SELECT statement. When the Static Values button is clicked, the rest of the interface shows a text window where you key in a value for the static value list, along with two buttons used to add and remove elements from the set of static values appearing in the large text box on the right side of the interface. At the top-right side of the interface is a check box, next to which is the phrase Restrict List to Predetermined Values. The meaning of this check box is that, when checked, the value defined for the parameter must be part of the LOV you display. This interface is redrawn when you click the SELECT Statement button, as shown in Figure 27-2.

When the radio button is set to SELECT statement, the interface is drawn such that the predominant object is the text box showing the SQL query statement. A second check box appears in the right-hand side of the interface, next to which is the phrase Hide First Column. The meaning of this check box is that you can optionally hide the values in the leading column returned by your select statement defined in the text box in the interface. The final two items in the interface you should focus on are the Query Builder button, which allows you to use that utility as explained early in the unit to define your select statement for the LOV. The last item is the OK button, which completes the definition of your LOV using the interface.

Your specification of a static set of values in your LOV happens in this way. First, ensure that the radio button in the upper left-hand side of the interface is set as shown in Figure 27-1 so that you can enter static values. If the interface is set to accept a select statement for developing your LOV, as shown in Figure 27-2, you will not be able to define a static LOV. Once this measure is taken, you are then free to enter static values in the smaller text box on the left side. Once entered, you move the new static value to the large text box on the right side by use of the Add button. If there are items in the static list you want to eliminate, you can use the Remove button to do so as well. When finished defining your static LOV, click OK.

Alternately, your specification of a dynamic set of values for your LOV happens in this way. First, ensure this time that the radio button in the upper left-hand side of the interface is set as shown in Figure 27-2 so that you can enter a select statement or use Query Builder to develop the query. If the interface is set to accept static values for developing your LOV, as shown in 27-1, you will not be able to code a select and the Query Builder button will be grayed out. After handling this task, you define your select statement to select the LOV from a table in the same way you would specify any other select statement in the database. Alternately, you can develop the query with Query Builder, which has already been covered earlier in this unit in relation to Report Wizard. Once complete, click the OK button.

Now that you understand how to give users the ability to specify their own parameters for a query, you need to provide a way for the report to incorporate the user parameter into how the report obtains its information. This step is accomplished through the use of lexical and bind references. Your use of user parameters in your reports depends on your ability to successfully manage the use of both these items.

Values specified as your user parameter can be incorporated into SQL statements in your report data definition through the use of bind references. Bind references replace a single value in SQL or PL/SQL, such as a DATE, VARCHAR2, or NUMBER. The clauses in which bind references are used include select, where, group by, order by, having, connect by, or start with. You may not include a bind reference in the from clause or instead of some Oracle SQL reserved word, and no bind reference may ever be the same name as an Oracle reserved word. Note also that you cannot use a bind variable in an order by clause without also using the decode( ) function. A bind reference is created by placing a colon (:) immediately before the column or parameter name. If you do not create a column or parameter before making a bind reference to it in a select statement, Report Builder will create a parameter for you by default. The following code blocks show some sample SQL statements containing bind variables:

SELECT nvl(DEPTID, :DFLTDEPT) DEPARTMENT, SUM(SALARY) TOTAL_HRCOST
FROM empl;

SELECT EMPID, SUM(REIMB_TOTAL) TOTAL_EXPENSES
FROM emp_exp_tbl
GROUP BY EMPID
HAVING SUM(REIMB_TOTAL) > :MINTOTAL;

Alternately, you can incorporate user parameter values through lexical references. Lexical references are placeholders for text that you include in select statements. You can use lexical references everywhere you can use bind references, and with one area you cannot use bind references: in a from clause! However, you cannot use lexical references in PL/SQL—but you can use a bind reference in PL/SQL to set a parameter value and then refer to that parameter value lexically in SQL. A lexical reference is created by placing an ampersand (&) in front of a column or parameter name.

Unlike bind references, a default definition is not provided for lexical references. Therefore, you must do one of the following. In the first case, you must define a column or parameter in the Data Model for each lexical reference in the query before you create your query. For columns, you must specify a value for the Value if NULL property, and for parameters you must enter a value for the Initial Value property. Report Builder then uses these values to validate a query with a lexical reference. Or, you must create your query containing lexical references. For example, you can define the following SQL query:

select &p_kgroo KGROO, &p_kgroo_joey OFFSPRING from JOEYS;

The lexical references &p_kgroo and &p_kgroo_joey can be used to change the columns selected at run time, such as through a runtime Parameter Form. If you do it this way, be sure to use column aliases; if you don’t, if you change the columns selected at run time, the column names in the select list will not match the Report Builder columns and the report will not run. Lexical references give you many other options for dynamically altering your SQL from and where clauses, as in the following code block:

SELECT ITEM, VALUE FROM &MY_TABLE;
SELECT ITEM, VALUE FROM ITEMS WHERE &MY_CLAUSE

You can use MY_TABLE to change the table used in the query at run time, which can be helpful in developing generic reports that will be used in several different applications, client sites, or departments. For example, you could enter KGROO_JOEYS, EMPLOYEES, or just about any other table where there is a logical concept of an "item" and a "value." In some cases, you may also want to use lexical references for the select statement to grab the actual names of columns in the tables if there are differences.

Unlike user parameters—which you have just learned how to build from scratch, and which are fully modifiable by you the report developer—system parameters are created by and built into Report Builder. You cannot arbitrarily remove one. You define values for system parameters in reports in the following way. Using the Object Navigator module, drill down into the Reports | Rpt_Name | Data Model | System Parameters node. You will notice all the system parameters mentioned above are listed, each with a small circular icon used for displaying the PL/SQL Editor module for developing PL/SQL functions to validate the value defined on the parameter. Next to that is the Properties icon, on which you can double-click in order to view the properties associated with the system parameter. The next step in the definition of a value for the system parameter is to double-click on the Properties icon and open the Property Palette for the parameter. You then can modify the value for the COMMENT, INITIAL VALUE, or VALIDATION TRIGGER properties in the Property Palette, but not the NAME, DATATYPE, or WIDTH properties.

Some notes about the properties you can modify now follow. The COMMENT property is self-explanatory. The INITIAL VALUE property is used to handle defining the value that will be used for the parameter when the report is run. The VALIDATION TRIGGER property allows you to define a PL/SQL function that validates the value against standards you define in the function. This trigger returns TRUE or FALSE based on whether or not the value was valid according to your criteria. The trigger has the ability to change the value for the parameter through the reference of the parameter as though it were a bind variable. If the validation fails, control is returned to the user within the runtime Parameter Form, where you must redefine the value for the parameter or else cancel execution. Note the following restrictions on validation triggers. They must usually be small—around 32K or less. You shouldn’t issue DDL statements against the database like create table in a validation trigger. Finally, because of the way connection to the database is handled when a report’s execution is triggered within another report, you should commit all outstanding changes in the parent report before kicking off the child.

One of the features Report Builder extends to the developer is its ability to permit entry of parameter values by users when the report begins processing. The development of an interface to help the user with accomplishing the task of defining values for runtime parameters is greatly facilitated within Report Builder with the use of the Parameter Form Builder tool. After building all your user parameters along with their associated LOVs and determining which of the system parameters you want your report users to have access to, you can then assemble a Parameter Form for the user to enter parameters easily.

The Parameter Form is used in the following way. First, with your report open in Report Builder, click on the Tools | Parameter Form Builder menu bar option. This selection causes the Parameter Form Builder interface to be displayed. Figure 27-3 shows you the Parameter Form Builder. The other way to open the Parameter Form Builder is to move to the Parameter Form view of your report in the Report Editor, and click on the Parameter Form Builder button available in that interface, which appears second to last on the right side of the top row of buttons in the interface, with a pen and window displayed on it. Within that interface, you will see several items listed, including text boxes for you to use when defining a title, hint line, and status line. The text you define for a title will appear centered at the top of the runtime Parameter Form seen by your users when the report runs. The text you define for the hint and status lines will appear centered in the runtime Parameter Form just below the title. Below these two text boxes is a list of all system and user parameters that have been defined for this report.

You are free to include whichever parameters you want from this list by simply clicking on the name of the parameter in the list once to highlight that parameter. Also, you can change the label that will appear in the runtime Parameter Form next to the text box where users enter the value they want for the parameter by entering something into the text box next to the name of the parameter in the Parameter Form Builder interface. When you are finished defining your Parameter Form using the Parameter Form Builder interface, click on OK. You can then see the Parameter Form you defined for the report using the Report Editor interface if you click on the Parameter Form button next to the Layout Model button in the top left-hand side of the Report Editor interface. The Parameter Form view available for your report in Report Editor is shown in Figure 27-4.

Run button

Once you have created your Parameter Form for the report, you can then see how the Parameter Form behaves in action by running the report. Several ways exist for doing this step. The first is to click on the Program | Run Report menu item in the Report Builder interface. The second is to click on the Run button in the top middle of the Report Editor interface, which is the button displaying the Traffic Light icon in the display. You can click on View | Runtime Preview or View | Live Previewer from the menu bar to see a runtime or live preview of the report. Alternately, you can click on the Live Previewer button from within Report Editor to get to the Live Previewer view. After employing any of these methods to start running the report, the first screen you will see is the runtime Parameter Form. On it, you can enter values for any of the parameters displayed. When you are finished, click the Run button appearing in the runtime Parameter Form interface and your report will execute with the values you specified for the parameters in the Parameter Form. Figure 27-5 shows the runtime Parameter Form.

For rapid application development purposes, you should find the basic layout provided automatically when you create your Parameter Form using the Parameter Form Builder quite useful. "Basic" is a good word to describe the interface the Parameter Form Builder creates for you. In some cases, however, you may want a more advanced appearance for you Parameter Form, perhaps adding the name of the company in addition to the name of the report, or changing the text color of the title or parameter labels.

Your customization of your Parameter Form happens in the following way. Switch to the Parameter Form view in the Report Editor on your report; notice there are several tools available to use in the Tool Palette on the left margin of the interface. The Parameter Form view of your report is essentially a layout view of the runtime Parameter Form. You can add elements to the runtime Parameter Form such as text boxes, additional parameter fields, lines, or shapes. Additionally, you can modify the fill, background, and line color of objects in the Parameter Form for the report using Tool Palette buttons. The general usage of these tools is the same process as that covered for Tool Palette buttons for the Data Model and Layout Model views in previous chapters. Refer to those areas of the text for a refresher if you need it.

You can modify the settings for existing objects along with adding new ones to your report using the Parameter Form view. Simply click once on the object to activate it and double-click on it to invoke the Property Palette for the object. When active, you can alter background, line, and fill color in the Parameter Form object. Other features you can add to your runtime Parameter Form that allow it to display more parameters, text, or graphics are now described.

Sometimes if your runtime Parameter Form offers users the ability to modify several different parameters for themselves, the runtime Parameter Form tries to prevent itself from becoming cluttered. Report Builder creates the basic runtime Parameter Form with scroll bars built in. If you have seven or eight parameters to display, rather than making you scroll through a screen, the runtime Parameter Form allows you to move through pages of screens to set values for parameters. You can add pages yourself using the Property Palette. Open the Property Palette on the entire report by clicking the icon next to the name of the report in the Object Navigator module, and look for the Number of Pages property under the Parameter Form Window property node. You can alter its value to another number by keying that number in, pressing enter, and closing the Property Palette. If you run your report again, you will see the Next Page button that was formerly grayed out is now active and available for use.

Adding enough parameters to the Parameter Form will also cause Report Builder to automatically prompt you that more pages are needed. Return to the Parameter Form Builder interface, as shown in Figure 27-3 by clicking on the Parameter Form Builder button in the Parameter Form view of the report in Report Editor, or choose the Tools | Parameter Form Builder menu item within Report Builder. Then, click on more parameters to highlight and add them to the Parameter Form. If too many to display on one page are added to the Parameter Form, Report Builder will prompt you asking if it is all right to increase the number of pages so that your parameters can be displayed cleanly.

In this section, you will cover the following points on embedding charts in reports:

• Creating graphics charts with Chart Wizard
• Displaying existing graphics charts in reports
• Using parameters for dynamic modification of chart data

Most people hate reports that just display statistics. Numbers, lists, tables, and all that are frankly boring, the stuff of endless meetings where your colleagues fall asleep and start drooling on themselves, or worse. To combat "meeting fatigue," people in business love charts—the more colorful the chart, the better. In the quest to give meaning to information, you will find yourself scoring points with everyone on the business side in your next meeting if you master the art of spicing your reports up with charts. Report Builder can help you achieve your goals with an easy-to-use tool that turns boring facts into exciting pictures. That tool is Chart Wizard, and in this section you will learn how to use it.

The use of Chart Wizard is really easy. The data is already in your report—you only need to put that extra spin on it to make it easily understood. Take an easy example—a quarterly sales report from a historical perspective. It’s nice to list the sales figures in tabular format, with this year’s figures on one side and next year’s on the other. You may even jazz it up by putting the numbers for this year in black and last year’s in blue. But the icing on the cake comes when you turn it all into even a simple column chart with the same color-coding scheme.

Open the Chart Wizard in one of the two following ways. You can either choose the Tools | Chart Wizard menu bar item from within Report Builder—with or without your report being open in Report Editor—or by opening Report Editor for your report, navigating to the Layout Model, and clicking on the Chart Wizard button found near the Report Wizard and Region buttons in that view on your report. You navigate through Chart Wizard in the same way as Report Wizard, using the Next button to move forward and the Back button to return to prior screens.

The first screen is used for defining an optional title to the chart, and it is also where you select the basic chart type and subtype. With the exception to the pie chart, each of the following charts display information in two dimensions, which in turn are represented as an axis on the chart. The horizontal (or X) axis is called the "category" axis, because on it you can separate your data shown into the categories they break down into. The vertical (or Y) axis is called the "value" axis, because on it you show the values that each category attains. For example, if you were making a chart to show unit sales by account executive, the category (X) axis would have each account executive’s name, and the value (Y) axis would have numbers to represent unit sales. Another example might be to show unit sales over a period of time. In this example, the category (X) would be time, while the value (Y) would be unit sales. The exception to this paradigm is the pie chart, for which the graph represents values as an aggregate, and categories as "slices" making up that aggregate. These reports have no X or Y axes, but instead have an aggregate or "value" broken out across category. This interface used for defining chart title and type/subtype is shown in Figure 27-6, and each type and subtype is described as follows.

This is a chart where vertical bars drawn from the horizontal (or X) axis reach values identified along the vertical (or Y) axis. Subtypes of this chart type allow for variations in the way the bars are drawn. This is often used for comparison of different items using the same criteria, such as sales figures by account executive. An example of a column chart appears in Figure 27-7.

A bar chart is similar to a column chart, except that the bars are drawn horizontally from the Y axis, reaching values identified in the X axis. Subtypes of this chart type allow for variations in the way the bars are drawn. Bar charts are often used for comparison of different items using same criteria, such as sales figures by account executive. An example of a bar chart appears in Figure 27-7.

This is a circular chart, or "pie," that displays subsections or "slices" in different colors. Subtypes of this chart type allow for variations in the way the pie is drawn. A pie chart is often used to illustrate a market of some kind and the market share different constituents hold. An example of a pie chart appears in Figure 27-7.

This is a chart with an X and a Y axis, where values of one type are identified on the Y axis and values of another type are identified on the X axis. A line is drawn in the chart to show a vector, or points where values on X meet values on Y. Subtypes of this chart type allow for variations in the way the lines may be drawn or for the use of symbols to represent lines. A line chart is often used to illustrate trends where Y represents a set of values such as unit sales or money, while X represents time. An example of a line chart appears in Figure 27-7.

A combination of the line and column charts identified above and below. Subtypes for this type identify variations for the way either the lines or the columns are drawn. A mixed chart may be used to show a combination of information, such as sales figures by salesperson, along with overall sales trend. An example of a mixed chart appears in Figure 27-7.

The next two screens in the Chart Wizard interface are used to define the values you will display on the X and Y axes of the chart. The interface shows the columns your report will display, corresponding to the columns defined in your data model. Given the descriptions of common usage for each of the chart types, it shouldn’t be difficult for you to decide which columns to use as baseline data for each axis. For example, say you were reporting on overall product sales figures per week for a six-month period. You had a table in your Oracle database called PROD_SALES, with two columns: WK_END_DATE and UNITS_SOLD. In both the interface used to define values for display on the X and Y axes, you would see both columns from the table. You can then use the Right Arrow button to move the WK_END_DATE column for display in the category (or X) axis. Click the Next button to move to the next interface, where you define the UNITS_SOLD column whose values will serve as the value (or Y) axis. Figure 27-8 shows the Chart Wizard interface used to identify category information, while Figure 27-9 shows the Chart Wizard interface used to identify value information.

Your new chart will have little impact on the overall value of the report if it doesn’t appear appropriately within the report. Chart Wizard allows you to determine the frequency a chart is drawn into the report you just wrote by selecting the frequency from the list of frequencies in the interface. The interface where you define the frequency of chart appearance is shown in Figure 27-10. The number and names of frequencies you can choose have the following general format:

• Beginning of report This frequency always appears in the list. It allows you to display the report only at the beginning of the report.
• End of report This frequency always appears in the list. It allows you to display the report only at the end of the report.
• At breakpoints in each group If your report is a group report, each group level you defined contains repeating frames in support of breakpoints for each of the groups. You have the option to display a chart at breakpoints for any of the groups defined in your report.

Once your chart definition is complete, you will be prompted within Chart Wizard to save the chart in Oracle Graphics Designer (OGD) format. The final interface of Chart Wizard prebuilds a filename for you to use to save your chart. You can use it automatically by default, or you can define your own filename by clicking on the Save As button in the window. This will cause Chart Wizard to open the Save As interface available for your operating system. Your only option for filename extension to use is .ogd because that extension indicates the type of file the chart is. Your files will be stored by default in the bin directory underneath your Oracle software home directory. When you are finished defining your own filename, click the Save button in that Save As interface and you will be returned to Chart Wizard, where you can click the Finish button to save the file and complete your chart definition.

Chart Wizard isn’t the only way for you to include charts in your report, either. You may have designed more complex charts using Graphics Builder, another component in the Developer/2000 toolset. You can include existing charts in your report easily with the use of the Layout Model view in Report Editor. Covered in Chapter 26, the Layout Model offers a Tool Palette containing many buttons that allow you to draw layout objects and other objects that let you interface with objects outside your report. One of those tools is the Chart button, shown in the Tool Palette for the Layout Model view with a small column chart on it.

Recall last chapter that there are general usage guidelines for any of the tools in the Tool Palette in the Layout Model view. Those steps are first to click on the Chart button to activate the tool, then move the pointer over to your report layout and draw the chart object in the layout. After the chart object is present in the Layout Model of your report, you can double-click on that object to assign its properties. The key properties you need to define for displaying existing charts in your reports are now described.

Obviously, the key element to define for your chart is the name of the file containing its definition. The Chart Filename property in the Property Palette for a chart object handles the definition of the chart filename. When you click on this property, a space appears where you can define the filename for the chart, along with a button that opens the Open File dialog interface. Key in the name of your file, or simply click the button, and then find the chart file with extension .ogd. When you have located your file, either in the default bin directory under the Oracle software home directory on your machine or elsewhere, click the Open button and you will return to the Property Palette where your filename and path appear together as the defined value for the Chart Filename property.

Since your chart may have been designed a bit more generically than the chart you would write with Chart Wizard, you must create a data source for your chart to use when drawing itself. The definition of chart parameters and columns identifies several key elements about the data source for the chart placed in your report. The properties are defined through a special interface. To invoke that interface, click on the Parameters and Columns property in your report to make the button used for invoking the interface appear in the Property Palette. Then, click on that button and the interface for defining report-to-chart column and parameter mappings will appear in the Report Builder interface. The next lesson focuses on how to use this mapping from report columns and parameters to chart columns and parameters in order to make dynamic modifications to report data.

The final points made in the last lesson should indicate a few things about the use of parameters for dynamic modification of chart data. Since your chart made in Graphics Builder may be used across many reports and/or Oracle Forms applications, you will want to set it up so that the chart can accomplish these tasks easily. The best way to do this is to pass the column and/or parameter values you want the chart to use. The Parameters and Columns properties—both available in the Property Palette for chart objects in the Layout Model view of your report— together are the methods by which you can dynamically modify chart data. When you click on this property, a button appears that you can click to invoke the Chart dialog interface, where you define how chart parameters and columns match up to report columns and parameters. The next two lessons identify the methods used for both tasks.

Figure 27-11 demonstrates the Chart dialog interface used to define parameters and column matchups between chart and report. Notice there are two tabs in the dialog interface in Figure 27-11, one for defining column matchups and one for defining parameter matchups. The contents of the Parameters tab are shown in Figure 27-11. Consider the Parameters tab first. To view the contents of the Parameters tab for this interface, click on that tab in the Chart dialog interface. This interface contains two lists. The list on the left contains all report system or user parameter names, along with the names of all columns displayed in the report. Any of these report columns or parameters can function as inputs for chart parameters. On the right of the display, there is a series of text boxes. You are expected to code in the names of your chart parameters to which these report parameters will correspond.

For example, you may be writing the report that shows the weekly unit sales figures over the last six months that was described earlier in the section. However, as a longtime developer of reports, you also know that there are many cases where your colleagues will want to see a line chart that displays trends over time. To speed report development, you create a generic chart that accepts a value called chrtval and a category called chrtcat. In your use of this chart on the weekly unit sales over six months report, you want to pass the product ID the user wants to see unit sales for and the end date for the six-month period, which are user-defined parameters in your report you have called PROD_PARM and END_DTTM, respectively.

You will find those two report parameters listed on the left side of the Chart dialog interface. You will first click on the name of the report parameter as you did in the interface used for defining runtime Parameter Forms described in the first section of this chapter. Then, you will enter the name of the chart parameter used to feed information from the report into the chart in the text box on the right of the line where the report parameter is listed. One final note is that defining a value in this text box is optional. If you do not specify the name of the chart parameter in the box across from the report parameter you highlight in this tab, then the Chart dialog box will assume the report and chart parameters have the same name.

Figure 27-12 displays the contents of the Columns tab in the Chart dialog interface. There are a few significant differences between the Columns tab and the Parameters tab. First, at the top of the Columns tab in the Chart dialog interface, there is a text box where you can define a chart query name. This feature allows you to have your chart select data for display in the chart out of the data in the report rather than from the database. If you have done a great deal of data filtering in the report and want to ensure your chart contains report data only, you can specify a query from the chart that should use the data extracted by Report Builder instead of data in the database. Defining a query name is optional, and should be done only when you want the chart query to use Report Builder to filter information rather than using information directly from the database.

The next significant difference between the Columns tab and Parameters tab is the presence of a report group definition item. The definition for this item consists of a drop-down box where all the report groups are listed. If you enter a value for the chart query item on the Columns tab, you must choose a report group for which the chart will summarize its data contents. Otherwise, the definition of a report group is meaningless and will be changed to NULL when you have finished defining the properties for your chart.

Identifying a report group and chart query puts a constraint on your definition of report parameters and columns appearing in the remainder of this window. If you define a chart query and associated report group, then all the columns you select from the list of columns must either be a member of the report group you defined or a member of a hierarchical group to that group in the report hierarchy. In addition, the qualifications you have in defining report parameters and corresponding chart parameters for the Parameters tab also apply to those columns listed in the Columns tab.

In this section, you will cover the following points on developing and enhancing matrix reports:

• Designing a matrix Data Model
• Designing a matrix Layout Model
• Displaying zeros in cells with no value

You have covered a tremendous amount of information about the design of reports from the data and layout perspective. You have also covered methods by which you can define parameters and use the Parameter Form built-in feature to allow your users to control the execution of their own reports with parameters. You have even added the creation of niceties such as charts and the process by which you add them to your report. So far, you have covered these items in association with simple to moderately complicated reports such as tabular and group reports. In this section, you will learn more about the most complex set of reports you can create in Report Builder: matrix reports. Creation of matrix reports from the Data Model and Layout Model perspective will be covered, along with simple replacement of zeros for NULL values in empty cells.

Recall from early in the unit the definition of a matrix report. To refresh the discussion, a matrix report is one with at least four groups, where two of the groups contain data that will become the horizontal and vertical axes of report. A third group represents the cross-product of the elements in the groups on the horizontal and vertical axes of the report. The creation of the Data Model for a matrix report starts in the same way as it would for other reports. In this example, you will create a matrix report of information from the UNIT_SALES table, where four account executives keep track of their unit sales by product and week. The columns in this table include UNITS_SOLD, UNIT_NAME, ACCT_EXEC, and WK_END_DT. The matrix report you will design displays unit sales by account executive by product for the month of January. The rest of the lesson covers the four steps for creating a matrix Data Model.

The way you design the matrix Data Model is as follows. You create or start a new report using the Object Navigator. Click on the Reports node and then on the Create button on the Tool Palette in the left margin of the Object Navigator, or simply double-click on the Reports node. Report Builder will then prompt you to decide whether to build the report using Report Wizard or whether you will build your report manually. Although you can build a matrix report using the Report Wizard, the point of this exercise is to master the task of designing your own matrix report using the Data Model view in Report Editor, so select to build your report manually and then click OK. Your new report called Untitled will now appear in the Object Navigator module.

Report Builder then opens the Report Editor module and places you in the Data Model view on your new report. From there, you will need to perform the data definition for your report. For your purposes here, you will simply select all the data from the UNIT_SALES for the month of January. Follow the general usage guidelines for tools in the Tool Palette for creating the data definition. Click on the SQL Query button in the Tool Palette of the Data Model view of the report, then move your pointer to the open canvas of your report and draw a small block in that open canvas by clicking and holding while in the open canvas until a box outline appears. Then, release the button, and the SQL Query Statement dialog interface appears. You can either code the SQL query directly into the text box provided or import a SQL statement from flat file using the appropriate button, or even use Query Builder to build your SQL statement using the appropriate button. When finished, Report Builder will verify the validity of the SQL statement against the Oracle database. If you have not done so already, you will need to connect to the database so this verification can happen. The following code block displays the SQL query used in the example:

SELECT unit_name, acct_exec, wk_end_dt, units_sold
FROM unit_sales WHERE wk_end_dt in
('02-JAN-99','09-JAN-99','16-JAN-99','23-JAN-99','30-JAN-99');

Once finished, your newly defined query will display in the Data Model view as one group. This is the Data Model view of a query feeding into a simple tabular report. The key action in the Data Model view for this step is to create two additional groups where the cross-product will comprise the content of the report. In this case, the two groups are account executives listed in the ACCT_EXEC column of UNIT_SALES, and products listed in the UNIT_NAME column of UNIT_SALES. There are four account executives—SUE, SAMEER, DON, and TOVA—and four products sold—printers, TVs, VCRs, and stereos.

First, make enough room between the query object and the main group to draw in your matrix groups. To do so, click and hold on the header bar for the group in the Data Model view and move the entire group down to the bottom of the visible open canvas. This action stretches the line from the query object to the group object. Then, extract the account executive column from the main group in the Data Model view by clicking and holding on the ACCT_EXEC column in the main group, then dragging that column to a point toward the left between the query object and the main group on the open canvas, and then releasing the mouse button. Be sure that the column does not get placed into another group or into the query object, but rather is allowed to create its own group when you release the mouse button.

Notice now that the line from the query object no longer extends to the main group, but instead goes into the group containing the ACCT_EXEC column. You have already learned that this effectively creates a data hierarchy in your report. Extend that hierarchy to include a second group of product names by clicking and holding on the UNIT_NAME column in the main group of the query, then drag that group to a point between the query object and the main group, and to the right of the group you just created containing the ACCT_EXEC column. Now, the line that extended from the group containing the ACCT_EXEC column to the main group in the query should extend to the new group containing the UNIT_NAME column, while a new line from that new group will extend to the main group in the Data Model. Figure 27-13 shows how the new groups containing information for which you will generate a cross-product for the matrix report should appear in the Data Model view of Report Editor.

The final step in creating the matrix Data Model is to draw the fourth group that will become the cross-product of information from the second and third group you created in the Data Model view of your report. This step is done with the use of the Cross-Product tool button in the Tool Palette. Click on that tool button to activate the tool and then move your pointer to a spot above and to the left of the second group you created. Then, click and hold on the mouse button and draw a box that encompasses the second and third group in the Data Model view of your report query. When you have completely enclosed both groups within your box, release the mouse button and Report Builder will draw your fourth group: the cross-product of data from your second and third groups. Figure 27-14 shows the full matrix Data Model as appropriately drawn.

Cross-Product button

You can’t look at your matrix report until you design a Data Model for it. If you try to see the report by navigating through Report Editor to the Live Previewer, you will receive an error saying that you must first create a Layout Model for the report. You can create one either manually or using the Report Wizard. In general, it is a complicated operation to build a Layout Model from scratch. Whereas building a Data Model is usually a bit easier and worth building manually for the experience, you will design your Layout Model with the assistance of Report Wizard and then review the Layout Model produced so that you understand where items in the Layout Model come from and how they are applied to the situation of a matrix report.

First, click on the Live Previewer button in the Report Editor. Since you have not created a layout for it yet, Report Builder will prompt you to create one. Click Yes in the dialog box to begin the process of creating your matrix Layout Model using Report Wizard. The Report Wizard interface now opens, only this time it is displayed with all interfaces accessible via a row of tabs, as shown in Figure 27-15. You define your report style in the interface corresponding to the Style tab. Click on the Matrix Report radio button and then click the Next button. The next interface is your data definition, which you have already identified.

Click Next again, and take a moment to study the information provided in the Rows interface. Here is where the rows in your matrix report are defined. Notice that your interface identifies the ACCT_EXEC column in the matrix row field as the top-most group column for that aspect of the report. Thus, the first group you created in the Data Model corresponds to the rows of your matrix report. You can change the column that defines the rows of your matrix, but you cannot remove them entirely. Click Next again and you can also study the information provided for the Columns interface. This is where the columns in your matrix report are defined. For your report, the columns will display the UNIT_NAME column. Finally, click Next again and you will be able to see the information defined for the Cell interface. This interface may not have anything defined for it yet, but it must have something defined for it in order for your matrix report to work right. You will want to show the information from the UNITS_SOLD column in the cells of your report, but since the data definition for the report retrieves this information for every week in January, you will want to calculate the sum of values for UNITS_SOLD for each account executive by product. To do so, click on the UNITS_SOLD column in the Available Fields text box and then click on the Sum button to display the sum of items from the UNITS_SOLD column for all weeks by account executive by product.

For the sake of simplicity, you should not consider the Totals tab, although when you see the output of your matrix report, it should be obvious the value displaying totals provides. For example, if you wanted to see the total number of units sold by each executive and by product, you could add a total for both the rows and the columns that would give you that information. This information is useful for such things as determining the account executive who moves the most and fewest products, and also for determining which products sell the most and fewest units. Next, consider the Labels tab, where the heading for each row and column are identified along with the width for each of the cells. You can change the header for any label you wish. Finally, consider the Template tab, but don’t use a template for the output of this report. When done, click the Finish button, and Report Builder will go ahead and generate the output of your report and show it to you in the Live Previewer.

Now that you have actually created a default Layout Model for the report, you can see the way your report will actually look on paper. Notice first that there are several headings throughout the report, corresponding to each of the three sets of data displayed in the report. There is a heading for the information from the UNIT_NAME column in the top-left corner of the report output in Live Previewer, corresponding to the label in that tab from the Report Wizard. Another label appears below it for the information displayed from ACCT_EXEC. Still another label appears below each of the distinct values from the UNIT_NAME column, corresponding to the sum of values in the UNITS_SOLD column. Thus, it is easy for you to tell that there are three sets of data displayed in a matrix report: all the distinct values for ACCT_EXEC and UNIT_NAME, and the sum of units sold for all the weeks in January. You know this because you understand the table underlying the report and the information it stores. However, the person reading your report may not. Thus, you should modify the column and row headers to emphasize the account executives and product names, but minimize the fact that information about unit sales figures is broken out by week.

Return to the Layout Model view of your report, displayed in Figure 27-16. Since you want to eliminate the Sum Units Sold label from the report, the easiest way to do it is to find that label in the Layout Model, click on it to activate it, and then press the Delete key. You can instead display the returned values from the UNIT_NAME column directly over the UNITS_SOLD sum information by clicking on the F_UNIT_NAME field once to activate it and then dragging it down into the frame once filled with the Sum Units Sold label. Notice also at the top and left of this layout that there are repeating frames across and down, respectively. These frames are what drive the format of the matrix report. Finally, to get rid of the Acct Exec label appearing over the F_ACCT_EXEC field in the Layout Model, click once to activate it and then press the delete key.

Take another look at the report from within the Live Previewer again. Notice that this time the appearance of the data looks a bit less cluttered. The product names appear directly over their unit sales figures, and the names of account executives appear directly to the left for each row of sales figures. The only problem now lies with providing this report with a distinctive title so that the reader understands what he or she is looking at. Move to the margin region of this report now within the Layout Model view and click on the Text button to use that tool to assign the report a title. Follow the appropriate steps to draw the text box and then enter the title, Sales Figures by Account Executive by Product, January. Figure 27-17 shows the contents of the matrix report again in the Live Previewer, this time with the data and column headers modified for readability.

Take another look at Figure 27-17 and notice that a new product appears in the output, corresponding to a new product line added at the company where these account executives work. This new product is called LAPTOP. It’s a recent addition to the product line sold at this company as of this month, and to be honest the account executives haven’t figured out how to market it just yet. The report shows this—there is no data displayed for any of the executives because none of them have sold a single laptop. However, rather than simply displaying nothing for cells that have no value, it may be more descriptive to show a zero instead. This is an easy task to accomplish. To do so, move to the Layout Model of your report again, and double-click on the F_sumUNITS_SOLD field to invoke the Property Palette for that field. Notice there is a property called Value if Null in the Property Palette for this item. This property has nothing defined for it if there is nothing showing in your LAPTOP column of the matrix report. You can change this by clicking on the property once to activate a cursor in it, and typing in the zero (0) character. When finished, press enter and then close the Property Palette. Now a zero will display in your LAPTOP column even though the database stores NULL for all account executives on sales figures for this product. Figure 27-18 demonstrates.

This chapter covers the conclusion of the basic elements of report design along with the beginning of the advanced features of report design. The development of Parameter Forms, allowing users to specify parameter values in their reports themselves when the report runs, is covered. The chapter then covers the design and implementation of charts in your reports, a more advanced feature of Report Builder that allows for the addition of the visual appeal that a chart can provide. Finally, a case study of matrix report creation is provided to assist your understanding of basic elements of report design from the Data Model and Layout Model perspectives, along with an understanding of the development of matrix reports, an advanced type of report that allows you to assemble a cross-product of information made by several data groups. The content of this chapter comprises 15 percent of material tested by OCP Exam 5.

The chapter covered how parameters manage the behavior of reports. Two types of parameters exist in Report Builder: system parameters defined by Report Builder and assigned values by the user, and user parameters defined by the report developer and assigned values by the user. The chapter identified several system parameters as a refresher from earlier in the unit, such as those that identify the report destination (DESTYPE, DESNAME, DESFORMAT) and those that identify special character content (THOUSANDS, CURRENCY, DECIMAL). To create user parameters, double-click on the Report | Rpt_Name | Data Model | User Parameters node in the Object Navigator module or simply drill down to that item and click the Create button in the Object Navigator Tool Palette. You will then define values for several properties in the Property Palette for your new user parameter. The properties include NAME, COMMENTS, DATATYPE, WIDTH, INPUT MASK, INITIAL VALUE, VALIDATION TRIGGER, and List of Values. Certain constraints exist for which properties apply to user parameters of various datatypes, and you should understand these constraints for the OCP exam.

You should also make sure you understand the creation of a List of Values, or LOV, for the OCP exam. A List of Values is a set of values associated with the user parameter. If defined, it can be used to limit the value set for a parameter. The example was made in the text that if you have a parameter you define called RAINBOW_COLORS, you may want to define an LOV for the parameter that forces it to accept only the values red, orange, yellow, green, blue, indigo, and violet. Clicking on the List of Values property in the Property Palette for the user parameter causes a button to appear. Click it, and the parameter List of Values interface appears. There are two ways to define an LOV using this interface: either as a static set of values or as a select statement against the database. Take some time to review Figures 27-1 and 27-2 again to understand the use of the parameter List of Values interface. Remember, to ensure that no other value can be specified for the parameter than the values defined in your LOV, you must click on the check box labeled Restrict List to Predetermined Values. You can define PL/SQL functions to validate the values specified for system parameters as well. These validation triggers will usually fire twice in the report’s execution—once when the report begins executing and then again when the Parameter Form is validated.

Recall that the chapter also describes a feature of Report Builder that allows you to set up an interface for your users to assign values for parameters in the report. This feature is the runtime Parameter Form. One can be assembled for your report using the Parameter Form Builder, invoked with the Tools | Parameter Form Builder menu bar item in Report Builder, or with the Parameter Form Builder button available in the Parameter Form view of Report Editor. The Parameter Form Builder interface lists out all system and user parameters available in the report. You can include any and all parameters by clicking on the name of the parameter to highlight. You can modify the label that will appear along with the text box in the runtime Parameter Form indicating the parameter the user is defining a value for. Three important controls are built into the runtime Parameter Form as well, including Run and Stop, and Next Page and Previous Page. The last two controls are activated automatically if you have more parameters in your Parameter Form than can fit into one page.

Your more advanced reports may make use of embedded charts, so you should understand how to assemble charts in your reports for the OCP exam. You can use an existing report created with Graphics Builder or generate one for your report on the fly with the Chart Wizard. Four aspects must be defined for your chart in order to use it, and those aspects are the chart type and subtype, values for display on both chart axes, frequency of chart appearance, and a filename and location to which to save the chart graphics file. Five types of charts exist in Chart Wizard: column charts, bar charts, pie charts, line charts, and mixed charts. For all chart types except pie charts, you must define columns for which information will be drawn to show on the category (or X) axis and the value (or Y) axis. Charts can appear at the following frequencies in a report: at the beginning, at the end, or at breakpoints in each group on the report. Finally, charts are stored as files in Oracle Graphics Designer format with the extension .ogd on your file system.

If you have an existing chart that you would like to use in a report, you can create a chart object in the Layout Model of your report and associate it with your existing chart file through the definition of the Chart Filename property. The chart you use may have parameters and columns already defined in it that you want to feed information into from the report. To do so, there is a special interface that allows you to map chart parameters, columns, and queries to report parameters, columns, and groups. This interface is called the Chart dialog interface, and it is invoked through a button appearing when you click on the Parameters and Columns property in the Property Palette for your chart object in the Layout Model of your report. The chapter covered the association of chart parameters to report parameters using the Parameters tab in the Chart dialog interface, as well as the association of chart queries and columns to report columns and groups using the Columns tab in the Chart dialog interface. Be sure you understand the use of this interface for the OCP exam on designing and running reports.

The final area of this chapter covers the design and modification of matrix reports using the Data Model and Layout Model views available in Report Builder. Creating the Data Model for a matrix report happens in four steps. The first of those steps is to create the report in the Object Navigator module. After that, the next step is to create the data definition through the use of the SQL Query tool button in the Data Model view of the report in the Report Editor. Your data definition can be very basic—a select * from tbl_name will suffice, if a bit slow on performance (depending on how much data is being summarized in the report). After that, you will have a query object in your Data Model with one group, which is the data definition for a simple tabular report. The third step is to create two additional groups, one for each of the datasets that will become the row and column names appearing down the left side and across the top of the matrix report. The final step is to create the fourth group, the cross-product group, whose data will appear at each juncture or cell where rows and columns intersect.

Once the Data Model has been created, you must create your Layout Model in order to view the report in the Live Previewer. To simplify things, you can use Report Wizard to create the basic layout and then modify that layout using the Layout Model. The main things that need to be addressed in the Report Wizard to create your Layout Model include defining the report style in the Style tab to be Matrix, and defining content to appear in the cells of the matrix report, where row and column values intersect. Finally, you can define a template for use in the report Live Previewer view. You can return to the Layout Model view of the report to improve the overall appearance of the report. Some things you may want to address in order to improve the appearance of the report are removing some of the labels for each of the datasets appearing in the matrix report and identifying a title for the report in the margin region of the Layout Model of the report. Note that the driving force behind the row and column layout of your matrix report is the use of repeating frames that store data from the row and column dataset into which the cross-product information line up to in cells. The final point made in this chapter about matrix reports is the use of the Value if Null property for the fields of the report in the Layout Model, which can be used to display zeros if the cell contains a NULL value.

• Chart type/subtype
• Inputs for category (X) and value (Y) axes
• Frequency of chart appearance
• Chart filename

• There are five types of charts definable with Chart Wizard. They are as follows:

• Column chart Vertical bars drawn from category axis to values in the value axis.
• Bar chart Horizontal bars drawn from the value axis to values in the category axis.
• Pie chart Circle cut into subsections based on percentage each section comprises of the total.
• Line chart Line drawn at the intersection of points on category and value axis.
• Mixed chart Combination of column and line chart above.

• The frequencies a chart can appear in a report include:

• Beginning of report
• End of report
• At breakpoints for each group

• Charts are stored by Chart Wizard with the extension .ogd because they are stored in the Oracle Graphics Designer format.
• Existing chart graphics files can be incorporated into reports through the use of the chart object in the Layout Model of your report.
• Two important properties to be defined when using the chart object to include existing charts in reports are Chart Filename to define the filename for your report and Parameters and Columns to map report columns, groups, and parameters to chart parameters, columns, and queries.
• The interface used to map chart and report parameters and columns is the Chart dialog interface. This interface has two tabs, the Parameters tab for mapping the chart to report parameters and the Columns tab for mapping chart columns and queries to report columns and groups.
• Matrix reports are those reports that contain four groups of data. Three of those groups are displayed as datasets in the report. One group acts as the row name, one acts as the column name, and the third represents the cross-product of row to column dataset values.
• There are four steps to defining a matrix data model for a report:

1. Create the new report in the Object Navigator module.
2. Create the data definition for the matrix report.
3. Draw at least two groups to represent row and column datasets.
4. Draw a cross-product group to represent the cell dataset.

• The easiest way to define a Layout Model is to use the Report Wizard to define the repeating frames that comprise the foundation of the Layout Model.
• You can return to the Layout Model later to refine the report layout, such as by getting rid of the labels for the cell, column, and row content and adding a title for the report to the margin region of the Layout Model.
• You can also set up to substitute zeros for NULL values in cells with the use of the Value if Null property in the Property Palette.