Apostate Café

By joshua


Posted in: programming

Using the Crystal Reports Print Engine API from Visual Basic through ODBC and Jet

Using the Crystal Reports Print Engine <span class="caps">API</span> from Visual Basic through <span class="caps">ODBC</span> and Jet
Note: this information is from the mid-90s and is not really useful any more. But this page still gets a lot of hits, so it stays around. Crystal Reports is not a perfect reporting engine, but for many applications, it is the best tool available. On occasion, it is necessary to launch reports from code, without using the Crystal Reports VBX or OCX. To do this, there is an API exposed by the Crystal Reports print engine DLL (CRPE.DLL or CRPE32.DLL). The supplied documentation on this API is sparse, and getting to work with Visual Basic is sometimes difficult. But it can work! Here are a few tips and tricks I’ve come up with to handle Crystal Reports. To access these functions from Visual Basic, you need to include the files `GLOBAL.BAS` or `GLOBAL32.BAS` which come with Crystal Reports professional. Most of the code listed is 16- and 32-bit compatible. Areas where there are significant differences will be marked. Launching a report from code —————————————— Before you print anything with Crystal Reports, the print engine must be initialized via a call to `PEOpenEngine`. This only needs to be done once during the application. From there, the process to launch a report from code is deceptively simple: you must open the report with `PEOpenPrintJob`, select an output device (preview, printer, or disk file) with one of the `PEOutputToXXXXX` functions, print the report with `PEStartPrintJob`, and finally, close it with `PEClosePrintJob`. This chain of 4 function calls is all that is required for minimal printing functionality in code. Unfortunately, the *minimal* part of the “minimal printing functionality” is very minimal. No error handling, no user-definable selection criteria, nothing. Error handling ——————— Certain portions of the Crystal Reports API make it obvious the print engine was targeted at C programmers. One of these is the method Crystal uses for handing error messages back to the program. If any Crystal API function fails, it return a non-zero value, which can be used to retrieve a reason for failure. The function below shows one way to accomplish this. *Note: iTextHandle is a **Long** for CRPE32, and an **Integer** for the 16-bit print engine.* Public Sub PrintErrorHandler(strMessage As String, Optional iJobNumber As Variant) Dim strErrorText As String Dim iTextHandle As Long Dim iTextLen As Integer Dim iErrorCode As Integer Dim iJob As Integer ‘ ‘ if we didn’t get print job number, check with a zero ‘ to look for print engine errors ‘ If IsMissing(iJobNumber) Then iJob = 0 Else iJob = iJobNumber iErrorCode = PEGetErrorCode(iJob) Call PEGetErrorText(iJob, iTextHandle, iTextLen) strErrorText = String$(iTextLen + 1, ” “) Call PEGetHandleString(iTextHandle, strErrorText, iTextLen) strErrorText = Trim$(strErrorText) strErrorText = Left$(strErrorText, Len(strErrorText) - 1) Call MsgBox(“Error ” & iErrorCode & “: ” & Trim$(strErrorText) & vbCrLf _ & strMessage, vbOKOnly + vbCritical, “Printing Error”) End Sub That’s an awful lot of code to get a single error message. Let’s examine what is happening. The first line of code simply works around the lack of default values in VB4. `PEGetErrorCode` takes a print job number, or 0 if there isn’t a current print job, and returns an error code. To get the text associated with the error, `PEGetErrorText` is used. This allocates a string inside the print engine, and supplies us with a handle to the string and the length of the string. After allocating a buffer of sufficient size with `String$`, the API call `PEGetHandleString` is used to place the string in our buffer. The rest of the code simply cleans it up and displays a message box. Handling Different Report Criteria ————————————————— To change the selection formula of a report being printed, the print engine exposes a function called `PESetSelectionFormula`, which is called immediately after opening the print job. The tricky part is remembering Crystal’s format for selection criteria. This is best learned by modeling a few expressions using the “Select Records Export” on the Crystal Reports *Report* menu, and pressing the “Show Formula” button. They are not quite SQL. For example, to limit by a range of dates, you supply an expression like this: “{foo.create\_date} in Date( 1997, 01, 01 ) to Date( 1997, 01, 31)”. Exporting to disk ————————- If you are exporting the results of the report to disk, on extra step is required. You must get the export options from the user. To do this, allocate a structure of type `PEExportOptions`, and use the API call `PEGetExportOptions`. Example ———- The following function shows the basic method for printing a report, and optionally setting the selection criteria. Note the second parameter, *iMode* is used to say where the output is going: 1, to the printer; 2, to the print-preview window; or 3, to disk. Public Function LaunchReport(ByRef strFilename As String, ByRef iMode As Integer, Optional SelectionCriteria As Variant) As Integer ‘ ‘ Open Crystal Reports print engine ‘ Dim iPrintJob As Integer Dim i As Integer ‘ ‘ Open specified report ‘ iPrintJob = PEOpenPrintJob(App.Path & “" & strFilename) If iPrintJob = 0 Then Call PrintErrorHandler(“Failed to open print job”) Screen.MousePointer = vbDefault Exit Function End If ‘ ‘ Set selection criteria if supplied ‘ If Not IsMissing(SelectionCriteria) Then If 0 = PESetSelectionFormula(iPrintJob, CStr(SelectionCriteria)) Then Call PrintErrorHandler(“Error specifying selection formula.”, iPrintJob) Exit Function End If End If ‘ ‘ Set output location based on iMode parm ‘ Select Case iMode Case 1 If 0 = PEOutputToPrinter(iPrintJob, 1) Then Call PrintErrorHandler(“Could not output to printer.”, iPrintJob) Exit Function End If Case 2 If 0 = PEOutputToWindow(iPrintJob, “Print Preview”, -32768, _ -32768, -32768, -32768, 0, 0) Then Call PrintErrorHandler(“Could not output to window.”, iPrintJob) Exit Function End If Case 3 Dim Options As PEExportOptions Options.StructSize = Len(Options) If 0 = PEGetExportOptions(iPrintJob, Options) Then Call PrintErrorHandler(“Could not get export options.”, iPrintJob) Exit Function End If If 0 = PEExportTo(iPrintJob, Options) Then Call PrintErrorHandler(“Could not export to file.”, iPrintJob) Exit Function End If End Select ‘ ‘ Execute print job ‘ Screen.MousePointer = vbHourglass If 0 = PEStartPrintJob(iPrintJob, 1) Then Call PrintErrorHandler(“Error starting printing of report.”, iPrintJob) Else Call PEClosePrintJob(iPrintJob) End If Screen.MousePointer = vbDefault End Function Going through ODBC ————————— Crystal Reports stores a lot of information in the report itself. However, it is possible to use different ODBC data sources to create and print a report. Using the `PELogOnInfo` structure, you can supply logon parameters that Crystal will use for the current session. This is can be accomplished with variables or hard coded strings. The technique is shown below. Note that a null (ASCII 0) is appended to each string. If any of these strings are not null-terminated, you will most likely get an erroneous *500: Not Enough Memory* error message. The call to `PELogOnInfo` must be made before `PEOpenPrintJob`. Dim LogOnInfo As PELogOnInfo Dim strDSN as string Dim strUID as string Dim strPWD as string ‘ ‘ Set up login parms ‘ strDSN = “MyDatasource” strUID = “joshua” strPWD = “password” ‘ ‘ Establish ODBC connection ‘ LogOnInfo.StructSize = Len(LogOnInfo) LogOnInfo.ServerName = strDSN + Chr$(0) LogOnInfo.DatabaseName = “MySQLDatabase” + Chr$(0) LogOnInfo.UserID = strUID + Chr$(0) LogOnInfo.Password = strPWD + Chr$(0) If PELogOnServer(“PDSODBC.DLL”, LogOnInfo) 1 Then Call PrintErrorHandler(“Failed to log onto database: Could not connect.”) End If Changing sorting order ——————————— User-definable sorting is possible using the Crystal Reports print engine, but it is not a simple feat. Some background record-keeping is required, because, like modifying the selection criteria, you must use exact field names. To make matters more complicated, if you have groups in your report, you must set those before setting your sorting order. Here is an extremely brief example of how to accomplish both, assuming one group defined for a given report. This code is designed to plug into the `LaunchReport` function listed above, after setting the selection criteria, but before setting the output device. ‘ ‘ Set group-by ‘ If 0 = PESetGroupCondition(iPrintJob, 3000, “{foo.create_date}”, _ PE_GC_ANYCHANGE, PE_SF_DESCENDING)) Then Call PrintErrorHandler(“Error specifying group-by field.”, iPrintJob) Exit Function End If ‘ ‘ Set sort field ‘ If 0 = PESetNthSortField(iPrintJob, 0, “{foo.create_time}”, PE_SF_DESCENDING)) Then Call PrintErrorHandler(“Error specifying sort field.”, iPrintJob) Exit Function End If **Credits:***Crystal Reports*is a trademark of Crystal Computer Services Inc., a Seagate Software Company.**