Richard is an independent technical consultant in Vancouver, British Columbia. He can be contacted at [email protected].
More often than not, reports are a necessary evil -- you deal with them not because they are fun or easy, but because you have to. Consequently, reporting systems are often left to the end of the development cycle and coded with a minimum of effort and time. Taking this shortcut can hurt the quality of your reports, and, since the person who signs your checks at the end of the job will probably only look at the reports generated, the quality of your reports reflect the quality of your system. Clearly, it's to your advantage to make reports as good as they can be.
Recognizing that there's more to a report than a simple table or two, specialized tools such as Seagate's Crystal Reports 5.0 facilitate the generation of complex, data-centric documents. Several software-development environments bundle Crystal Reports, including Visual C++, Visual Basic, Symantec Enterprise Developer, Visual Appware, Netscape Live Wire Pro, Oracle Power Objects, and dBase for Windows. While Crystal Reports can be used by end users who simply want to generate reports against a database, the Crystal Report Engine DLL and API let you integrate powerful report facilities into a variety of programs.
Crystal Reports 5.0 can be used within any Windows programming environment. It includes interfaces such as ActiveX (OLE) Control, Visual Basic Customer Control (VBX), MFC Class Library with AppWizard, and Delphi Visual Component Library (VCL). In all, Crystal Reports Professional 5.0 provides 110 ReportEngine DLL calls.
With most Visual Basic programming techniques, there are many ways of accomplishing the same task. This holds true for Crystal Reports. You can use the VBX, OCX, or the DLL to control Crystal Reports. I use the DLL since it offers the most features, greatest flexibility, and highest performance. Also, converting code from the 16-bit DLL to the 32-bit DLL is straightforward since the calls are identical.
Granted, writing your own code is contrary to the concept of component programming. But when using components, there is little you can do to improve performance (or implement features the component writer failed to include). You have to decide what's more important to you -- maximum flexibility and high performance, or simplicity and fast development. Luckily, there are programming techniques that take a middle road.
Using Temporary Tables
Crystal Reports states your entire report as a single SQL statement. This isn't always easy, and in some circumstances, it's impossible. More importantly, the single SQL statement method is not always the fastest or most efficient way to process data for a report.
As a programmer, you have the ability to preprocess data for a report using whatever methods are available to you through Visual Basic. So instead of using a single SQL statement, you can use a group of them -- dynasets, snapshots, or tables (in any combination) -- with as much processing as you need. You then take the results of all that processing and place it into a temporary table. Crystal Reports generates the report from that summary table.
The immediate benefit of the temporary-table technique is that you have control over the processing of the report. If the performance doesn't meet your expectations, for example, you can employ techniques such as looping through indexed tables manually, or avoiding excessive table outer joins. Another benefit of temporary tables is that they are stored on the user's local drive, so Crystal doesn't have to worry about network contention. You handle all the fetching from the network drives and SQL servers; Crystal only has to look at a single table in a local, exclusively locked MDB file, substantially improving performance. Also, by eliminating the need to contact networks and SQL servers, you are able to create reports before such tools are available -- just dummy up a dataset in a local table and build away. Using this temporary table, you could have the entire set of reports for the system finished, tested, and approved before the SQL server is up and any data is ready.
Finally, temporary tables let you simplify what Crystal does with the data. This means you only have to use the most-basic features in Crystal to make the reports you want. There's no need to jump through hoops to crosstab a report -- you simply crosstab in your preprocessing and pass the results into the temporary table. Crystal never needs to know that you're working with a crosstab; you use only the Standard Wizard to create your reports. In other words, Crystal becomes a formatting and printing tool.
A Case Study
To illustrate how to use the temporary-table technique, I'll use a project that involves a central purchasing office and satellite stores as an example. All stores purchase their stock through the central office, with inventory kept at the store. On a routine basis, inventory figures are sent to the central office, along with sales for each product. (The complete Visual Basic project for this application is available electronically; see "Availability," page 3.)
Consolidating this data can be a headache, reporting on it, even more so. For example, assume that the basic inventory data has been processed into a summary for each store (see Figure 1). Because the number of stores can change, trying to explain this to Crystal gets difficult. By preprocessing all the stores into a single consolidated table, Crystal picks up the data from the one local table, formats, and prints it.
An essential part of the design of this code is a unified report-request structure. Regardless of whether you preprocess data for a particular report, the method of getting a report should be uniform. In this example, a single call (RepGen "Inventory Status Report") starts the reporting process, regardless of what techniques are used to generate the report. The RepGen call is really just a large Select Case statement that does preprocessing before sending the request for Crystal to print. Depending on the report, there may or may not be a call to a specific generator for preprocessing data. Some reports are simple and don't require preprocessing. All reports call the DoCrystalReport subroutine (see Example 1 and Listing Two) that actually prints the report.
The preprocessing of this report takes several steps (see Listing One). First, the temporary table is cleared of any data from a previous report generation. Then the loop to collect data is started, which gathers information from several different sources using only tables. Because you're doing your own processing, you can use whatever Visual Basic tools you wish -- tables, dynasets, snapshots -- it doesn't matter. In this example, I even insert data into a field based on the table name of a particular store. This is virtually impossible in Crystal, but the tool doesn't need to know where the data comes from -- it only looks at the resulting set that is stored in the temporary table. Once the accumulation is finished, the tables and databases are closed and the report can be printed.
Of course, to print a report, you have to have an RPT file, and the only way to get one is to use the Crystal Report Writer (CRW). But if you use this temporary-table technique, you don't have to learn much about CRW. The Standard Wizard will lead you through most of the report-creation process -- where to get the data, what order to display fields, what totals there should be, how the data is grouped, and so on.
Printing the Report
Once preprocessing is finished and a dataset is sitting in the temporary table, it is time to get Crystal Reports involved in the process. Printing (or previewing) the report involves a separate call in the report-generation process. The RepGen subroutine calls the DoCrystalReport subroutine. By isolating each function, access to reports is symmetrical, whether or not they have preprocessing components.
There are a variety of methods for printing with Crystal Reports. Besides VBX and OCX, there are several methods in the API alone; however, you may encounter problems using the Crystal DLL. Most of these problems are due to weak documentation (this is probably the main reason more programmers don't use the DLL to print).
In this example, I've used the most-complex method of printing and previewing in the API, because it is the most flexible. (A simpler method uses the PEPrintReport call.) The call sequence in the API for printing a report using a custom link is: OpenEngine, OpenPrintJob, OutputtoPrinter, StartPrintJob, ClosePrintJob, and CloseEngine. For previewing, the only variation on this sequence is to replace OutputtoPrinter with OutputtoWindow.
When the DoCrystalReport routine starts, a message box offers the choice of previewing or printing. Assuming the user hasn't pressed Cancel, the first step in printing is to open the Crystal Report Engine. Several things can go wrong at this point, most notably that the engine does not exist on the system. A trap for errors coming from the call will detect this, since there's no way to proceed if the report engine isn't loaded.
The second step is opening the print job. Again, this is a likely point of failure, particularly if the path and/or name of the report file is incorrect, or the file doesn't exist. Another trap protects against any problems here.
Should the routine make it beyond these calls, it will likely succeed, so I quit checking every single call. The balance of the calls is simple for printing. OutputtoPrinter has the option of specifying more than one copy (this example is set to 1). StartPrintJob has a flag called WaitUntilDone, which is always set to True. Execution of the program stops at StartPrintJob until the job is finished processing and is sent to the printer. All that is left to do after StartPrintJob is to ClosePrintJob and CloseEngine -- unless you're previewing.
Preview Windows
Preview windows are more complicated than straight printing. However, the Crystal help file on the OutputtoWindow call reveals a series of global constants referred to for setting up the Style settings of the preview window. The problem is that the global module provided with Crystal Reports for all API calls and constants doesn't include these constants.
Crystal Reports actually uses the standard Windows constants defined in WINDOWS.H. Unfortunately, Visual Basic doesn't provide them in the CONSTANT.TXT file. They are in the WIN31API .HLP file, but you can't add .HLP files to a Visual Basic project. I've included a list of the constants in Listing Three for reference.
When you print a report, you want the data to get to the printer as fast as possible, then free up your computer. When previewing, this process has certain problems, the most significant being that you'll likely never see the preview window -- as fast as it is drawn, it will disappear. The best way to keep the window until the user is finished with it is to monitor its existence with the GetWindowHandle call in the Windows API. This call returns the handle of the preview window for a particular print job. If the window doesn't exist, it returns 0. To create a delay and wait until the user has finished with the window, a loop runs (calling GetWindowHandle), waiting until there is no handle, signaling that the user has closed the preview window. Included in the loop are a couple of DoEvents to tell Windows that execution outside of the loop can go on. Leaving out the DoEvents call effectively hangs the system. Notice that there are two DoEvents calls in a row in the loop. The reasoning behind this is straightforward: Every time a DoEvents occurs, Windows allows another program to execute until that program offers its own DoEvents, then execution returns to my loop. These jumps only take microseconds, so an awful lot of processing time is going into checking that loop over and over. By adding another DoEvents, the frequency of loop checking is decreased, giving everything else in the Windows environment more processing time.
Once the print job is off to the printer (or the preview window has been closed by the user), all that is left to do is clean up. ClosePrintJob and PECloseEngine complete the task of printing with the Crystal API.
Conclusion
By preprocessing complex data yourself, you overcome the fundamental weakness of Crystal's "one-query" approach to report creation. Beyond that, preprocessing offers the distinct advantage of avoiding spending any more time in a foreign development environment (the Crystal Report Writer) than is absolutely necessary.
Finally, using the API for all report generation in Crystal Reports makes porting between 16- and 32-bit versions easy: The CRPE and CRPE32 APIs are identical. When you're ready to switch to VB4, replace the Crystal 16-bit global code module with the 32-bit global code module and you're done.
For More Information
Seagate Software
1095 West Pender Street, 4th Floor
Vancouver, BC
Canada V6E 2M6
601-681-3435
http://www.seagatesoftware.com
DDJ
Listing One
Sub GenerateInvStatData () Dim Report As Database, Central As Database, Store As Database
Dim InvStat As Table, Products As Table, InvSumm As Table
Dim x As Integer, StoreName As String
</p>
AppendEventLog "Opening databases"
Set Report = OpenDatabase(ReportLoc & "Reports.MDB")
Set Central = OpenDatabase(CentralLoc & "Central.MDB")
</p>
AppendEventLog "Purging report table"
Report.Execute "DELETE * FROM [Inventory Status Report]"
</p>
Set InvStat = Report.OpenTable("Inventory Status Report")
Set Products = Central.OpenTable("Products")
InvStat.Index = "PrimaryKey"
Products.Index = "PrimaryKey"
</p>
For x = 1 To 2
StoreName = "Store" & Trim$(Str$(x))
AppendEventLog "Processing " & StoreName
Set Store = OpenDatabase(StoreLoc & StoreName & ".MDB")
Set InvSumm = Store.OpenTable("Inventory Summary")
InvSumm.MoveFirst
Do While Not InvSumm.EOF
Products.Seek "=", InvSumm("Product ID")
InvStat.AddNew
InvStat("Product") = Products("Product Name")
InvStat("Location") = StoreName
InvStat("Open") = InvSumm("Open")
InvStat("Delivered") = InvSumm("Delivered")
InvStat("Sold") = InvSumm("Sold")
InvStat("On Hand") = InvSumm("On Hand")
InvStat("Inventory Value")=InvSumm("On Hand")*Products("Cost Price")
InvStat("Sales Value") = InvSumm("Sold Value")
InvStat("Retail Value") = InvSumm("Sold") * Products("Retail Price")
InvStat.Update
InvSumm.MoveNext
Loop
InvSumm.Close
Store.Close
Next x
AppendEventLog "Processing complete. Closing files."
Products.Close
InvStat.Close
Central.Close
Report.Close
End Sub
Listing Two
Sub DoCrystalReport (ByVal ReportName As String) Dim result%, jobnum%
Dim PreviewPrint As Integer
</p>
PreviewPrint = MsgBox("Do you want to preview this report? Press 'Yes' to
preview, 'No' to print, 'Cancel' to cancel:", MB_YESNOCANCEL +
MB_ICONQUESTION, "Preview Report?")
If PreviewPrint = IDCANCEL Then
MsgBox "Cancel pressed. Report aborted.", MB_OK + MB_ICONSTOP,
"Cancel Pressed"
AppendEventLog "Report Cancelled"
Exit Sub
End If
AppendEventLog "Open Engine"
result% = PEOpenEngine()
If result% = 0 Then
MsgBox "Crystal Reports Engine failed to open.", MB_OK, "Failed to
Open Print Engine"
Exit Sub
End If
AppendEventLog "Open Print Job"
jobnum% = PEOpenPrintJob(ReportLoc & ReportName & ".RPT")
If jobnum% = 0 Then
MsgBox "Could not start print job, something must be wrong with the
report file.", MB_OK, "Failed to Open Print Job"
Exit Sub
End If
AppendEventLog "Configure Print Job"
If PreviewPrint = IDYES Then
result% = PEOutputtoWindow(jobnum%, "Crystal Report Preview" & Chr$(0),
CW_USEDEFAULT, CW_USEDEFAULT, CW_USEDEFAULT, CW_USEDEFAULT, 0, 0)
Else
result% = PEOutputtoPrinter(jobnum%, 1)
End If
AppendEventLog "Print"
result% = PEStartPrintJob(jobnum%, True)
If PreviewPrint = IDYES Then
result% = 1
Do While result% <> 0
result% = PEGetWindowHandle(jobnum%)
DoEvents
DoEvents
Loop
End If
PEClosePrintJob jobnum%
AppendEventLog "Close Engine"
PECloseEngine End Sub
Listing Three
Global Const WS_MINIMIZE = &H20000000Global Const WS_VISIBLE = &H10000000 Global Const WS_DISABLED = &H8000000 Global Const WS_CLIPSIBLINGS = &H4000000 Global Const WS_CLIPCHILDREN = &H2000000 Global Const WS_MAXIMIZE = &H1000000 Global Const WS_CAPTION = &HC00000 Global Const WS_BORDER = &H800000 Global Const WS_DLGFRAME = &H400000 Global Const WS_VSCROLL = &H200000 Global Const WS_HSCROLL = &H100000 Global Const WS_SYSMENU = &H80000 Global Const WS_THICKFRAME = &H40000 Global Const WS_MINIMIZEBOX = &H20000 Global Const WS_MAXIMIZEBOX = &H10000 Global Const CW_USEDEFAULT = &H8000
