1591598 - How to change the database (Pervasive Database) of a Crystal report at runtime using In-Proc RAS SDK.

How to change the database (Pervasive Database) of a Crystal Report at runtime using a connection string.

• Visual Studio 2010
• Crystal Reports for Visual Studio 2010
• Pervasive 11 Database
• In-Proc RAS SDK

The Crystal Report connects to the Pervasive database using the ODBC connectivity through a connection string.

Here is the VB.NET code using In-Proc RAS SDK to change the Pervasive Database at runtime.

Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports CrystalDecisions.ReportAppServer.DataDefModel
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

Public Class Form1
   
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim boReportDocument As New ReportDocument
        '**EDIT** Change the path and report name to the report you want to change.
        boReportDocument.Load("CrystalReport1.rpt", OpenReportMethod.OpenReportByTempCopy)

        'Create a new Database Table to replace the reports current table.
        Dim boTable As New CrystalDecisions.ReportAppServer.DataDefModel.Table

        'boMainPropertyBag: These hold the attributes of the tables ConnectionInfo object
        Dim boMainPropertyBag As New PropertyBag
        'boInnerPropertyBag: These hold the attributes for the QE_LogonProperties
        'In the main property bag (boMainPropertyBag)
        Dim boInnerPropertyBag As New PropertyBag

        'Set the attributes for the boInnerPropertyBag
        boInnerPropertyBag.Add("Connection String", "Driver={Pervasive ODBC Client Interface};ServerName=ServerName;dbq=DatabaseName")
        boInnerPropertyBag.Add("Server", "ServerName")
        boInnerPropertyBag.Add("UseDSNProperties", "False")

        'Set the attributes for the boMainPropertyBag
        boMainPropertyBag.Add("Database DLL", "crdb_odbc.dll")
        boMainPropertyBag.Add("QE_DatabaseName", "")
        boMainPropertyBag.Add("QE_DatabaseType", "ODBC (RDO)")
        'Add the QE_LogonProperties we set in the boInnerPropertyBag Object
        boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag)
        boMainPropertyBag.Add("QE_ServerDescription", "ServerName")
        boMainPropertyBag.Add("QE_SQLDB", "True")
        boMainPropertyBag.Add("SSO Enabled", "False")

        'Create a new ConnectionInfo object
        Dim boConnectionInfo As New CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo
        'Pass the database properties to a connection info object
        boConnectionInfo.Attributes = boMainPropertyBag
        'Set the connection kind
        boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE
        '**EDIT** Set the User Name and Password if required.
        boConnectionInfo.UserName = "UserName"
        boConnectionInfo.Password = "Password"
        'Pass the connection information to the table
        boTable.ConnectionInfo = boConnectionInfo

        'Get the Database Tables Collection for your report
        Dim boTables As CrystalDecisions.ReportAppServer.DataDefModel.Tables = _
        boReportDocument.ReportClientDocument.DatabaseController.Database.Tables

        'For each table in the report:
        ' - Set the Table Name properties.
        ' - Set the table location in the report to use the new modified table
        boTable.Name = "TableName"
        boTable.QualifiedName = "TableName"
        boTable.Alias = "TableName"

        boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables(0), boTable)

        'Verify the database after adding substituting the new table.
        'To ensure that the table updates properly when adding Command tables or Stored Procedures.
        boReportDocument.VerifyDatabase()
        CrystalReportViewer1.ReportSource = boReportDocument

    End Sub 

End Class

KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , How To

SAP Crystal Reports, developer version for Microsoft Visual Studio