Status: Verified
GOAL:
How to use a DSN-less connection with the Crystal Reports ActiveX control in 4GL?
GOAL:
Is it possible to specify a connection string rather than a ODBC DSN to connect the Crystal Reports ActiveX control to a Progress database?
FACT(s) (Environment):
Progress 8.3E
Progress 9.1D
Progress 9.1E
OpenEdge 10.0x
OpenEdge 10.1A
Crystal Reports 9
Crystal Reports 10
Crystal Reports 11 (XI)
Windows
FIX:
One drawback of calling a Crystal report programmatically is that the name of the ODBC DSN must be known in advance. By using a DSN-less connection or Connection String, an ODBC DSN is no longer even necessary. The only requirement is having a Progress SQL-92 ODBC driver installed on the PC.
1) Start AppBuilder and create a new window
2) Click the OCX button in the AppBuilder Palette and add the appropriate viewer control depending upon the version of Crystal Reports that is being used:
Crystal Report Viewer Control 9 - Crystal Reports 9
Crystal ActiveX Report Viewer Control 10.0 - Crystal Reports 10
Crystal ActiveX Report Viewer Control 11.0 - Crystal Reports 11
3) Add a button, copy the following code into the CHOOSE trigger of the button; the sections of the code that need to be replaced have been highlighted.
Please be aware that the default name of the control changes between Crystal Reports 9 and Crystal Reports 10/11.
For Crystal Reports 9, replace <Control Name> with CRViewer9
For Crystal Reports 10 or Crystal Reports 11, replace <Control Name> with CrystalActiveXReportViewer
/* Sample Code - ConnectionProperties - Crystal Reports Viewer Control */
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE chApplication AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chReport AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chConnProps AS COM-HANDLE NO-UNDO.
CREATE 'CrystalRuntime.Application' chApplication.
CREATE 'CrystalRuntime.Report' chReport.
/* Insert full path to the Crystal report */
chReport = chApplication:OpenReport("<FullPathToReport>").
/* Set the Connection Info to Connection Properties for each table object in the report */
DO i = 1 TO chReport:Database:Tables:Count:
chConnProps = chReport:Database:Tables(i):ConnectionProperties.
/* Clear the ConnectionProperties collection */
chConnProps:DeleteAll.
/* Add the Connection String for the report - no other connection parameters are required; sample connection strings are included below */
chConnProps:ADD("Connection String", "DRIVER=<ODBC Driver Name>;HOST=<Hostname>;PORT=<Port>;DB=<Database Name>;UID=<Username>;PWD=<Password>;DIL=<DEFAULT ISOLATION LEVEL>").
/* Use either of the following statements to override the Catalog name if the report has been created using OpenEdge 10.1B or later - see P122620 for more information*/
/* chReport:Database:Tables(i):Location = "<Database Name>". */
/* chReport:Database:Tables(i):SetTableLocation("<Database Name>","",""). */
RELEASE OBJECT chConnProps .
chConnProps = ?.
END.
/* Set the report source */
chCtrlFrame:<Control Name>:ReportSource = chReport.
/* View the report */
chCtrlFrame:<Control Name>:ViewReport().
RELEASE OBJECT chReport.
RELEASE OBJECT chApplication.
ASSIGN chReport = ?
&n.bsp; chApplication = ?..