Categories
Blog Posts

Running Reports Outside Spire

This article shows you how to output Spire reports without using the Spire application. You will still need Spire installed, but won’t need to log in. The password required will be secured inside a compiled MS Access application.

This solution requires the following to implement:

  • Spire
  • Microsoft Access
  • A copy of Crystal Reports. 2008 onward will be fine.

To deploy, the client needs:

This demonstration will output one or more standard Spire invoices to a PDF document, although it can be customized to output many other types of forms or reports.

The general steps are:

  1. Create an ODBC datasource on your development PC.
  2. Create a new MS Access database.
  3. Add references to Crystal Active-X components.
  4. Paste in the sample code.
  5. Update the configuration settings in the sample code.
  6. Compile the database as an accde application.
  7. Distribute the accde application to a client PC along with the .rpt report file used.
  8. Install MS Access Runtime if the client PC has no MS Access.
  9. Create an ODBC datasource on the client PC.
  10. Execute the program and output the PDF.

The Code

On Error GoTo ReportError

' objects needed for Crystal Reports
Dim objApp As New CRAXDRT.Application
Dim objRpt As CRAXDRT.Report
Dim objParam As CRAXDRT.ParameterFieldDefinition
Dim objSection As CRAXDRT.Section
Dim objObject As Object
Dim strConnectString As String

' objects needed to process subreports
Dim objRptSub As CRAXDRT.Report
Dim objSubRpt As CRAXDRT.SubreportObject
Dim objDBSub As CRAXDRT.Database
Dim objTblsSub As CRAXDRT.DatabaseTables
Dim objTblSub As CRAXDRT.DatabaseTable

' configuration variables.
Dim strSpireDBName As String
Dim strDSN As String
Dim strPostgreSQLUser As String
Dim strPostgreSQLPassword As String
Dim strReportFolder As String
Dim strReportFileName As String

' Configuration Settings
'----------------------------------------------

strDSN = "INSPIRE33"
strPostgreSQLUser = "spireapp"
strPostgreSQLPassword = "nv-Mj,mhJTxZ-0Tk"
strReportFolder = "c:\reports\"
strReportFileName = "Invoice Posted.rpt"
strOutputFolder = "c:\reports\"

' Open Report and modify database/company settings
'--------------------------------------------------------------------------------
Set objRpt = objApp.OpenReport(strReportFolder & strReportFileName, 0)

strConnectString = "DSN=" & strDSN & ";;" & _
                    "User ID=" & strPostgreSQLUser & ";" & _
                    "Password=" & strPostgreSQLPassword & ";" & _
                    "UseDSNProperties=0"

' loop through the tables in the report and update their connection string
For i = 1 To objRpt.Database.Tables.Count
    objRpt.Database.Tables(i).ConnectBufferString = strConnectString
    Debug.Print objRpt.Database.Tables(i).Qualifiers.Count
Next i

' loop through the sections of the report, looking for sub-reports and update their connection strings
For Each objSection In objRpt.Sections
    For Each objObject In objSection.ReportObjects
        If objObject.Kind = crSubreportObject Then
            Set objSubRpt = objObject
            Set objRptSub = objSubRpt.OpenSubreport
            Set objDBSub = objRptSub.Database
            Set objTblsSub = objDBSub.Tables
            
            For Each objTblSub In objTblsSub
                objTblSub.ConnectBufferString = strConnectString
            Next objTblSub
        End If
    Next objObject
Next objSection

' this has the effect of changing the PostgreSQL schema name from "SampleData" to your Spire DB Name.
objRpt.Database.Verify

' This code interrogates the report parameters to determine what to pass
' To run different reports, this section will need to be modifed
' Here it is coded to include two invoices with numbers 0000600009 and 0000600010
' an alternative is to have the user prompted for parameters (see "objRpt.Export True" below)

For Each objParam In objRpt.ParameterFields
    Debug.Print "Name: " & objParam.Name & vbTab & "Range or Type:"; objParam.DiscreteOrRangeKind & vbTab & "Value Type: " & objParam.ValueType
    Select Case objParam.Name
        Case "{?InvoiceNoList}"
            objParam.AddCurrentValue ("0000600009")
            objParam.AddCurrentValue ("0000600010")
        Case "{?Locale}"
            objParam.AddCurrentValue ("en_CA")
    End Select
Next objParam


' Output report.  In this example, a PDF file
'--------------------------------------------------------------
With objRpt.ExportOptions
        .DestinationType = crEDTDiskFile
        .DiskFileName = strOutputFolder & "InvoiceOutput.pdf"
        .FormatType = crEFTPortableDocFormat
End With

' this will export the report
' changing to "objRpt.Export True"  will prompt for any remaining parameters and is an alternative to settings any parameters above.
objRpt.Export False

' This command will save a new copy of the rpt file with the changes made.
' This could potentially make future code MUCH shorter and faster
'objRpt.SaveAs "c:\reports\Invoice Posted " & strDSN & ".rpt", crDefaultFileFormat

Set objRpt = Nothing
Set objApp = Nothing

Exit Sub

ReportError:
MsgBox Err.Number & " " & Err.Description, vbCritical, "Oh dear..."

Notes:

  • Crystal Reports must be installed on the PC that compiles the Access database.
  • If you are deploying this report to multiple PC’s you can use a simple batch file to create the ODBC DSN’s. You will find that information in a previous blog post.
  • The script can be enhanced to email the PDF. That’s covered in another blog post.

Leave a Reply

Your email address will not be published. Required fields are marked *