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.

‹ Using arithmetic in place of CASE in SQLupVB format syntax ›