A flexible reporting front-end that allows users to select sorts, breaks and filters dynamically is a powerful addition to any major reporting system, but is usually a very complicated and time consuming application to create, even for the most experienced developer. If the time is spent on creating a template, a flexible reporting front-end application can be created in a day by a novice developer.

In most major reporting systems, there are 10-20 distinct reports with hundreds of variations. The conventional way to build this type of system is to create a report for each variation that the user wants. There are two problems with this approach:

1. What happens when the data model changes? The same change needs to be made to hundreds of reports.
2. Every time the users want a different variation, a new report needs to be written

An easier and more efficient method would be to write the 10-20 distinct reports and place lexical parameters in the main queries. Then build a front-end that allows the user to select the sort, break, and filter options they desire. When the user hits the print button, the front-end builds valid SELECT, WHERE, and ORDER BY clauses to fit the choices made by the user. The front-end then passes these clauses to the report using a parameter list, and the report automatically conforms to the user’s selections. An entire article can be written on this method; but this article will focus on the front-end that manages this system. This front-end is the most complicated application our firm has ever built. For this reason, we decided to spend some time and design a template so that a novice developer can build a production-level Flexible Reporting Front-end in a day. This paper will be organized into four sections:

• Section I will describe a completed Flexible Reporting Front-end.
• Section II will step through the use of the template.
• Section III will explain the parts of the template.
• Section IV will show the code behind some of the more important program units.

This section shows a flexible reporting front-end that was built with the template, and describes how to operate it.

The above screen shot shows the main screen of the Flexible Reporting System front-end.

There are four general areas of this application:

1. Breaks/Sorts
2. Filters
3. Preview
4. Saving/Opening Settings.

Each will be discussed separately.

The break and sort drop-down lists will be empty until a report is selected.

Once a report is selected, the Break and Sort drop-down lists are populated with the columns that can be sub-totaled or sorted on for the specified report as shown in Figure 2.

If the user selects a different report, the break and sort choices are cleared and the lists are re-populated with the columns that are valid for the chosen report. Error checking is built in so the user cannot select the same sort column twice, nor can they sort on the column they chose as a break. This sort will happen automatically.

Once a sort is chosen, the user can specify that sort to be in Ascending or Descending order.

Figure 3 shows the Awards tab that has four filter blocks on it. Until the user clicks on the Activate check box for the block, the block is ignored.

Figure 4 the Award block activated and two choices set. If the report were run now, it would only show the records with an award of BD or BP. If the activate button was de-selected, the items in the list would still be selected, but the report wouldn’t filter them.

Figure 5 is an example of the Preview tab using the filter options shown in the previous section. This will show the user what data will appear on the report without having to generate the report.

Not only does this save the user time by not having to generate a report to find errors, but this application can also double as a finder. The user can double click on any one of these awards, and control switches to the Award Editor with the selected award activated.

If the user wants to run the report with the same options multiple times, they can save the settings so that they can be recalled with just a click of a button. To save the settings, the user clicks on the Save button (Disk Icon) on the toolbar. The Save window appears showing the user the names that are already in use. The user types in the name they want and clicks on OK. These selections are saved to the database.

To use a saved setting, the user clicks on the Open Setting button (1^st button on the toolbar). A dialog box similar to the Save As dialog box shown in Figure 6 appears with the title "Open."

The user clicks on the setting they want, and clicks on OK. The application automatically sets itself up according to the specifications of the setting file.

This section explains the steps necessary to create a filter block similar to the awards block shown in the previous section. For this example, we will use the EMP table in SCOTT/TIGER. This section was included to show how easy it is to build this application once the template is designed.

1. In Forms Designer, open two files: the object library and the base template.
2. Click on the base template heading and choose File | Save As from the menu. Rename the file EMP.FMB.
3. Click on the CODENAME object group in the object library and drag it to the Object Groups section of the base template. Forms will now ask if you want to Reference or Copy the item. Choose Copy. On the resulting dialog box, click on OK. This step will copy in two blocks (CODENAME, CODENAME_HEADER) and one canvas (CODENAME).
4. Rename the new blocks and canvas, replacing CODENAME with EMP.
5. On the EMP block’s property sheet, change the Base Table to SCOTT.EMP.
6. In the EMP_HEADER block, change the default value of the following fields (The purpose of these fields is explained in the next section):

• DATATYPE – change to NUMBER.
• HEADING – change to Employee
• COLUMN – change to EMP.EMPNO

1. In the STUFF block, change the default value of LIST_OF_FILTER_BLOCKS to EMP.
2. Create two items in the EMP block. Name one EMPNO, and change the datatype to NUMBER. Name the other ENAME.
3. In the EMP block, bring up the property sheet for CN_CODE. Change the Mirror Item to EMPNO, and change the datatype to NUMBER.
4. In the EMP block, bring up the property sheet for CN_NAME. Change the Mirror Item to ENAME.
5. On the EMP canvas, edit the Title text item. Change it to read "Employee."
6. In the TABS block, bring up the PL/SQL editor for TAB1. Replace <CANVAS> with EMP. Click on the Compile button, then click on Close.
7. Bring up the Layout Editor for the canvas C_MAIN. Select View | Stacked Views and select EMP from the list of canvases. This will show the EMP canvas on top of the C_MAIN canvas. Click on the EMP canvas and drag it to the desired position on the screen.

The result of the above 13 steps is shown in Figure 7.

The Employee filter block is fully functional. The user can activate it, select items from it, and when they run a report, the form will build a valid Where clause for it. Save and Open also work automatically with this new block.

There are two parts of the template: the object library and the base template.

Refer to the screen shot in Section I that shows the entire Awards tab. This screen is made up of 5 canvases: The main canvas, which is shown from the tabs on up, the awards canvas, the event canvas, the presentation site canvas, and the conference canvas. Each filter type has its own blocks and canvas associated with it. The object library holds this information in Object Groups. Each Object Group has all of the items necessary for a filter type. Each filter type has a header block that holds the following items:

• Param – Stores a comma-delimited list of ID’s of all the items currently selected by the user for the block. This will be used to build the where clause and is built automatically by the system.
• Desc_Param – Stores a comma-delimited list of the descriptions of all the items currently selected by the user for the block. This will be used to build the list of settings shown at the bottom of the report and is built automatically by the system.
• DataType – Stores the data type of the Primary Key for the block. This needs to be populated by the developer.
• Field_List – Stores the name of all the fields in the block. This does not need to be changed since the default value will always be correct.
• FilterType – Stores the name of the filter type for the block. This does not need to be changed since the default value will always be correct.
• Heading – Stores the heading used for this block in the list of settings shown at the bottom of the report. This needs to be populated by the developer.
• Column – Stores the Table_Name.Column_Name that the block will be filtering on. This will be used to build the Where clause and needs to be populated by the developer.

The three of the most common filter types are: Base Table, Multi-LOV, and Checkboxes.

A Base Table filter was demonstrated in Section II. The filter values for the blocks are retrieved directly from a database table. The user then selects the checkbox next to the filter value they want. If they want to show all but one, they would click on Include All, then de-select the items they don’t want to show.

The base template holds all of the code for this application. Without any coding, this form will save, open, and delete settings, and run reports will break, sort, and date filter information. When a filter block is copied in from the object library, the code looks at the LIST_OF_FILTER_BLOCK field in the Stuff Block, and performs all necessary actions on the blocks listed there. Each tab has a WHEN_MOUSE_CLICK trigger attached telling the system which canvases to show when the tab is clicked on. No matter the number or the types of filter blocks the user has, this template will handle them with minor modifications to some block items. No change in any program unit is necessary.

This section will show the code behind some of the more important programs units needed to make this application work.

All filter blocks have two parameters associated with them:

• PARAM – Holds the ID’s of the items the user has selected.
• DESC_PARAM – Holds the description of the items the user has selected

Both of these parameters store these values in a comma-delimited list. Each time the user changes the items they have selected for a particular block, this procedure is called to update the two parameters.

This package holds the procedures needed to save the settings the user made to the database. The procedure that will be discussed here is the Base_Table procedure. This will be used for all base table filter blocks.

This function will build a valid Where clause for a block that is based on a character ID.

A similar function was built to handle numeric ID’s called F_Build_Where_Num. This function will not be shown here.

With this template, hard coding sorts, breaks, and filters into reports is a thing of the past. Not only does using lexical parameters greatly reduce the number of reports that need to be designed, using the template cuts the design and build time of the front-end to manage it all from a month to a day.