Crystal Reports

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?

 

 

Introduction

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.

 

Library References

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.

Decalrations

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

 

Full Code


'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

Top

www.mahipalreddy.com
Terms and Conditions of Use
Copyright 2004 - 2006 Mahipal Padigela. All rights reserved.