by Joshua Oskwarek
The Forum Corporation, an international training and consulting company, came to us with a need for a new reporting system. They were outgrowing the Clipper-based reporting system they had built several years ago--the old system lacked the flexibility to create the reports their clients required. We quickly realized that a traditional banded reporting system, like the one shown in Figure A, wouldn't work. Banded reporting systems depend upon reports that repeat the same types of information, record by record. While a few parts of Forum's reports were like this, most pages required complex graphs accompanied by freeform text, as shown in Figure B. In this article, we'll explain how we fulfilled this client's needs.
Figure A: Traditional banded reports assume that you want to repeat information for each record.
Figure B: Our client required reports that were much more freeform.
System goals
Because of the Forum Corporation's widely varied client needs, we knew we needed a reporting system that was highly flexible. We needed to meet these goals:
In response to this need, we developed a powerful solution using Visual Basic, SQL Server, the VSView ActiveX control, and Visual Component's First Impression ActiveX charting control. We designed the reporting engine to be object-oriented--the user can place freeform graphical objects on a report page by setting properties on the object. We started with the object types listed in Table A.
Table A: Report object types
| Object type | Used for... |
| Text | Fixed text appearing on the report |
| TextData | Placing text on the report from data that's stored in a table |
| Graph | Graphs |
| Line | Placing lines on the report |
| RepeatLine | Creating grids (similar to the Line object) |
|
Tip: Get the controls You can download the latest version of VideoSoft's VSView component at www.videosoft.com. To get the First Impression control, visit Visual Components' Web site at www.videosoft.com. |
We coded these objects into VB class modules and stored the property settings for the report objects in a SQL Server database table. When the report runs, the property settings in the database are married with the VB class modules and each object is "painted" onto a printer object. While we could have used the built-in Visual Basic printer object, we found that the VSView ActiveX control gave us many more features, including print preview.
We encountered a potential difficulty when setting properties at runtime. Using DAO, we opened a recordset with fields containing the name of the object, its property name, and the new setting for the property. The trick was taking that information and setting the object's property with the values stored in the fields. Unfortunately, VB doesn't have an Eval() function--such a function would help greatly in setting the property value, by letting us write code like this:
Eval("Object. " & recordset!Property_Name _
& " = " & recordset!Property_Value)
However, since this code isn't possible, we had to find another way to set the properties. We solved the problem by programming a SetProp method into each class module; Listing A shows the method's code. SetProp accepts two parameters: the property's name and its new value. Inside the class module, a Select Case sets the proper private variable that stores the value for the property. To set the property, the following code calls the SetProp method:
Object.SetProp recordset!Property_Name, _
recordset!Property_Value
Listing A: The SetProp method
Public Sub setprop(property_name As String, _
property_value As String)
Select Case UCase(property_name)
Case "X1"
mvarX1 = property_value
Case "X2"
mvarX2 = property_value
Case "Y1"
mvarY1 = property_value
Case "Y2"
mvarY2 = property_value
Case "LINESTYLE"
mvarlineStyle = property_value
Case "LINEWIDTH"
mvarlinewidth = property_value
Case "LINECOLOR"
mvarlineColor = property_value
Case Else
MsgBox "INVALID PROPERTY NAME FOR LINE CLASS: "
& _
property_name
End Select
End Sub
To print an object on the report page, each object has a printobject method. This method processes all property settings, generates the output, and then sends the output to the VSView printer control. The object's position on the page is controlled by the X1, X2, Y1, and Y2 properties; these properties belong to the class module. In addition, the VSView printer object is stored in another property of the class module: PrinterObject. Here's an example of the lineobject's printobject method:
Public Sub PrintObject()
mvarPrinterObject.PenColor = mvarlineColor
mvarPrinterObject.PenStyle = mvarlineStyle
mvarPrinterObject.PenWidth = mvarlinewidth
mvarPrinterObject.DrawLine mvarX1 * 1440, _
mvarY1 * 1440, mvarX2 * 1440, mvarY2 * 1440
End Sub
We also had to tune our report system's performance. One potentially large slowdown lay in using class modules that query data--that is, text data and graph objects. Since these types of objects rely upon data, data retrieval needed to be fast. One way to increase data-retrieval speeds was to keep our queries as simple as possible. This meant eliminating all joins, and querying only one table.
To do so, we created one Report_Data table to store pre-processed data ready to be displayed on the reports. Table B lists the Report_Data table's basic fields.
Table B: Report_data table fields
| Field | Data |
| Report_Data_ID | Identifies the set of result data |
| Report_ID | Identifies which report this data is for |
| Report_Page | Identifies the page number on which the data resides |
| Report_Object_Name | A unique object name that matches the data with an object on the page |
| Number_Result | The actual data for the object, if it's a numeric type |
| String_Result | The actual data for the object, if it's a string type |
With this one-table result scheme, a data pre-process was necessary. We did this using generic VB DLL functions that perform calculations upon the raw data and append records to the Report_Data table. A SQL Server table stores the DLLs necessary for each report.
We implemented another performance enhancement that greatly increased the report's speed. Each report is potentially made up of hundreds of objects throughout its pages. If each object queries the Report_Data SQL Server table, this could mean hundreds of separate remote calls to SQL Server--a very slow process.
To eliminate this problem, we cached the data locally. We created a Microsoft Access table whose structure was identical to the Report_Data table. When a new report runs, we extract all the records from the SQL Server Report_Data table that will be needed for the report, and append them to the local Access table. From that point on, all objects that require data query the local Access table instead of SQL Server. This is much faster and efficient.
Satisfying the client's needs
Let's review how the different aspects of our report system satisfy the client needs that we outlined earlier.
Supporting an almost unlimited number of graphing objects
We accomplished this goal by using class modules to define how graphical objects behave. Class objects can be programmed quickly to perform almost any task. We also found that Visual Components' First Impression ActiveX control gave us the most flexibility in creating custom graphs.
Perform complex calculations upon raw data
We also accomplished this goal by using class modules compiled into COM objects running on Microsoft Transaction Server. These components are flexible; and, once each object has been proven to perform as designed, the object can be re-used and trusted.
Making system objects as reusable as possible
To accomplish this goal, we used class modules compiled into DLLs. This approach makes re-use of objects the mainstay of the application.
Creating new reports as quickly as possible
Once report modules are created, the user can design new reports quickly by adding calls to existing class modules. Since class-module property settings are stored in SQL Server tables, a simple database application can allow new reports to be created easily.
Making the report design as simple as possible
By using the object-oriented approach, any complicated logic created for one report can be easily duplicated for others. New reports can re-use logic that has been previously written. This minimizes the amount of coding required by a programmer and allows non-programmers to construct reports using pre-existing objects.
Ensuring that the system is fast
By using the optimization techniques we've described, we achieved a very fast report engine. In addition, we made it possible for reports to be created via an Internet connection.
Importing data from the existing Clipper system
To meet this goal, we chose to use Intersolve's Clipper ODBC driver (www.intersolve.com). By doing so, we were able to implement a reliable pipeline for importing Clipper data into VB.
Conclusion
The Forum Corporation needed a reporting system that could give them a wide range of output based upon their data. Since these reports go directly to clients, they needed to be professional looking and very flexible in order to conform to their client's needs; users also needed the ability to create reports quickly. Our object-oriented approach saved costs by not requiring custom programming for every new report created, thus giving Forum a competitive edge.
Copyright © 1999, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.