|
How Do I Set the Location of database tables of a subreport in crystal reports with VB?
How Do I use SetLogOnInfo with sub reports in crystal reports with VB?
How Do I pass LogOn Information/credentials like database name,user name, password and DSN to a subreport in a Crystal Report from VB?
How Do I loop through all the subreports in a crystal report programatically using Visual Basic?
The article shows you how to loop through each subreport in a main crystal report and each Database Table in
the Database Tables collection of a subreport and then set the location of the database file for each table.
Also shows you how to pass DSN name and login info
If you are using a Native connection to SQL database or an ODBC connection to a PC or SQL database.
The article demonstrates using CRAXDRT object Library to achieve this.
Crystal Reports software comes with Crystal Reports ActiveX Designer Run Time Library which provides
the necessory objects to do this. Though I've used version 9.0 objects in the article,
it should work with version 8/8.5/9/10/XI without any trouble, I think.
So, before getting started, you need to reference this object library to your VB Project.
First, let's declare the Object variables to hold the Crystal Application, Report, Sub-report,
sections, section, Database, Tables and Table objects
Dim CrxApp As CRAXDRT.Application
Dim CrxRep As CRAXDRT.Report
Dim crxSubreport As CRAXDRT.Report
Dim crxSubreportObject As SubreportObject
Dim crxDatabase As CRAXDRT.Database
Dim crxDatabaseTables As CRAXDRT.DatabaseTables
Dim crxDatabaseTable As CRAXDRT.DatabaseTable
Dim crxSections As CRAXDRT.Sections
Dim crxSection As CRAXDRT.Section
Dim CRXReportObject As Object
Instantiate the Application object and open the Crystal Report from the disk
Set CrxApp = New CRAXDRT.Application
Set CrxRep = CrxApp.OpenReport("H:\test\crtests\Report1.rpt")
Now, set our crxSections object to the Report object's sections collection
Set crxSections = CrxRep.Sections
Now we loop through all sections in the main report, then loop through all objects in each section,
check to see if the object is a subreport and if it's a subreport, then open it, loop through the
DatabaseTables collection and set the location of the database file for each table in it. Sounds complicated, but
it's not really is, as you see later.
If the source is a Native connection or ODBC connection to a PC or SQL database, then we use the SetLogOnInfo method to set the logon credentials.
SetLogOnInfo takes 4 parameters.
First one is the DSN name for ODBC connections and Servername for Native connection to SQL database,
Second is the database name (like northwind),
third is username and the last one is the password.
For Each crxSection In crxSections
For Each CRXReportObject In crxSection.ReportObjects
If CRXReportObject.Kind = crSubreportObject Then
Set crxSubreportObject = CRXReportObject
Set crxSubreport = crxSubreportObject.OpenSubreport
Set crxDatabase = crxSubreport.Database
Set crxDatabaseTables = crxDatabase.Tables
For Each crxDatabaseTable In crxDatabaseTables
'If you are using a Native connection to PC database
crxDatabaseTable.Location = App.Path & "\xtremelite.mdb"
'Use next line, if you are using Native connection to SQL database
'crxDatabaseTable.SetLogOnInfo "servername", "databasename", "userid", "password"
'Use next line, if you are using ODBC connection to a PC or SQL database
'crxDatabaseTable.SetLogOnInfo "ODBC_DSN", "databasename", "userid", "password"
Next
Set crxSubreport = Nothing
End If
Next
Next
Now, preview the Report
'Make sure you add a Crystal Report Viewer Control to you form
CRViewer91.ReportSource = CrxRep
CRViewer91.ViewReport
Finally, release the references.
Set crxDatabase = Nothing
Set crxDatabaseTable = Nothing
Set crxDatabaseTables = Nothing
Set crxSections = Nothing
Set crxSection = Nothing
Set crxSubreportObject = Nothing
Set crxReport = Nothing
Set crxApplication = Nothing
'Add a command button and a Crystal Report Viewer Control to you form
'Reference Crystal Reports 8/8.5/9/10 ActiveX Designer Run time Library
'Paste the following code into the form code window
'Change the path "C:\crtests\Report1.rpt" to suit yours
Private Sub Command1_Click()
Dim CrxApp As CRAXDRT.Application
Dim CrxRep As CRAXDRT.Report
Dim crxSubreport As CRAXDRT.Report
Dim crxSubreportObject As SubreportObject
Dim crxDatabase As CRAXDRT.Database
Dim crxDatabaseTables As CRAXDRT.DatabaseTables
Dim crxDatabaseTable As CRAXDRT.DatabaseTable
Dim crxSections As CRAXDRT.Sections
Dim crxSection As CRAXDRT.Section
Dim CRXReportObject As Object
Set CrxApp = New CRAXDRT.Application
Set CrxRep = CrxApp.OpenReport("H:\test\crtests\Report1.rpt")
Set crxSections = CrxRep.Sections
For Each crxSection In crxSections
For Each CRXReportObject In crxSection.ReportObjects
If CRXReportObject.Kind = crSubreportObject Then
Set crxSubreportObject = CRXReportObject
Set crxSubreport = crxSubreportObject.OpenSubreport
Set crxDatabase = crxSubreport.Database
Set crxDatabaseTables = crxDatabase.Tables
For Each crxDatabaseTable In crxDatabaseTables
'If you are using a Native connection to PC database
crxDatabaseTable.Location = App.Path & "\xtremelite.mdb"
'Use next line, if you are using Native connection to SQL database
'crxDatabaseTable.SetLogOnInfo "servername", "databasename", "userid", "password"
'Use next line, if you are using ODBC connection to a PC or SQL database
'crxDatabaseTable.SetLogOnInfo "ODBC_DSN", "databasename", "userid", "password"
Next
Set crxSubreport = Nothing
End If
Next
Next
CRViewer91.ReportSource = CrxRep
CRViewer91.ViewReport
Set crxDatabase = Nothing
Set crxDatabaseTable = Nothing
Set crxDatabaseTables = Nothing
Set crxSections = Nothing
Set crxSection = Nothing
Set crxSubreportObject = Nothing
Set crxReport = Nothing
Set crxApplication = Nothing
End Sub
|
