Spinning Web Reports from Oracle Databases with Excel
Gerhardt Martin Rumpi Gravenstein
Goodyear Tire & Rubber Company Rumken, Inc.
Michael A. Rife Jen Croy
Goodyear Tire & Rubber Company Rumken, Inc.
Topics of Discussion
· Introduction
· Why Excel?
· Implementing the Oracle Report Queue Manager
· Implementing the Excel 97 Report Server Program
· Report Generation Programming Issues
· Conclusion
Introduction
This paper will describe how to design and implement a report server program running under Microsoft Excel 97 and a report queue management package in an Oracle database. The Excel report server program and the report queue management package are sub components of a larger global reporting application system that provides users with the capability to request reports from an intranet web site. Figure 1 shows where the Excel report server and the Oracle job queue package fit in the overall system design.
Figure 1 – Global Reporting Application Architecture
In a typical usage of this system, a user would access the Oracle Web Forms Application and enter a request for a report. The system then processes the request and delivers the report as an Excel workbook file attached to an e-mail message. The report queue manager and the Excel report server are the key players in the back-end process that makes this system possible.
Why Excel?
There are many products out on the market that provide database reporting capabilities, so “Why Excel?” is a good question. Here is a list of the factors that led to the decision to use Excel:
· The users of the system desire the capability to edit and mark up reports easily.
· The users’ previous reporting system was based on reports generated in Excel.
· Excel is a standard application available on all of the users’ desktops.
· The complexity of the reports.
Building a server program that uses Microsoft Excel to generate reports has some distinct advantages and disadvantages:
Advantages
· The Visual Basic for Applications programming environment is included in Microsoft Excel 97 so purchasing a report development tool is not required.
· Microsoft Excel includes a deep tool chest of rich reporting capabilities that are easily accessible from within the Visual Basic for Applications development environment.
· Excel is a widely used, relatively robust, and reasonably supported software product with a wealth of documentation available.
Disadvantages
· Running a report server program under the Excel runtime environment means that a Windows NT/98 machine is required in the system architecture.
· The development and maintenance of an Excel based Visual Basic for Applications program to control the report serving process is required.
· Excel is database friendly, but not to the degree of other report development tools that are targeted specifically at database environments.
Implementing the Oracle Report Queue Manager
The Oracle report queue manager was created with a queue table, a table pre-insert trigger, a PL/SQL batch package, and the Oracle DBMS Job environment. The basic process is shown on the next page in Figure 2. It begins with a user requesting that a report be run. This request is made from an Oracle Web Form application. When the user makes the request, a row is put into the job queue table that serves as the Excel report queue. Placing the row into this table fires a pre-insert trigger which schedules a DBMS JOB that will run at the time the user requested. At the requested time, the DBMS_JOB starts the report by running the report job through the PL/SQL batch package. This package, the coordination point between the Oracle and Excel environments, performs the following functions:
1. Starts the data processing for the requested report
2. Signals an alert indicating to the Excel Batch environment that a report is ready to be run by the Excel server
3. Provides the Excel server an interface by which it can request the next report
4. Provides the Excel server an interface by which it can mark a report complete or in error
Figure 2 – Oracle /Excel interface
The PL/SQL batch package is accessed by Excel
via three functions within the package, get_excel_job, mark_excel_job_done, and
mark_excel_job_error. The function
specifications are as follows:
FUNCTION get_excel_job(
job_no OUT
NUMBER, -- Identifies report to run
pgm_na OUT VARCHAR2, -- Type of report
err_msg OUT
VARCHAR2 ) -- Error message if function
returns false
RETURN BOOLEAN; --
True=Success/False=Failure
FUNCTION mark_excel_job_done(
job_no IN NUMBER, -- Job to mark ExcelDone
err_msg OUT
VARCHAR2 ) -- Error message if function
returns false
RETURN BOOLEAN; --
True=Success/False=Failure
FUNCTION mark_excel_job_error(
job_no IN NUMBER, -- Job to mark ExcelError
msg IN VARCHAR2, -- Excel Error message
err_msg OUT
VARCHAR2 ) -- Error message if function
returns false
RETURN BOOLEAN; -- True=Success/False=Failure
The functions do pretty much what you would expect. The get_excel_job function checks the report queue for reports that are ready to be processed by the Excel server. If a report job is found, that job number and report program name are returned to the Excel server. The job number is used by the Excel report server to locate the parameters and data associated with the report. If no job is found the function waits for an alert that signals that a job is ready to be processed. The mark_excel_job_done and mark_excel_job_error update the report job queue with a processing status, EXCELDONE or EXCELERROR. The mark_excel_job_error function also records any error message text passed from the Excel report server into an error message column in the report queue table.
Implementing the Excel 97 Report Server Program
The report server program is housed in an Excel add-in file and runs under the Excel executable environment. Excel add-ins allow programmers to customize and extend the functionality of Excel through the use of a sub-set of the Microsoft Visual Basic programming language known as Visual Basic for Applications (VBA). The VBA programming environment in Excel is quite similar to the ide provided by the full version of Microsoft Visual Basic and can be accessed directly through Excel. Each workbook file in Excel has a Visual Basic for Applications project associated with it in the integrated development environment (IDE). This VBA project exposes a default set of Excel visual basic programming objects that can be manipulated in code. The Excel report server program was created as a code module in the VBA project of an Excel add-in file. The remainder of this paper assumes that the reader is proficient in the use of Microsoft Excel 97 and the Excel Visual Basic for Applications IDE.
There is no real magic in writing a server program. The traditional recipe is to code a never-ending processing loop that listens to a network resource and reacts with an action when a client request comes forth. The following is a high level description of the tasks required and how the server program interfaces with the report queue manager in the database:
1. Programmatically establish contact with the database from within the Excel environment.
2. Begin a continuous processing loop in the Excel report server program.
3. Receive a reporting job number and that report job’s associated program name from the report queue manager in the database.
4. Extract the report job’s data from the database.
5. Execute a program in Excel that renders the data into a finished report.
6. Dispatch the resulting report.
7. Notify the report queue manager in the database that the reporting job is complete.
8. Repeat steps 3 through 7 indefinitely.
The requirements of the Excel report server program correspond with the traditional server program design. However, there are some very unique design challenges to consider in this system. Both these challenges and their solutions are identified in the remainder of this paper. The discussion covers, in detail, how to implement a report server program in the Excel environment that satisfies all requirements listed above. Beginning with a complete walk through of pseudocode for the Excel report server program, the discussion continues with an in-depth look at data extract programs and report generation programs. The discussion of the pseudocode has been broken down into five distinct sections for the purpose of discussion. A complete uninterrupted listing of this pseudocode can be found in Appendix A.
Report Server Program Pseudocode Section 1
BEGIN
/* Section 1 *******************************/
ON ERROR GOTO errorhandler:
Code to initialize Excel environment
/* set server state variables */
skip_job = FALSE
get_next_job = True
job_completed = False
job_id = 0
/* set error persistence values */
error_count = 0
WAIT_TIME = 10 /* wait time in minutes */
MAX_ERRORS = 20 /* number of consecutive network/database errors to tolerate */
/* Initialize plsql call strings */
get_jobid_call = “unnamed PL/SQL block calling stored procedure get_excel_job”
mark_job_call = “unnamed PL/SQL block calling stored procedure mark_excel_job_done”
mark_error_call = “unnamed PL/SQL block calling stored procedure mark_excel_job_error”
Section 1 of the pseudocode lists actions that are performed exactly once as the server program is started. The “ON ERROR GOTO ERRORHANDLER:” line announces the intention to trap Visual Basic run time errors and jump to the label ERRORHANDLER: in the code. The server program starts execution by setting up the working environment in Excel. The code to initialize the Excel environment creates and sets the working directory where reports will be stored; allows the user starting the server to choose a database to log into; and minimizes the Excel application window to clear Excel from the server machine’s console. The server state boolean variables are extremely important variables that are going to be used throughout the program. The flow of control in the program does not follow the rules of structured top down stepwise design, therefore there are situations in the execution of the code where one can not be exactly certain which line of code was executed prior to the current line of code. The current value of one or more of the server state variables is examined at runtime to determine what action to take in sections 3-5 of the pseudocode. This is explained in greater detail as the remaining sections of the pseudocode are covered.
The error persistence variable and values are used to control error persistence in the server program. Error persistence, in this context, refers to the server program’s capability to recover from an error condition gracefully and re-spawn itself without intervention. One of the design goals for the server program was to give it the capability to recover automatically after a database connection problem, for instance when the database is shutdown for back up. These error persistence values and the error_count variable are used by the program to control how long the program will time out on a connectivity error, and how many times the program will attempt to re-spawn itself before halting the serving process. The last item in Section 1 is the initialization of Visual Basic string variables that will contain unnamed PL/SQL blocks of code that call stored procedures in the database. Usage of these PL/SQL blocks is discussed in further detail in Section 4 of the pseudocode.
Report Server Program Pseudocode Section 2
/* Section 2 *******************************/
RETRY:
OO4O Visual Basic Code to Establish database connection
OO4O Visual Basic Code to create PL/SQL input-output parameter variables
The very first item in Section 2 of the pseudocode is the label “RETRY:”. This label is the only point of re-entry into the program after handling a visual basic runtime error in the “ERRORHANDLER:” code located in Section 5 of the pseudocode. The next task is to attempt to connect to the database using the Visual Basic objects supplied by the Oracle Objects for OLE (OO4O) programming API. There are two OO4O objects that must be instantiated to establish this connection. The following lines of Visual Basic code in the server program use the OO4O API to establish the database connection.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OPENDATABASE(“database name here”, “scott/tiger”, 0&)
The first line creates an Oracle In Process Server object, which is the root or point of entry into the class hierarchy of all the objects supplied by the OO4O API. Once this object is instantiated, a Visual Basic program must drill down through the hierarchy to create more useful database objects in the API. The second line of code uses the Oracle in Process Server Object created in the previous line of code to establish a database connection object. This database connection object is used throughout the program to allow the report server to communicate directly with the database. The last step in this section of the pseudocode establishes a mechanism for the sharing of bind variables between the Visual Basic report server program and the PL/SQL stored procedures in the Oracle database. The OO4O database connection object supports a collection of parameter objects that allows this communication to take place. The following lines of Visual Basic code add a parameter object to the database connection object and then set the PL/SQL data type.
OraDatabase.Parameters.Add "JOBNO", 0, ORAPARM_BOTH
OraDatabase.Parameters("JOBNO").ServerType = ORATYPE_NUMBER
Note that ORAPARM_BOTH and ORATYPE_NUMBER are custom symbolic constants that are maintained in the report server program for code readability. (See the documentation for OO4O for the actual values required in these API calls.) Once the parameters are established in the database connection object, they can be used in the unnamed PL/SQL blocks that are executed on the Oracle database. The Visual Basic code of the report server program can use OO4O API calls to read and/or set their values. The report server program and the PL/SQL blocks of code communicate through five such parameter objects. The following is a list and a brief description of what information these parameters share between the report server program and the PL/SQL blocks executed on the database:
· JOBNO - a numerical key that uniquely identifies all the information in the database report queue, report parameter, and report data tables that belongs to the report being processed by the server program
· PGMNA - a string value that denotes the report generation program name for the report being processed by the server program
· SUCCESS - a numerical error status value that indicates whether or not a program error was generated when the PL/SQL block runs on the database
· ERRMSG - a string that contains the error message generated should a call to a stored procedure in the database fail to run successfully
· EXCEL_ERR_MSG - an error message string that is passed on to the database job queue manager when the report server encounters an error. This message is stored in an error message column of the report queue table
Report Server Program Pseudocode Section 3
/* Section 3 *******************************/
If skip_job Then
Call database procedure to mark job skipped in job queue table
Test for successful run of database procedure
skip_job = FALSE
get_next_job = True
job_completed = False
error_count = 0
End if
Section 3 of the server program contacts the database report queue manager and signals that an error in report processing has occurred. At first glance in the complete listing of the pseudocode, one might wonder why this code is not in the errorhandler: code section. In fact, that was exactly where it was placed in the initial design. The problem with placing this code in the errorhandler: section is the call to the database procedure that signals the report queue manager. If this code were placed in the errorhandler: section, an unpredictable database connectivity error could be thrown during the execution of the ERRORHANDLER: code. The report server program runs on the top of the Visual Basic call stack, hence any errors that are thrown by Visual Basic in the ERRORHANDLER: code could not be trapped and handled. The error would be forwarded to the Excel environment and an error message would be displayed on the console, effectively halting the report server program. In this situation, halting the report server program is clearly undesirable. The solution is to place this code in the body of the program and guard it from execution except in the case that the server state variable skip_job is TRUE. The coding technique applied here demonstrates why this program does not follow a traditionally structured top down stepwise design. In fact, the flow of control in the report server program behaves more like that of an event-driven program. The report server program must react to unpredictable database connectivity situations, much like event-driven GUI programs are required to handle unpredictable requests from users interacting with the interface. The upshot of this rather strange placement of the code is that connectivity errors can be easily trapped and treated, which guarantees that the report server program will not continue processing other pending jobs until the current job’s error is reported to the database job queue manager. While it is important to understand this section of the server program code, it does not make sense to spend too much time trying to completely understand it at this point in the discussion. It is recommended that the reader forge ahead and revisit this portion of the discussion after gaining a more complete understanding of the entire server program.
Report Server Program Pseudocode Section 4
/* Section 4 *******************************/
Do While TRUE
/* Section 4 : Task 1*******************************/
If get_next_job Then
Job_id = Call database procedure to request a new job
Test for success of database procedure
get_next_job = FALSE
job_completed = FALSE
End if
/* Section 4 : Task 2 & 3*******************************/
If NOT job_completed Then
Call Excel VBA code to process the report
Call Excel VBA code to dispatch the report
job_completed = TRUE
End if
/* Section 4 : Task 4*******************************/
Call database procedure to mark job_id completed in job queue table
Test for success of database procedure
/* Section 4 : Task 5*******************************/
Clean up/reset current Excel environment
job_completed = FALSE
get_next_job = TRUE
error_count = 0
Loop
END
This section of the pseudocode represents the server program’s endless processing loop. Here is a simplistic high level overview of tasks that must be completed by the processing loop:
Task 1: receive a pending request to process a report off of the database report queue.
Task 2: process the report.
Task 3: dispatch the report.
Task 4: Mark the report complete in the database report queue.
Task 5: Cleanup and prepare the Excel environment for the next report.
Notice in the pseudocode that all the statements responsible for task 1 are guarded by an IF statement and that all the statements responsible for tasks 2 and 3 are also guarded by a second IF statement. Execution of either portion of the server program’s endless processing loop are guarded by server state variables mentioned earlier in the discussion of pseudocode Section 1. The server state variables facilitate correct continuous processing of reports in situations where an error has occurred and the processing loop was re-entered after appropriate corrective action has been taken in the ERRORHANDLER: section. Again, it is probably best to ignore the somewhat confusing error handling built into the server program for now and focus on the implementation details of the five major processing loop tasks listed above.
/* Section 4 : Task 1*******************************/
If get_next_job Then
Call database procedure to request a new job
Test for success of database procedure
get_next_job = FALSE
job_completed = FALSE
End if
Task 1: Receiving a request from the database report queue
In the previous discussion of pseudocode Section 3, very little was mentioned about the mechanics involved in running an unnamed PL/SQL block on the database and passing information between the server program and the database environment. At a closer look, the server program uses the database connection object instantiated earlier in the report server program to execute the PL/SQL block on the database. The PL/SQL block calls the stored procedure in the report queue management package that is responsible for identifying the next pending report job. The following line of code is a typical example of how this would be written in Visual Basic using the OO4O API.
OraDatabase.DbExecuteSQL get_jobid_call
The get_jobid_call is a Visual Basic string variable containing the PL/SQL code to be handed to the database for execution. Creating the Visual Basic assignment statement to set up the PL/SQL code string is tedious work, but necessary. (An example of this Visual Basic code can be found in Appendix B.) Here is an example of the PL/SQL code that is handed to the database in a more readable format:
DECLARE ret BOOLEAN;
BEGIN
ret := job_q_package.get_excel_job( :JOBNO, :PGMNA, :ERRMSG );
If ret Then
:SUCCESS := 1;
Else
:SUCCESS := 0;
END IF;
RETURN;
EXCEPTION
WHEN OTHERS THEN
:ERRMSG := SQLERRM;
:SUCCESS := 0;
END;
Remember those OO4O parameter objects that were added to the database connection object in Section 2 of the pseudocode? They are being used here as bind variables in the PL/SQL code being sent to the database. The value of the variable :JOBNO being used in the PL/SQL above is available to the server program code through the OO4O API after executing the DBExecuteSQL call that runs the unnamed block on the database. The following line of Visual Basic code reads the value of :JOBNO into a Visual Basic variable:
job_id = OraDatabase.Parameters("JOBNO").Value
In this case, the values of all the database parameter objects are being set during execution of the PL/SQL code on the database and are then read in the Visual Basic server program. This channel of communication between the PL/SQL code executed on the database and the server program is provided and managed by the OO4O database connection object being used in the server program. It is also possible to set the value of one or more of the database connection parameters in the visual Basic code, then run the PL/SQL code in the database to pass information from the Visual Basic report server program to the database. In fact, this technique is used in the calls to mark a report complete and to mark a report aborted. There is another twist in the process of requesting a pending reporting job that should be noted. The stored procedure being called in the database will not return until a reporting job is pending in the database job queue. This means that the server program will hang or sleep until pending report jobs arrive. At first this might appear to be a risky proposition, and this author was unsure if the Visual Basic environment, the database connection, and the stored procedure running on the database would tolerate long periods of sleep without timing out or hiccuping on the network. The report server program in production at Goodyear over the past 12 months has not experienced any problems related to this sleep state. There have been numerous occasions where the report server program has slept more than 18 hours waiting patiently for a pending report to be passed back from the stored procedure in the database. Once the stored procedure returns and the server program awakens, the parameter values are interrogated to check if the stored procedure encountered an error. If an error condition is discovered at this point, the actual server program throws an error and the ERRORHANDLER: section of the code is invoked. If all is well, the server program accepts the :JOBNO and the :PGMNA (program name) and moves on to the next task in the processing loop. Before moving out of this section of the code the server program sets the server state variables get_next_job and job_completed to FALSE. Setting the job_completed status variable to false allows the report server program to enter the report processing and dispatch area of the code. Setting the get_next_job status variable to false prevents the server program from requesting a new pending report job from the report queue manager until the report server program has handled the current reporting job appropriately.
Task 2 & 3: Process & Dispatch the report
/* Section 4 : Task 2 & 3*******************************/
If NOT job_completed Then
Call Excel VBA code to process the report
Call Excel VBA code to dispatch the report
job_completed = TRUE
End if
Task 2: Process the Report
Here is a high level overview of report processing. This topic will be addressed in greater detail in the discussion of report generation programming issues. In task 1, a job number (:JOBNO) and a program name (:PGMNA) were passed to the server program by the database report queue management package. These two values are all that are required to completely identify the data in the database associated with the report and the Visual Basic programs that are invoked to extract the data and render the report. The report server program calls the appropriate data extract sub-program passing it the database connection object and the job number. The data extract program queries the database for the report’s data and populates a spreadsheet. After the data extract sub-program finishes, the appropriate report generation program is loaded and the report is rendered. The server program then unloads the report generation sub-program and continues with the next task in the processing loop.
Task 3: Dispatch the report
The process of dispatching reports is probably one of the most open areas in the report server design, and for that reason, implementation details of this process will not be covered in this paper. There are many ways this process can be implemented depending on the needs of its users. The report server being used at Goodyear supports two options. The first attaches the completed report Excel workbook file to an e-mail and disburses it to the original report requestor, while the second prints the report to the requestor’s printer. These two options just happened to best serve the customer, but there is no real reason why completed reports could not be dispatched to a location on the web or be served back to the report requestor’s web browser in some cases. The server program simply hands control over to a report dispatch program and program control is returned to the server program after the report has been successfully dispatched to the requestor. At this point, the job_completed server state variable is set to true to prevent the server program from generating and/or dispatching the report multiple times. The server state variable get_next_job remains FALSE until successful completion of the remaining tasks in the processing loop.
Task 4: Mark the report complete in the database job queue.
/* Section 4 : Task 4*******************************/
Call database procedure to mark job completed in job queue table
Test for success of database procedure
At this stage, the pending report has been generated and dispatched to the requestor. The server program now must let the report queue management package in the database know that the report has been completed. This is accomplished by calling another stored procedure in the database. The task of notifying the database report queue manager is carried out in a similar manner as discussed in task 1. The database connection object is used to execute PL/SQL passing the job number of the completed report to another stored procedure in the database. This stored procedure then handles the work of updating the report queue table status fields for the job number. If this call to the stored procedure in the database returns control to the server program with a success status, the server program moves on to the next task. Otherwise an error is manually raised and the ERRORHANDLER: section is invoked in an attempt to handle the problem.
Task 5: Clean up the Excel environment and prepare for the next reporting job
/* Section 4 : Task 5*******************************/
Clean up/reset current Excel environment
job_completed = FALSE
get_next_job = TRUE
error_count = 0
The very last task to perform in the endless processing loop is to clean up the current state of Excel and then reset the server state variables get_next_job and job_completed. The Excel clean up includes closing and optionally keeping a copy of the newly created Excel workbook containing the report. The production server at Goodyear saves these workbook files in a known report directory on disk using the job number as a filename. These files are kept for seven days and then deleted by a Perl script that is scheduled to run nightly. Saving these files for a few days is especially helpful when debugging problems with a report generation program that is not working as expected. Once the cleanup is complete, the server state variable get_next_job is set to TRUE and the variable job_completed is set to false. The final step in the endless processing loop is to reset the error_count variable to 0. The purpose of the error_count variable will be discussed in more detail in the next and final section of the pseudocode which covers error handling. The server program is now back in a state that will allow it to continue the process of requesting the next pending report job from the job queue in the endless processing loop.
Report Server Program Pseudocode Section 5
/* Section 5 *******************************/
ERRORHANDLER:
Close Oracle Connection
Select Case ( ERROR NUMBER )
Case Unknown Error, Excel Error, Excel Programming Error
skip_job = TRUE
resume RETRY:
Case Oracle Connection Error
If error_count < MAXERROR Then
error_count = error_count + 1
wait WAIT_TIME
resume RETRY:
Else
Exit Program
End if
End Select
In a perfect world, the report server program would faithfully execute the processing loop of code. Unfortunately, this is not always the case. The database can be brought down for a number of reasons and if the report server program attempts to communicate with the database when it is not available, an OO4O API object generates a Visual Basic run time error. The Excel VBA programs that are used to render and dispatch the reports can error out due to expected and unexpected circumstances or programming errors. The key to dealing with these errors effectively is to classify the hundreds of possible errors that can occur into a few categories and devise a strategy that handles them appropriately. The report server program is designed to handle two distinct classes of errors, Excel side report processing errors and network/database connectivity errors.
1. Excel side report processing errors: Errors that should cause a reporting job to be aborted and marked accordingly in the database report queue. In this instance, the job should be marked and simply discarded in favor of the next pending report in the report queue. The server program then continues the report processing loop. All unknown errors, excluding unknown network/database connectivity errors described below, have also been lumped into this category.
2. Network/database connectivity errors: Errors that indicate there is a loss of connectivity between the report server program and the database. These errors are not always fatal to the report server program. Connectivity errors are trapped and a number of attempts are made to regain the connection. If a connection cannot be established within a predefined number of attempts, the report server program terminates, and all processing of reports is halted.
This strategy is a simple one, but it has proven to be very robust in practice. This error handling scheme assumes that any runtime error generated in any section of the program that uses the database connection is a network/database connectivity error. Applying the strategy to the report server program and the report generation and dispatching programs called from within it is straightforward. One of the keys to handling errors in this manner is to separate all the code that requires active use of the database connection from all the code that handles processing tasks local to Excel. The report generation programs discussed later in this paper are good examples of this error handling strategy at work. Each report generation program is actually broken down into two distinct sub-programs, one to extract the data and report parameters from the database, and another to handle the local Excel report generation. The difficult task in this simple strategy is ensuring that no matter what the network/database connectivity error is and no matter where it occurs in the execution of the code, that the report server program can recover and continue to work on the current report if the database connection becomes available again. This is a tricky task and one of the main reasons why the settings of the server state variables are so crucial to the report server design. Remember that the current values of these state variables are the only way the server program remembers what it was doing when it was interrupted by a database connection outage. Another thing to keep in mind is that the report server program’s current job number value must not be allowed to change until the job is completed or marked as aborted due to error in the database report queue. When an error does occur in the report server program, the ERRORHANLDER: section described in the pseudocode is always executed. The first task that is performed is to effectively disconnect the server program from the database no matter what class of error has occurred. An OO4O API call to do this does not exist, but the equivalent functionality can be achieved by letting go of the database connection object and the Oracle in Process Server object using the following Visual Basic code.
Set OraDatabase = Nothing
Set OraSession = Nothing
The rationale is that if the database connection object is broken, the best and safest way to fix it is by replacing it entirely. This forces the report server program to halt all attempts at processing reports until a connection to the database can be restored. This attempt to reconnect to the database is precisely what will happen after the error handling code returns to the RETRY: label in the program. The next task is to actually determine which of the two classes of errors has occurred and then take appropriate action. If the error is an Excel side processing error, the skip_job server state variable is set to true and the report server program is put in a state that will allow it to enter pseudocode Section 3. Remember that pseudocode Section 3 is responsible for communicating to the report queue manager that a reporting job could not be successfully generated by the report server program due to error. If the error was actually a network/database connectivity error, then the report server program checks the number of consecutive network/database errors stored in the error_count variable and compares that value to the symbolic constant MAXERROR. If the error_count value is less than the value of MAXERROR, then the server program takes a time out, so to speak, and waits for a set period of time before trying to resume at the RETRY: label and establish a fresh connection to the database. The line of Visual Basic code which makes the server program sleep, is as follows:
Application.Wait DateAdd("n", WAIT_TIME, Now)
Where WAIT_TIME is a symbolic constant, and “n” instructs the DateAdd function to add WAIT_TIME minutes to “Now” (the current system date). The values of the symbolic constants, WAIT_TIME and MAXERROR, can be adjusted to vary the amount of time that the report server program persistently attempts to reconnect to the database before the program halts completely.
Additional Report Server Program functionality
There are two optional, yet useful, functions that the actual report server program performs that were intentionally left out of the pseudocode to keep the focus on the essential entities in the design and structure of the program itself. These extra features include logging and e-mailing warnings to those who are in charge of system maintenance. The implementation of logging is a pretty simple task and one that has proven to be the most reliable and helpful tool for diagnosing system problems and verifying the current state of the report server program. This author recommends that every major step of the report server program be logged in a text file as the step occurs. Each line written in the log file should contain, at the minimum, the following items:
· A short pertinent description of the action being performed
· The current job number of the report being processed
· A time and date stamp
· Error messages for all errors that occur
Another helpful feature that was not covered is the ability to e-mail a warning message to system support personnel. The principle here is that support personnel should be automatically notified when the report server cannot complete a request to generate a report due to an Excel side report processing error, and also before shutting down when too many consecutive network/database connectivity errors occur. These e-mail warnings allow a more proactive approach to system maintenance and aid in the monitoring of system operability. The implementation details of e-mailing warning messages are not covered in this paper, but one thing to keep in mind is that adding the capability to e-mail warning messages also creates additional error handling concerns.
Report Generation Programming Issues
The scope of the discussion of the Excel report server program pseudocode did not include any of the details of how a report is actually generated in Excel. In this section of the paper, the focus will be on how the report server program loads and executes Excel VBA programs to generate reports; the technique used to extract data and reporting information from the database; and some tips and techniques that aid programmers in the rapid development of reports.
The Overall Process of Creating A Report
The process of creating a new report, in general, requires the following tasks:
1. Programmatically open a new workbook in Excel as the target for the new report.
2. Execute the sub-program to extract the data from the database and place it in a spreadsheet in the target workbook using the program name to identify the correct data extract Visual Basic module.
3. Load the Excel add-in file that is responsible for rendering the report using the program name to identify the file name of the Excel add-in.
4. Invoke the gen_rep() sub-program in the report-rendering Excel add-in file.
5. Capture the exit status from the report rendering sub-program.
6. Unload the report-rendering Excel add-in file.
Once this chain of tasks is complete, Excel has a brand new fully rendered report in the target Excel workbook.
Moving information from the database to the Excel Environment
There are two distinct types of data stored in working tables on the database:
1. The report parameter list
2. One or more report data sets
In an effort to keep the communication with the database separate from the actual process of rendering a report in Excel, a data extract program is written to download the report’s parameter list and data set and lays this information out in a predefined format on an Excel spreadsheet. This Excel spreadsheet is the only input to the report rendering add-in program. The report parameter list serves two purposes. It supplies information to the report rendering program that is specific to this instance of the report and also provides a way to display the reporting options that were selected by the requestor in the Web Forms Application. Depending on the complexity of the report, there may be one or more data sets that must be extracted and handed to the report generation program. There might be a series of the same type of data sets or there may be a sequence of different types of data sets. The design of the data extract program and the report generation program must be carefully planned to take into account the layout of the data. The data extract programs that are written to do this task are additional modules of code in the VBA project of the report server program. The module name for the data extract program is the same as the program name that the report server program retrieves from the database when it gets the report’s job number. This scheme allows the report server program to invoke the correct data extract program and to also load the correct report rendering Excel add-in file. In general, the data extract programs must be passed a reference to the report server program’s database connection object. The database connection object is used to query the report parameter list and the report data from tables in the database. The transfer of data from the database to the spreadsheet is accomplished through calls to the OO4O API. Here is a typical example of the code required to get the parameter list from the database table report_param into a spreadsheet:
Dim ParamDynaset AS Object
Dim cell_range AS Excel.Range
Set cell_range = ActiveSheet.Range(“A1”)
PARAM_SQL = _
"select param_name,value_tx " & _
"from report_param " & _
"where job_id = " & job_id & _
" order by param_order"
Set ParamDynaset = OraDbase.DbCreateDynaset(PARAM_SQL, 0&)
If ParamDynaset.RecordCount = 0 Then
Err.Raise number:= ORAFAILURE, Description:="No Report Parameters in data extract"
End If
dynaset_to_sheet ParamDynaset, cell_range, 2
The first step is to get a reference to a cell in the target spreadsheet. The next step is to create a Visual Basic string variable that contains the SQL query. An OO4O Dynaset object is then created using the DbCreateDynaset() API call passing in the SQL query. The resulting dynaset object contains the rows of data fetched from the database. The code then checks if the dynaset contains any rows of data. If not, an error is raised. The final step is a call to dynaset_to_sheet, which is a custom procedure that loops through the rows and columns of the dynaset and places the data into the spreadsheet. The complete code for the dynaset_to_sheet() procedure is included in Appendix C. This same code can be applied as needed to extract the data sets that are required and place them on the spreadsheet that serves as input for the report generation program. The layout of the spreadsheet that contains the input for the report generation program is critical. This layout drives the only means of interface between the data extract sub-program and the report generation program. The layout must place the reporting parameters and the data in areas of the spreadsheet that are well known to the report generation program, but must be flexible enough to allow for variations on the physical size of the data as well. Using empty rows or columns in the spreadsheet to de-mark particular sections of the input spreadsheet works very well. A trivial example of how the layout of the parameter list and data sections look in a typical input sheet, created for a report generation program, can be found in Appendix D. Note how the sections of the input sheet are broken out by empty rows in the spreadsheet. This positional layout is easily read by the report generation program.
Loading and Launching Report Generation Programs
In a more traditional programming environment like C, Java, or Visual Basic, a program has the ability to include and call functions and procedures in other source code files and libraries. Visual Basic for Applications in Excel also includes methods of accomplishing this programming task via VBA References and Registered Excel Add-ins, but the standard options available did not meet the needs for the overall report server program design.
· VBA References expose the VBA functionality of one Excel workbook to another.
· Registered Excel Add-ins expose the VBA functionality of the Excel add-in workbook to all open workbooks in Excel.
If VBA references were used to include each report generation Excel add-in file to the report server program’s VBA project, Excel would open every report generation workbook every time the report server program’s workbook is opened. All registered local Excel add-ins are opened when Excel is opened. This was unacceptable in the server program’s design. Each report generation program workbook contains one or more templates and can be as large as one MB in size for more complex reports. Loading 30 large report generation programs and carrying all that extra overhead in the Excel environment appeared rather impractical. One possible solution was to programmatically register the report generation programs as Excel add-ins on an as needed basis, but this method proved cumbersome. This issue was a major stumbling block in the initial design. The work around was to have the report server program manage the loading and unloading of the report generation programs on an as-needed basis and find some way to call the sub-programs in the report generation program without having a hard reference to the workbook. The following lines of Excel VBA code open an Excel workbook, call code in the loaded Excel workbook without having a hard VBA reference, and then close the workbook.
Workbooks.Open “drive:\path\programfilename.xla”
report_error_msg = Application.Run(“programfilename.xla!gen_rep", [ARG1,ARG2,,,,,,,,ARGN,])
Workbooks(“programfilename.xla”).Close
The Run method of the Application object takes a Visual Basic string of the form “path/filename! function” and then a variable list of arguments to pass to the program. This method runs the function and yields its return value. The “programfilename” used by the report server program to load the Excel add-in file and execute the report rendering sub-program is the program name retrieved from the database job queue. Every report rendering Excel add-in file contains a function called gen_rep() that provides the entry point into the report rendering code. An issue that should be noted here is that using the Run method severs the visual basic call stack. This means that an error thrown during the execution of the gen_rep function left unhandled in the gen_rep function’s code cannot be passed back up the call stack to the program that invoked the Run method. Any unhandled errors in the gen_rep() function will cause Excel to pop up a Visual Basic runtime error on the console. Using the Run method in this manner requires that all errors be trapped in the gen_rep() function. The work around for this is to trap all errors in gen_rep() and return the error message back to the report server program as a string. The report server program code that calls the Run method must capture the return value. If it is an empty string, the report server program assumes success. Otherwise, the program assumes failure and raises an error containing the returned error message. The usage of the Application.Run method is definitely a workaround. An opportunity for design improvement exists here, but this solution to the problem has worked very reliably.
Tips for Developing Report Generation Programs
One of the advantages to working in the Excel environment under this design is that the report generation programs are fundamentally Excel workbooks. The code to render the data can be developed in conjunction with template sheets in the report generation program’s workbook that lay out the report. The typical development cycle of a report generation program is the following:
1. Using an example of the data, work with the customer to create the layout of the report in Excel.
2. Define which portions of the report layout fluctuate from run to run.
3. Create a template of the layout in a spreadsheet stored in the report generation program workbook.
4. Write the code to take the input data spreadsheet and insert it into a copy of the template.
5. Write any code that is needed to mitigate the fluctuations defined in Step 2.
6. Combine the templates and the code into one Excel Workbook and save it as an Excel Add-in file (.xla).
The following is a simple, yet good, example of how handy templates can be in the design of a report. Assume the customer wants a report that contains one relatively small data table. It has to include 10 charts each of a different type driven off that data table, and all of the output has to appear on one spreadsheet. Assuming that the shape and the number of rows and columns in the data table are constant, a template sheet could be designed from a sample of the data with all the charts. The sample data then could be removed from the spreadsheet leaving a template sheet, that when filled in with data, automatically regenerates the charts. The development time for the report generation program in this simple case may take as little as an hour or two to code.
Use the Excel Macro Recorder to learn how to write code. In many cases, Excel shows developers how to write the code to manipulate data on spreadsheets and charts. The Visual Basic Macro recorder actually records the steps performed in Excel and translates the actions occurring during the recording into Visual Basic code. The code that results from this recording is usually not the best to use for the final program, but developers can gather enough information from this recording to determine what must be done.
Conclusion
A production instance of the Global Reporting Application system shown in the Introduction (Figure 1) has been in operation at Goodyear for about 12 months. The Excel report server and Oracle report queue package described in this paper have teamed up to process well over 5,000 reports with very minimal support staff intervention.
APPENDIX
Appendix A:
Complete report server program pseudocode listing:
BEGIN
/* Section 1 *******************************/
ON ERROR GOTO errorhandler:
Code to initialize Excel environment
/* set server state variables */
skip_job = FALSE
get_next_job = True
job_completed = False
job_id = 0
/* set error persistence values */
error_count = 0
WAIT_TIME = 10 /* wait time in minutes */
MAX_ERRORS = 20 /* number of consecutive network/database errors to tolerate */
/* Initialize plsql call strings */
get_jobid_call = “PL/SQL statement calling stored procedure”
mark_job_call = “PL/SQL statement calling stored procedure”
mark_error_call = “PL/SQL statement calling stored procedure”
/* Section 2 *******************************/
RETRY:
OO4O Visual Basic Code to Establish database connection
OO4O Visual Basic Code to create PL/SQL input-output paramater variables
/* Section 3 *******************************/
If skip_job Then
Call database procedure to mark job skipped in job queue table
Test for successful run of database procedure
skip_job = FALSE
get_next_job = True
job_completed = False
error_count = 0
End if
/* Section 4 *******************************/
Do While TRUE
If get_next_job Then
Call database procedure to request a new job
Test for success of database procedure
get_next_job = FALSE
job_completed = FALSE
End if
If NOT job_completed Then
Call Excel VBA code to process the report
Call Excel VBA code to dispatch the report
job_completed = TRUE
End if
Call database procedure to mark job completed in job queue table
Test for success of database procedure
Clean up/reset current Excel environment
job_completed = FALSE
get_next_job = TRUE
error_count = 0
Loop
END
/* Section 5 *******************************/
ERRORHANDLER:
Close Oracle Connection
Select Case ( ERROR NUMBER )
Case Unknown Error, Excel Error, Excel Programming Error
skip_job = TRUE
resume RETRY:
Case Oracle Connection Error
If error_count < MAXERROR Then
error_count = error_count + 1
wait WAIT_TIME
resume RETRY:
Else
Exit Program
End if
End Select
Appendix B:
Example Visual Basic assignment of a PL/SQL block to a string variable:
get_jobid_call = _
"DECLARE" & vbLf & _
"ret BOOLEAN;" & vbLf & _
"BEGIN" & vbLf & _
"ret := job_q_package.get_excel_job( :JOBNO, :PGMNA, :ERRMSG );" & vbLf & _
"If ret Then" & vbLf & _
":SUCCESS := 1;" & vbLf & _
"Else" & vbLf & _
":SUCCESS := 0;" & vbLf & _
"END IF;" & vbLf & _
"RETURN;" & vbLf & _
"EXCEPTION" & vbLf & _
"WHEN OTHERS THEN" & vbLf & _
":ERRMSG := SQLERRM;" & vbLf & _
":SUCCESS := 0;" & vbLf & _
"END;" & vbLf
Appendix C:
Dynaset to Spreadsheet Sub-Program:
Sub dynaset_to_sheet(OraDset As Object, r As Range, numfields As Integer)
'This procedure copies data from a dynaset object into a spreadsheet
'OraDset is an OO4O Dynaset Object Assumed to contain rows of data
'r is an Excel range that references the upper left hand cell of where to start copying data
'numfields limits it by the number of columns to actually copy into the spread sheet
'it will always get every row.
Dim j, i As Integer
Dim OraCols As Object
Set OraCols = OraDset.FIELDS
j = 1
Do While Not OraDset.EOF
For i = 0 To numfields - 1
If Not IsNull(OraCols(i).Value) Then
r(j, i + 1) = OraCols(i).Value
Else
r(j, i + 1) = ""
End If
Next
j = j + 1
OraDset.dbmovenextn 1
Loop
End Sub
Appendix D:
Example spreadsheet layout for input to a report rendering program:
