cancel
Showing results for 
Search instead for 
Did you mean: 

Poor performance exporting Crystal Reports from VBA ???

Former Member
0 Kudos
1,183

Hi all,

When I export reports from within Crystal Design to Excel the time it takes to complete the export if fine.

But when I export the same reports from within VBA (Ms-Access 2003) the time it takes to export one report can take up to a few hours. Here's the function I wrote for this.....

*Note that this function executes from an Ms-Access module.

Can someone have a look at the code below and tell me if I'm missing anything to improve performance or is this the nature of exporting through VBA ?

__________________

Public Function RunCrystalReport(strReportPath As String, intFormatType As Integer, strReportFileName As String) as boolean

On Error GoTo ErrorHdl

Dim appCR As CRAXDDRT.Application

Dim cryReport As CRAXDDRT.Report

RunCrystalReport = False

Set appCR = New CRAXDDRT.Application

Set cryReport = appCR.OpenReport(strReportPath)

cryReport.Database.LogOnServer "oracle.dll", "pdor43", "pdor43", "admin", "password"

cryReport.ExportOptions.DiskFileName = strReportFileName

cryReport.ExportOptions.DestinationType = crEDTDiskFile

cryReport.ExportOptions.FormatType = intFormatType

cryReport.ExportOptions.ExcelMaintainRelativeObjectPosition = True

cryReport.ExportOptions.ExcelMaintainColumnAlignment = True

cryReport.ExportOptions.ExcelAreaType = crPageHeader

cryReport.ExportOptions.ExcelExportAllPages = True

cryReport.ExportOptions.ExcelUseConstantColumnWidth = False

cryReport.ReadRecords

cryReport.Export False

Set cryReport = Nothing

Set appCR = Nothing

RunCrystalReport = True

Exit Function

ErrorHdl:

MsgBox Err.Description & " in RunCrystalReport()", vbCritical

End Function

______________

thanks,

Richard

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Richard,

You didn't mention which version of Crystal Reports you're using. If you're using CR9 or newer you'll want to adjust your database logon code. The code you have was deprecated with the release of CR9. The code you're using will work with CR8, CR8.5, and CR9 (backward compatibility), however the code will start getting flaky with CR10, CR XI, and CR XI R2.

There's a new connection properties method that is better suited for database connectivity. Clarify the version of Crystal Reports that you're using and I'll see if I can find the "Connection Properties" document link.

It also looks like you're referencing the wrong part of the RDC in your code. You really should be using the CRAXDRT reference ( one "D"). The CRAXDRT and CRAXDDRT are similar objects, but they're not the same. The "DDRT" is used for embedded reports and report creation properties and methods. The "DRT" is really the reference you want to use. You should just be able to change the reference and then update the code.

Again, if you clarify the version of Crystal Reports that you're using and I'll see if I can find the "Connection Properties" document link.

Former Member
0 Kudos

Hi Daniel,

I'm using CR version 11.0.0.2495

Your assistance is much appreciated !!!

Rick

Former Member
0 Kudos

Hi Richard,

Here are links to some documents that should help.

[RDC Connection Properties|https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/00635998-751e-2b10-9cba-f50ee1e4ef81]

This document was written for CR9, however it's still valid for CR10, CR XI (v11.0), and CR XI R2 (v11.5)

[Connectivity changes from Crystal Reports 8.5 to XI|https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/0003c6cc-891e-2b10-678e-8b1a2dd96556]

To search for other Crystal Reports related documents you can visit the [Business Objects Article|https://www.sdn.sap.com/irj/sdn/businessobjects-articles] link on the SAP SDN site.

You'll also want to make sure you've applied the latest service packs and deploy with the latest merge modules.

In case you're not aware, please note that CR XI (v11.0) has reached end of life for patch support. Additionally, the RDC (Report Designer Component) has been retired with the release of CR 2008 (v12.0). The upgrade path will be to move to the Crystal Reports .NET SDK.

I hope this helps!

Daniel Kelleher

Former Member
0 Kudos

Hi Daniel,

I've changed the references to CRAXDRT like you mentionned, and I changed the reference from oracle.dll to crdb_oracle.dll. The code ran OK but the performance did not improve(very slow to export).

I went through the documentation you pointed me too and tried to connect using the syntax in the docs. Here's a sample of some code I wrote (based on the document ReportDesignerComponent9.pdf page 10), the code runs as far as line 4 then an "Automation Error, Invalid Pointer" occurs.

1. Dim ConnectionInfo As CRAXDRT.ConnectionProperties

2. Dim crReport As CRAXDRT.Report

3. Set crReport = New CRAXDRT.Report

4. Set ConnectionInfo = crReport.Database.Tables(1).ConnectionProperties

(Note the document does not declare the object crReport so I just declared it myself)

Do you know why I would get an invalid pointer ?

thanks for your assistance.....

Richard

ted_ueda
Employee
Employee
0 Kudos

Did you open the report?


Dim crApp As New CRAXDRT.Application
Dim crReport As CRAXDRT.Report
Set crReport = crApp.OpenReport("C:\path\to\rpt\file")
' export here

Sincerely,

Ted Ueda

Former Member
0 Kudos

Hello folks,

Well I've got the connection working right )), here's the code; although my issue is still with the export (in the code below) being much slower than using the Crystal design environment to export.

Do you guys have an idea why it would be much slower exporting using the code ? also I can't locate any documents explaining the ExportOptions, could you point me to one.

Thanks for your help !

Richard

Dim appCR As CRAXDRT.Application

Dim cryReport As CRAXDRT.Report

Dim ConnectionInfo As CRAXDRT.ConnectionProperties

Set appCR = New CRAXDRT.Application

Set cryReport = appCR.OpenReport(strReportPath)

'---: Set connection properties

Set ConnectionInfo = cryReport.Database.Tables(1).ConnectionProperties

cryReport.Database.Tables(1).DllName = "crdb_oracle.dll"

ConnectionInfo.DeleteAll

ConnectionInfo.Add "DSN", "ODBC DSN"

ConnectionInfo.Add "User ID", "UserID"

ConnectionInfo.Add "password", "Password"

ConnectionInfo.Add "Server", "Server"

'---: Read data

cryReport.ReadRecords

'---: Set export options

cryReport.ExportOptions.DiskFileName = "d:\xyz.xls"

cryReport.ExportOptions.DestinationType = crEDTDiskFile

cryReport.ExportOptions.FormatType = 36 '---: Export to Excel data only.

cryReport.ExportOptions.ExcelMaintainRelativeObjectPosition = True

cryReport.ExportOptions.ExcelMaintainColumnAlignment = True

cryReport.ExportOptions.ExcelAreaType = crPageHeader

cryReport.ExportOptions.ExcelExportAllPages = True

cryReport.ExportOptions.ExcelUseConstantColumnWidth = False

'---: Export data

cryReport.Export False

Edited by: Richard Mourre on Sep 3, 2008 8:50 PM

former_member183750
Active Contributor
0 Kudos

Richard, when you say much slower, what is the actual difference? Also, do you see the performance improve on second export - without recreating the report app object?

Ludek

ted_ueda
Employee
Employee
0 Kudos

How long does the ReadRecords method take to complete?

How long does it take for the report to refresh in Crystal Reports Designer?

I'm wondering where the performance bottleneck is in your code.

Sincerely,

Ted Ueda

Former Member
0 Kudos

I beleive I've found the problem....

I started by noting the time it took between the ReadRecords method and refreshing the report in Crystal Design, (I did this for a few reports). The conclusion of this excercise showed that the performance was about the same.

Second, I noted times it took to export using the export method with crEFTExcel97 and crEFTExcelDataOnly export formats. I noticed that exporting to excel data only took under 1 minute versus exporting to excel 97(with formatting) could take up to a few hours (seriously). so I conclude that the export to excel 97 format was causing the bottleneck.

Also, I noticed that exporting from Crystal design using the format option "Microsoft Excel 97 - 2000 (xls)" is much faster than using the ExportMethod with the crEFTExcel97 format type. Even though they both output with the same formatting... I'm not sure why that is ?!? I'll have to test some more with different export options.

note:

In the document "Exporting changes in Crystal Reports 8.5 to XI" on page 3 it recommends using the excel data only format type for exporting.

Also I beleive the links in the "Best practices formatting for exporting to Excel" section are broken.

thanks for all your help and ideas !!!

regards,

Richard

Answers (1)

Answers (1)

0 Kudos

"The code you have was deprecated with the release of CR9. The code you're using will work with CR8, CR8.5, and CR9 (backward compatibility), however the code will start getting flaky with CR10, CR XI, and CR XI R2."

this explains the problem I am facing. I am using Crystal Report 2016. I don't have Crystal Reports ActiveX Designer Run Time Library and I downloaded wrong version it seems.

Anyone know where can I get new version? or solution.