DOCUMENT #764
===========================================================================
Integrating Crystal Reports into Visual Basic Applications
===========================================================================
PRODUCT: R:BASE VERSION: 3.1 or higher
===========================================================================
CATALOG: Programming Area : Reports
===========================================================================
It's easy to integrate a Crystal report into a Visual Basic application.
And using the SQL Engine you can also access your R:BASE database from
Visual Basic creating a complete Windows application. There are three
different ways to print a Crystal Report from Visual Basic:
Printing a compiled report
Using the Crystal custom control
Using the Crystal print engine
Printing a compiled report
A compiled Crystal report is an .EXE file that runs as an independent
application. You launch a compiled report using the Visual Basic SHELL
command. The SHELL command is used in Visual Basic to execute an external
.EXE file.
In your application, first move to the location of the compiled report and
the associated .RPT and .MKX files. You must be in this directory to print
the report. Put an RBASE.CFG file with the appropriate settings, e.g.
MULTI ON, in this directory also. Then, execute the report using the SHELL
command.
The code in your Visual Basic application might look like this:
ChDrive "c:"
ChDir "c:\crw\reports"
result = Shell("test.exe", 1)
Use a drive list box, directory list box and file list box to let the user
select the report to print. You then have the drive, directory and report
name stored in properties for reference by the SHELL command. Or use the
Visual Basic common dialog box to prompt for the file name and then use
other Visual Basic commands to parse the report name into drive, directory
and report name pieces.
See Appendix B of the Crystal Reports for R:BASE User's Manual for a
listing of the necessary files to distribute a compiled report. A compiled
Crystal report can be distributed with your Visual Basic application.
Using the Crystal custom control
Using the Crystal custom control is an easy way to incorporate a Crystal
report in your Visual Basic application. You can let the users select the
report to print, and modify print conditions at runtime.
The Crystal Reports for R:BASE User's Manual has complete information about
the Visual Basic custom control in Chapter 12. The Visual Basic custom
control, CRYSTAL.VBX, is included with Crystal Reports for R:BASE. You can
use it with Visual Basic 2.0 or 3.0, standard edition or professional
edition. The professional edition of Visual Basic 3.0 comes with Crystal
Reports 1.0 (Beware! this is not the same as Crystal Reports for R:BASE)
and a Crystal custom control. The custom controls are slightly different;
you can use either one. You can't interchange any of the other Crystal
program files. Note that the error "Unable to load report" indicates an
incompatible version of the file CRPE.DLL. Even if you choose not to
install the Crystal Reports from Visual Basic 3.0, the 1.0 version of the
file CRPE.DLL may be installed into your WINDOWS\SYSTEM directory.
Use the Visual Basic common dialog box to select the report to print. Both
the Visual Basic common dialog box and the Crystal custom control are
hidden controls. They are visible at design time but hidden at runtime.
Thus you place command buttons or other objects on your form to activate
them.
For example, place the following commands as the "Click" action on a
command button to select a report:
CMDialog1.Filter = "Crystal Reports (*.rpt)|*.rpt"
CMDialog1.FilterIndex = 1
CMDialog1.DialogTitle = "Reports"
CMDialog1.Action = 1
ReportName = CMDialog1.Filename
The selected report is stored in the FileName property of the common dialog
box. That property is then referenced by the Crystal Reports custom
control.
Report1.ReportFileName = CMDialog1.FileName
Print the report either by setting the Action property to 1 or by using the
PrintReport function. The difference between the two is in how errors are
handled. Using the Action property you want to be sure to include error
handling so that your application will not abort. Use the print method that
best fits your application.
1. Result% = Report1.PrintReport
2. Report1.Action = 1
If Report1.LastErrorNumber <> 0 Then
MsgBox Report1.LastErrorString
End If
You can prompt for SORT and WHERE clauses in your Visual Basic application
but you must structure them exactly as they would be in Crystal Reports.
For example, your WHERE clause must be sent like this:
Report1.SelectionFormula =
{transmaster.transdate} in Date (1993, 01, 31) to Date (1993, 12, 31)
A WHERE clause adds on to any where clause defined in the report. A SORT
clause replaces any sort clause defined in the report. You cannot query the
R:BASE database or the report unless you use the print engine. Refer to
Chapter 12 of the Crystal Reports for R:BASE User's Manual for more
details.
If you have a password protected database you must grant SELECT access to
PUBLIC on SYS_TABLES, SYS_TYPES and the table(s) the report is based on. If
you get the error "SQL Server error" it indicates that passwords kept the
database from being opened.
Use the Crystal Reports Print Engine to print a Crystal report
You have the most flexibility when using the Crystal Reports print engine
to print a report. Using the print engine you can query the selected report
for the database name, then use the R:BASE SQL Engine to query the database
for SORT and WHERE conditions.
Here's sample code that will select a report, then query it to find the
database name and location. A command button brings up the common dialog
box for selecting the report name. If no report is selected, the general
procedure ResetReport is called; if a report is selected, the general
procedure GetInfo is called with the selected report name. The selected
report and database are displayed in list boxes.
Variables are declared in the declarations procedure:
Dim ReportName As String
Dim ReportHandle As Integer
Dim LogOnInfo As PELogOnInfo
Dim TableNames() As String
Dim DBName As String
The click procedure for the command button to select a report:
Sub Command1_Click ()
On Error GoTo ErrHandler
CMDialog1.Filter = "Crystal Reports (*.rpt)|*.rpt"
CMDialog1.FilterIndex = 1
CMDialog1.DialogTitle = "Reports"
CMDialog1.Action = 1
If (Not ReportName = "") Then ResetReport
ReportName = CMDialog1.Filename
GetInfo
ErrHandler:
End Sub
The general procedure GetInfo retrieves the database name and table names
from the selected report. It also opens a print job. The Location and
LogOnInfo structures also need to be sized here.
Sub GetInfo ()
Dim N As Integer
Dim i As Integer
Dim Worked As Integer
Dim Location As PETableLocation
Location.Structsize = 258
LogOnInfo.Structsize = 514
lblReport.Caption = ReportName
ReportHandle = PEOpenPrintJob(ReportName)
N = PEGetNTables(ReportHandle)
If N > 0 Then
If PEGetNthTableLogOnInfo(ReportHandle, 0, LogOnInfo) = False Then
DBName = "ERROR!!!!"
Else
DBName = LogOnInfo.DatabaseName
End If
lblDatabase.Caption = DBName
ReDim TableNames(0 To N - 1)
For i = 0 To (N - 1)
If PEGetNthTableLocation(ReportHandle, i, Location) = False Then
Location.Location = "ERROR!!"
End If
TableNames(i) = Location.Location
List1.AddItem TableNames(i)
Next i
Else
lblDatabase.Caption = "Error Retrieving Table Info"
End If
End Sub
The ResetReport procedure clears out the list boxes and closes the print
job.
Sub ResetReport ()
PEClosePrintJob (ReportHandle)
List1.Clear
lblReport.Caption = ""
lblDatabase.Caption = ""
End Sub
Once the report name is selected, and the database and table names
retrieved, you can print the report, or query the database using the
R:BASE SQL Engine. As with the custom control, you can customize SORT and
WHERE clauses. Using the print engine gives you more capabilities, however.
It lets you retrieve the existing selection formula and change it; you are
not limited to just adding on to it.
See Chapter 10 of the Crystal Reports for R:BASE User's Manual for a
complete description of all the print engine functions. The Crystal Reports
on-line Help has additional code examples, particularly for Visual Basic.
Here's an example of how you can connect to an R:BASE database using the
SQL Engine and the retrieved database name. Be sure to include error
checking in your code.
'allocate the environment
retcode = SQLAllocEnv(phenv&)
retcode = SQLAllocConnect(phenv&, hdbc&)
'database settings
'transaction processing off
scoOption = 1006
scostatus = 0
retcode = SQLSetConnectOption(hdbc&, scoOption, scostatus)
'multi user off
scoOption = 1004
scostatus = 0
retcode = SQLSetConnectOption(hdbc&, scoOption, scostatus)
'connect the database
szUID$ = "PUBLIC"
cbUID% = Len(szUID$)
szAuthStr$ = "PUBLIC"
cbAuthStr% = Len(szAuthStr$)
retcode = SQLConnect(hdbc&, DBName, -3, szUID$, cbUID%, szAuthStr$,
cbAuthStr%)
Because the database name is stored in a variable that has been declared
with a length of 128, you don't compute and send the length of the database
name as you normally would with the SQL Engine. Instead of the length, send
a -3. That tells the SQL Engine the string is terminated by a null (Visual
Basic fills the variable with nulls, not blanks). Note that if your
database name, including path, is exactly 128 characters, this won't work.
There is not room at the end of the variable for the null terminator.
To print the report you need to set the display parameters and then call
the PrintReport function. For example:
prtr% = 0
window% = 1
title$ = "Test Report"
lft% = 100
ttop% = 100
wdth% = 480
hght% = 300
style% = 0
pwindow% = 0
result% = PEPrintReport(ReportName, prtr%, window%, title$, lft%, ttop%,
wdth%, hght%, style%, pwindow%)
For more information about specific Visual Basic commands or controls refer
to your Visual Basic documentation or contact Microsoft Visual Basic
support. Chapters 10, 11 and 12 in the Crystal Reports for R:BASE User's
Manual have information about the print engine, the custom control and
creating new functions using C. The Crystal on-line Help has additional
code examples. Another Visual Basic - SQL Engine example is available on
the Microrim forum on CompuServe and on the Microrim BBS. Look for the
file SQLEAPP.ZIP.
Integrating Visual Basic, Crystal Reports and the SQL Engine you can easily
and quickly write Windows applications for your users. Currently, Crystal
Reports and the SQL Engine have the same database limits as the 16-bit
(286) version of R:BASE 4.5 -- a maximum of 1000 tables and 2000 columns
depending on available memory.
Visual Basic is a trademark of and available from Microsoft Corporation,
One Microsoft Way, Redmond, WA 98052, 206-882-8080