Report Preparation/Management |
The Report Preparation/Management screen enables you to associate SQL stored procedures with queries and reports.
You can define relatively simple reports in the Query/Report Generator that use the results of the stored procedures. You can define a list of simple reports with the name of the stored procedure used by each one and execute the procedure whose results the report generator will reference when it executes the report.
Stored procedures support difficult reporting requirements or analyses. They prepare data in the database for the execution of one or more reports. A second procedure can perform cleanup work (e.g., delete data related to previous runs of the procedure).
You can create SQL stored procedures of any complexity, including storing data in specially defined tables that the report generator can use to perform reporting or analysis beyond the power of the report generator alone.
Each time a report executes the SQL stored procedure, you provide a unique value called the instance ID.
Many standard reports display in the Reports list box in the Standard toolbar. Refer to Standard reports. To run a standard report on Oracle that is dependent on stored procedures, refer to Oracle-stored procedure dependent reports.
In addition to the Query/Report Generator, you can use Web Modules Reporting or any other reporting tool sold separately (such as Microsoft Access) that can attach to the database. Refer to the related documentation for these products.
The field in the top section of this tab defines the data that displays.
Report ID
Identifier of the report currently selected in the grid and for which the stored procedure provides support. This field has a list box. The report description displays to the right of the ID.
The data fields in the bottom section of this tab define basic information about the report. Unless otherwise indicated, these fields are required.
Description
Description of the stored procedure. Optional, free-format area.
Related Query/Report generator report name
Query/Report Generator report associated with the stored procedure in the Name of processing stored procedure field. This field has a list box. Optional.
Note: Report titles that begin with RG are standardized reports.
Name of processing stored procedure
Name of the SQL stored procedure to execute in preparation for running the report in the Report ID field. The stored procedure may include any statements and operations that it supports.
Note: The procedure should not include the creation of tables or views that it requires; tables and views should be created separately in the database.
Name of cleanup stored procedure
Name of the SQL stored procedure executed to cleanup (delete) data from a previous execution of the processing stored procedure. To ensure that no data from a previous execution will be mixed with data for the current execution, you should execute the associated cleanup stored procedure with the same instance ID before you run a processing stored procedure. The cleanup stored procedure in this field must take exactly one argument or parameter (e.g., an instance ID) and should consist of SQL delete statements in the following form:
DELETE FROM x WHERE instance_id = IN_instance_id
where x is a table into which the associated processing stored procedure inserted data and IN_instance_id is the name of the argument to the cleanup stored procedure.
Stored procedure information
The values of any parameters or arguments that the stored procedure requires when it executes. Separate the values with semicolons (;). Enclose strings, characters, dates, and times in single quotes (‘). When the stored procedure is executed, the semicolons are converted to commas, creating a comma-separated argument list. Required if the Stored procedure field on the Manage tab of the Queries/Reports -> Query Report Generator screen specifies a procedure name.
Note: Do not include the instance ID in the stored procedure parameters; it is submitted separately as the first argument in the stored procedure.
Prepare now using instance ID
If this field has a check, the processing stored procedure is executed. You must enter a value for the instance ID in the unlabelled field to the right. Because this value disappears after you process the record, for reference purposes you should type this number in the Description field.
Delete data using instance ID
If this field has a check, the data stored in connection with an instance ID is deleted. You must enter a value for the instance ID in the unlabelled field to the right.