In an earlier post I looked at the way in which it is possible to open up a Crystal Report from VB or VBA code in The Raiser’s Edge. That was with version 8.5 of Crystal Reports. I tried to do the same thing recently with a report created in Crystal Reports 11 and the code broke. I rewrote the code using a newer object and technique.
I wrote the code as a VBA macro and that was then placed on the user’s home page as a link. Sometimes it is not possible to create a report using export and custom reports. Export does not allow all permutations of data to be exported so it may be neccessary to query the database directly using a Crystal Report.
Public Const ODBCSOURCE As String = "RE7_1" Public Const REPORT_LOC As String = "C:\Reports\Report.rpt" Public Const DB As String = "RE7_1" Public Sub GenerateLocalRepReport() Dim crystalApp As New CRAXDRT.Application Dim crxTable As CRAXDRT.DatabaseTable Dim crystalReport As CRAXDRT.Report Set crystalReport = crystalApp.OpenReport(REPORT_LOC) crystalReport.DiscardSavedData ' Set the DSN connection for every table in your database On Error GoTo BadCrystalConnection For Each crxTable In crystalReport.Database.Tables crxTable.ConnectionProperties.DeleteAll crxTable.ConnectionProperties.Add "DSN", ODBCSOURCE crxTable.ConnectionProperties.Add "Database", DB crxTable.ConnectionProperties.Add "User ID", "MySQLUserId" crxTable.ConnectionProperties.Add "Password", "PasswordForMySQLUserId" 'Unsure why this is required but without it there is an error message saying that tables cannot be found crxTable.Location = crxTable.Location Next crxTable On Error GoTo 0 crystalReport.Export Exit Sub BadCrystalConnection: MsgBox "The DSN connection string for Crystal Reports is invalid" End Sub
This code will not show the Crystal Report but it does allow you to export it to pdf or any other format. Also if you have any sub reports in the report then this will not work. I’ll post a follow up soon showing how to do that…