Advanced Generic Reporting with Oracle Reports Developer

Ileana Balcu, Dulcian, Inc.

Introduction

Oracle Reports is a tremendously powerful reporting tool. However, building the reporting portion of a system is usually time consuming and tedious. There are two possible strategies for creating reporting systems:

· Create multiple reports, sometimes in the hundreds for a complex system

· Build a small of generic, highly flexible, complex reports

There are two types of reports that can be included in a generic system:

1. Summary reports, generally used by managers, consist of numbers that can be summarized and displayed in various ways, including number of customers, merchandise stock, amounts in general ledger systems or in accounting systems, etc. These numbers can be displayed in an organization structure, an accounting book structure, grouped by departments, service representatives, etc. These report types have a similar structure, but they usually require complex calculations. One of the most efficient ways to build these reports is to use temporary tables or views cast from database object types.

2. Detail reports require a great deal of customization of elements including the columns, tables, filters, and the sorting and/or breaks.

This paper presents some useful tips for building complex generic reporting systems. In addition to presenting the technical details for creating these two types of reports, this paper will also discuss the advantages of having a reports repository.

Summary Management Reports Based on Temporary Tables

Oracle Reports is sophisticated enough so that resorting to temporary tables is rarely necessary for traditional reports development. However, generic reporting systems require the ability to generate any one of perhaps hundreds of possible reports. It is very difficult to build the system so that each report will run with acceptable performance. Populating a temporary table as an intermediate step in the running of the report can often improve performance and reduce (or at least compartmentalize) development complexity.

At Dulcian, we have developed a number of reporting systems that relied on temporary tables. One of them was a General Ledger system that displayed account values and summaries in a hierarchical book structure or in an organizational structure. The columns in the reports were very flexible, and could consist of comparisons between fiscal periods (quarters or years), comparisons between organization units, budget vs. financial, or various accounts.

In another system used for recruiting where potential employees had to be tracked in their various statuses and be moved from department to department, temporary tables were also helpful. The calculations in this case were too complex to be resolved with a simple query, so a temporary table had to be created and populated using PL/SQL code. A global temporary table was needed because the structure could be up to 5 levels deep, and a simple rollup function was not adequate.

The temporary table has the following structure:

TMP_GLREPORT_OID	Row identifier, also used for ordering
LEVEL_NR	Level used for display purposes and conditional formatting, such as bolding upper levels
NAME_TX	Text identifying the row, such as the account name or the department name
DRCR_CD	Debit/Credit indicator – used for general ledger systems
NET1_CY…NET6_CY	Actual numeric values

The report is then simply run as a query from this global temporary table. All of the calculations will be in the PL/SQL that populates the temporary table. In another paper presented at the ODTUG 2004 conference, Michael Rosenblum discusses the details of efficiently building the temporary table or a view cast on a database object type.

Developers need to be aware of two technical details in Oracle Reports that are needed in order to make this reporting system work. The first one involves displaying the column headings correctly. The other one is the correct placement of the code for populating the temporary table or view. These issues are discussed in the following sections.

Horizontal Repeating Frames

The temporary table’s columns have generic names: column1,2,3, etc. that have no meaning to the user. After creating the report, the default column headings that the Reports Wizard creates have to be deleted and replaced with a repeating frame that will hold the actual column headings (See Figure 1). In order to accomplish this, you will need the following:

· A repeating frame based on the ReportColumn group - The Print Direction property for this frame is “Across” to extend the new columns horizontally. The Vertical elasticity property must be set to “Expand” in order to allow longer column headings to expand on multiple rows.

· A field with the source heading (that must be in the group ReportColumn) - This field must be inside the frame described above and have the Vertical Elasticity property set to “Expand” as well.

· The size of the frame must match exactly the size of the columns and must be aligned with the columns.

Figure 1 – Frames with “Across” Print Direction for headings

It is a somewhat tedious task to correctly align and size the headings and the columns. However, it is important to do this because a few pixels of deviation will multiply and will be significant towards the end of the page. You should use the zooming tools to align the frames correctly.

Temporary Tables

When you use Global Temporary Tables or Object Types cast into views, each session stores its own data and the other sessions/users will not see it. When the session ends, the content of the Temporary Table will automatically be deleted, so that it does not take up unnecessary space. Users can select, insert, update and delete rows from temporary tables with the same commands that are used for normal tables.

The DDL command to create a temporary table is:

CREATE GLOBAL TEMPORARY TABLE tmp_table (

Column1 VARCHAR2(2000),

Column2 NUMBER(10) ,

…)

ON COMMIT PRESERVE ROWS;

Note that Oracle Forms and Reports always run in different sessions. When you need to use a temporary table for a report, the code to populate the temporary table should be included in the report in the AFTER PARAMETER FORM trigger. It should not be called in the parameter form because Reports always runs in a new session and will not see the data.

Detail Generic Reports

There are times when there is a lot of information contained in fields in one table or in a few connected tables, and reports are needed that will only show a few of these fields at a time. One manager might be interested in the sales associated with the customers, another one in a customer’s location, a third one in a customer’s interests. It is possible to create one report for each of these requests, but this means maintaining more reports. Creating a single report to handle any one of these requests takes longer to design and build, but is much easier to modify and maintain and it will support a larger range of possible requests.

Oracle Reports Builder allows users to create these flexible reports. The only real problem is the inability of dynamically setting a variable field length appropriate for each case. This section includes a description of the workaround used to solve this problem by creating two sizes of fields.

The following is a description of the techniques used to customize the queries.

1. Lexical Parameters

Lexical parameters can replace all parts of the query upon which the report is based.

SELECT empno,ename

FROM emp

&p_where

In the example above, the lexical parameter “p_where” can replace the WHERE or ORDER BY clauses or a combination of them. The lexical parameter can be built either by the application calling the report, or by the report itself in a program unit such as an AFTER-PARAMETER-FORM trigger or a formula column.

4. Generic Sorts

This is the simplest application of the lexical parameters:

SELECT empno,ename

FROM emp

WHERE deptno=30

ORDER BY &p_sort

The user can choose to order the report alphabetically or by employee number by selecting a value from a list item. You need to remember to set an Initial Value for the p_sort parameter, so that the user will not get an error if he/she does not select any value.

5. Generic Breaks

If users need to view summaries grouped by various criteria such as summary per department or summary per job type, then you need to create generic breaks as shown here:

SELECT empno

,sal

,&p_break break

FROM emp

WHERE deptno=30

ORDER BY &p_sort

In the Report Wizard you can set the break column in the master group and use either “deptno” or “job” as values for the p_break parameter.

6. Generic Filters

This is another specific application of the lexical parameters in which you can control the WHERE clause of the query by adding any appropriate filter. The user can then select the column to be filtered, the operation (which can be =, <, LIKE, IN, etc.) and the filtering values.

SELECT empno,ename

FROM emp

WHERE &p_where

In the example above, the initial value of the p_where parameter should be 1=1 for an unfiltered report.

7. Columns to Display

The basic idea for selecting different columns to display is to use aliases for the columns as shown here:

SELECT &p_column1 column1,

&p_column2 column2,

&p_column3 column3

FROM emp

…

You need to set the initial value of the p_column parameters to ‘xxxxxxxxxx’.

Note: You can find more detailed descriptions of the lexical parameters, generic sorts, filters, break columns, and columns to display in Oracle Developer: Advanced Forms and Reports (Koletzke & Dorsey, Oracle Press, 2000).

A Practical Example

This following query was used for a Customer detail report:

select &cp_break1 break1_column,

&cp_break2 break2_column,

&cp_columns_tx

from &cp_from_tables

where &cp_where_tx

order by &cp_order_by

The Initial Values properties for the placeholder columns are shown in Table 1:

Placeholder Name	Initial Value
cp_break1	rpad('q',200,'q')
cp_break2	rpad('q',200,'q')
cp_columns_tx	rpad('q',200,'q') col_narrow1, rpad('q',200,'q') col_narrow2, …, rpad('q',200,'q') col_narrow10, rpad('q',200,'q') col_wide1, rpad('q',200,'q') col_wide3,…, rpad('q',200,'q') col_wide9
cp_from_tables	RC_CUSTMR cust
cp_where_tx	rownum<50
cp_order_by	3,4

Table 1: Initial Values for generic lexical parameters

The creation of the query, as displayed with the Initial Values as described in Table 1, will create fields for 10 narrow columns and 5 wide columns that will each allow up to 200 characters. It is important to set the initial column lengths in the query to a large number because there is no other way to manually change the layout column width. Any character other than ‘q’ would have done the same thing.

Figure 2 shows an extract from the Oracle Forms Developer front-end that defines the report.

Figure 2 – Parameter form for a detail generic report

Using this front-end, users select the columns to display from an LOV. The LOV can be populated from the Oracle metadata, or can be maintained by developers in a local repository. The users can select either a narrow or a wide column.

A PL/SQL function will then set the column names corresponding to the selected wide or narrow columns as well as a special code (such as ‘<<do not display>>’) in the other columns that are not needed. The report will contain two sets of columns as shown in Figure 3 (Displayed this way for clarity although, in the actual report the two sets of columns and headings will be on top of each other).

Figure 3 – Layout Model for columns with 2 widths

The columns that have to be displayed will be controlled by the following format trigger:

function F_narrow_column_1FormatTrigger return boolean is

begin

-- Automatically Generated from Report Builder.

if (:col_narrow5 = ‘<<do not display>>’)

then

return (FALSE);

end if;

return (TRUE);

end;

Storing Report Definitions in the Database

When analyzing the reporting requirements for a system, there are often a large number of reports that are similar in structure and format. When a lot of reports are integrated, the set of parameters to call these reports usually becomes extremely complex, since you must specify columns, rows, calculations, filters, breaks, etc.

The best approach is to use a reports repository. This has multiple advantages including:

1. The list of parameters passed to the reports is reduced (to only one parameter namely, the report ID);

2. Once created, the reports are reusable. They can be copied so that a similar report can be easily created.

3. The reports parameters can be displayed on the report.

4. It is very easy to print a list of reports.

Because the structure of the parameter form becomes so complex for these generic reports, two parameter forms are used: one for super-users or developer-user teams who maintain the repository and another one for the users. The users will only pass simple parameters such as dates or department IDs to already designed reports.

A report definition usually contains:

· The report ID

· A name and a description used by developers to identify the report

· A type or folder to group reports

· One or more titles and other information to be displayed in the report

· A report type, if needed, such as Detail, Summary, Matrix, etc.

· The structure identifier if you have an accounting or an organization structure report

· An active flag

Each report can have specific details. These can define columns, rows, statistics, breaks (or grouping), sort order and filters.

A column definition may contain:

· An order number

· A heading for the report

· Various filter values, such as: start date, end date, a department ID, a location ID, an account type or identifier

· References to other columns for calculations: variances, percentages, totals

· A format code: In this case, we used a narrow vs. wide format where each column could be simple or double-sized

· A reference to a table column from an Oracle metadata repository or a custom user repository

Breaks, statistics and sort orders can have

· An order number

· A reference to a table column from an Oracle metadata repository or a custom user repository

Figure 4 shows an example of the parameter form used to call the report. This is built just like a normal form where all of the data is stored in the database tables. The Preview Report button commits the changes and calls the report using only one parameter, the report ID.

Figure 4 – Parameter form for a general ledger generic reporting system

After successfully using a reports repository for a general ledger reporting system several years ago, we continue to use and extend it for other types of reporting systems. It has proved to be a very good solution.

Conclusion

Reporting is one of the key areas of system design. Access to data is a core user requirement that is too often neglected or passed off to junior developers.

Using generic techniques, you can provide a tremendous amount of support for reporting with very little development effort. In the hands of a senior developer, reporting can shift from mindlessly producing hundreds of similar reports to the challenging task of providing all possible reports by building a customized reporting system.

This approach is a vast improvement over delivery of ad hoc query tools to users. With a generic reporting system, it is possible to control the queries used to generate the reports and ensure that users are getting the information that they think they are getting.

Additional Documentation