How to launch a Crystal 11 report from VB6

[Henry Schubel]

There will be two posts minimum...
First, I'll refer you to the Knowledgebase Articles that give the needed information, and second I'll give you code samples to run a report given the methods I use. Specifically, I access a SQL or Access database via ODBC, but either database will work with the code samples.

This basic document is a good place to start.
http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do?cmd=displayKC&docType=kc&externalId=http--supportbusinessobjectscom-communityCS-TechnicalPapersNoNav-cr9ocxtordcpdfasp&sliceId=&dialogID=14150208&stateId=1%200%2014152193

The biggest changes were the new ConnectionProperties in version 9.0 and later.
They are explained in the following document:
http://support.businessobjects.com/communityCS/TechnicalPapers/cr_query_engine.pdf

The RDC code is described in:
http://support.businessobjects.com/communityCS/TechnicalPapers/cr_rdc9_connectionproperties.pdf.asp

If you will be running reports from older versions of crystal, I recommend using the property .DeleteAll and add the new properties in for ODBC. Each connection does have different properties as it now reads the driver; ODBC, OLEDB or Native.

The information is there, but I did not find these articles particularly user friendly.

[Henry Schubel]

Second, I access a SQL Server database via ODBC. Since I can't predict or dictate what the SQL Server (that's the SQL Server, not the Windows Server) will be, the name is passed to the program as a parameter in the desktop shortcut.

Preliminary:
I use a separate UserID and password for all my crystal reports, and I set up a DSN with ODBC Administrator to define it. I call it something cryptic, but the gist is I usually call the DSN 'CRSS' which stands for Crystal Reports SQL Server, and I don't use that ID for anything except crystal, and in general, the User ID has READONLY access (Select access) only, to all tables in the database. It's readonly, so you can't hurt anything, and unless you're really paranoid about security/privacy it won't be a crisis if someone gets a hold of that User ID and password combination. Of course, I don't tell anyone either. It is used strictly inside my programs to facilitate crystal. I use the DSN 'CRSS' because if you have to move 100 reports to another machine, like one would have to do when installing a commercial product, all you have to do is define that DSN on the target machine and all your reports will work. Therefore, this is actually one place in life where being cryptic is a good idea.

For VB6

Three Project References:
Crystal ActiveX Report Viewer Library 11.0
Crystal Reports ActiveX Designer Run Time Library 11.0
Crystal Data Object Library 2.7
All abover references should be pointing to files in the BusinessObjects folder somewhere.

Now, you need a form to display a report. I called this form the Crystal Viewer, and I took the controls from the sample code that comes with the product. You can easily tailor the options on the form to meet your own needs, or use the sample form.

On the Menu Bar in my programs, I have a Menu Item called Report Library. It is defined as an array, and I build the list of available reports dynamically when the program loads based on a 2-character ID that identifies that particular program. No going back and forth updating the code just to show a new report you've written ! The user selects which report they want, and I query my database to find the menu name and get the fully qualified path to access the report. You use that full path name to set the string variable CrystalTargetFile per the code below.

Set this up in a separate VB6 module.

Option Explicit
Public ReportLibDirPrefix As String
Public CrystalTargetFile As String
Public CrystalSelectionFormula As String
Public CrystalApp As New CRAXDRT.Application
Public crxTable As CRAXDRT.DatabaseTable
Public CrystalReport As CRAXDRT.Report

This code goes in the the form CrystalActiveXReportViewer or where ever you determine the name of the report file you want to run. In my case, this is in a public Module. This is a sample ! Notice code commented out which is generally the old CRv7 code. In my case, I went from Crystal Reports V7 to V11.

' Formname.CrystalReport.SelectionFormula = " " ' Crystal Reports v7 looked like this
' Formname.CrystalReport.ReportFileName = TargetFilePath & Trim$("FileName")

' remember to blank out the formula in CR11 too if you're not using it!
CrystalSelectionFormula = ""
' set the fully qualified path to the report file
CrystalTargetFile = ReportLibDirPrefix & Trim$("FileName")

On Error GoTo ReportNotFound
Set CrystalReport = CrystalApp.OpenReport(CrystalTargetFile)
CrystalReport.DiscardSavedData

' DO NOT PASS the Record Selection Formula unless you're really passing a selection
' CrystalReport.RecordSelectionFormula = CrystalSelectionFormula
' CrystalReport.RecordSelectionFormula = "{VendorMaster.VendorNo} = '709948'"
' this selection formula worked fine too. Notice this code is commented out !

Then to actually enable the report...

' 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", "CRSS"
crxTable.ConnectionProperties.Add "Database", "YourDatabaseName" 'Database Name
crxTable.ConnectionProperties.Add "User ID", "YourDatabaseID" 'UserID
crxTable.ConnectionProperties.Add "Password", "YourPassword" 'Pswd
Next crxTable
On Error GoTo 0

' Formname.CrystalReport.Action = 1 ' CRv7
' The following tests items on the programs Menu Bar to see where
' the user wanted the output.
If Formname.mnuPrintToWindow.Checked = True Then CrystalViewer.Show 1
If Formname.mnuPrintToPrinter.Checked = True Then CrystalReport.PrintOut False
Exit Sub

ReportNotFound:
MsgBox "Unable to execute this report" & Chr(13) & Chr(13) & CrystalTargetFile, vbExclamation, "Report File Missing"
Exit Sub

BadCrystalConnection:
MsgBox "The DSN connection string for Crystal Reports is invalid"

ALL OF THE ABOVE IS IN THAT PUBLIC MODULE YOU MADE.

IN THE VIEW FORM....ALL YOU NEED IS....

CrystalReportViewer.EnableExportButton = True
CrystalReportViewer.EnableZoomControl = True
' CrystalReportViewer.Zoom = 75 ' This worked fine
' CrystalReportViewer.EnableGroupTree = True ' worked fine
CrystalReportViewer.ReportSource = CrystalReport
CrystalReportViewer.ViewReport
' That last line should make your report execute.

Good Luck.

[bhanley]

There will be two posts minimum...
First, I'll refer you to the Knowledgebase Articles that give the needed information, and second I'll give you code samples to run a report given the methods I use. Specifically, I access a SQL or Access database via ODBC, but either database will work with the code samples.

This basic document is a good place to start.
http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do?cmd=displayKC&docType=kc&externalId=http--supportbusinessobjectscom-communityCS-TechnicalPapersNoNav-cr9ocxtordcpdfasp&sliceId=&dialogID=14150208&stateId=1%200%2014152193

The biggest changes were the new ConnectionProperties in version 9.0 and later.
They are explained in the following document:
http://support.businessobjects.com/communityCS/TechnicalPapers/cr_query_engine.pdf

The RDC code is described in:
http://support.businessobjects.com/communityCS/TechnicalPapers/cr_rdc9_connectionproperties.pdf.asp

If you will be running reports from older versions of crystal, I recommend using the property .DeleteAll and add the new properties in for ODBC. Each connection does have different properties as it now reads the driver; ODBC, OLEDB or Native.

The information is there, but I did not find these articles particularly user friendly.

The links to the articles have all been broken as of 10/31/2008 when I tried them. They just took me to the home page.

[S. Faisal Imam]

Syed Faisal Imam

Here dsrSOReport = Design Report

Dim objReport As New dsrSOReport

Screen.MousePointer = vbHourglass

Dim myTable As CRAXDRT.DatabaseTable

For Each myTable In objReport.Database.Tables
myTable.ConnectionProperties.DeleteAll
myTable.ConnectionProperties.Add "Provider", "SQLOLEDB.1"
myTable.ConnectionProperties.Add "Data Source", "ServerName"
myTable.ConnectionProperties.Add "Initial Catalog", "DatabaseName"
myTable.ConnectionProperties.Add "user id", "UserName"
myTable.ConnectionProperties.Add "password", "Password"
Next

objReport.DiscardSavedData

objReport.RecordSelectionFormula = "{TableName.FieldName}=" & VariableValue
CRViewer1.ReportSource = objReport
CRViewer1.ViewReport
CRViewer1.Refresh

Screen.MousePointer = vbDefault

Set objReport = Nothing

[asaness]

hi! hello! may i know where did you get that installer of Crystal Report 11? and would you pls. give me some copy of that installer pls. i need it..