1217802 - How to change the database and server at runtime using the RAS .NET SDK

A Microsoft Visual Studio .NET application uses the .NET Report Application Server (RAS) SDK in Crystal Enterprise (CE) 10 Embedded Edition as the reporting development tool.

At runtime, how do you change the server, database, username, and password for a report and its subreport?

To change the server, database, username and password for a report and its subreport, use the following VB .NET sample code.

This sample code changes the location of the database from one Microsoft SQL Server to another Microsoft SQL Server database. The report connects to the database using an OLE DB connection.

====================

NOTE:

The VB .NET sample code creates a custom function called 'GetNewTable' to create a new 'CrystalDecisions.ReportAppServer.DataDefModel.TableClass' object based on an old table, server, database,

username, and password.

The code loops through all the tables in the main and subreport report, and then uses the 'SetTableLocation' method to set the new table location.

====================

Imports CrystalDecisions.ReportAppServer.Controllers

Imports CrystalDecisions.ReportAppServer.ClientDoc

Imports CrystalDecisions.ReportAppServer.DataDefModel

'

' ************ Class definition info ************

'

Private Sub Form1_Load(ByVal sender As Object,

ByVal e As System.EventArgs) Handles MyBase.Load

Dim oFilename As Object = "YourReportName.rpt"

Dim crDatabaseController As DatabaseController

Dim crDatabase As Database

Dim crSubreportController As SubreportController

Dim crTableOld, crTableNew As Table

Dim strSubreportname As String

Dim strServerName, strDatabaseName,

strUserName, strPassword As String

' Set the values for the logon information.

strServerName = "ServerName"

strDatabaseName = "DatabaseName"

strUserName = "YourUserID"

strPassword = "YourPassword"

m_crReportClientDocument = New ReportClientDocumentClass

m_crReportClientDocument.Open(oFilename, 0)

crDatabase = m_crReportClientDocument.DatabaseController.Database

crDatabaseController = m_crReportClientDocument.DatabaseController

crSubreportController = m_crReportClientDocument.SubreportController

' Loop through all the tables in the main

' report and set their new table information.

For Each crTableOld In crDatabase.Tables

crTableNew = GetNewTable(crTableOld, _

strServerName, _

strDatabaseName, _

strUserName, _

strPassword)

crDatabaseController.SetTableLocation(crTableOld, crTableNew)

crTableNew = Nothing

Next

crDatabase = Nothing

crTableOld = Nothing

' Loop through each subreport in the main report.

For Each strSubreportname In crSubreportController.QuerySubreportNames

' Get the database of the subreport

crDatabase = crSubreportController.GetSubreportDatabase(strSubreportname)

' Loop through each table in the subreport

' database and set its new table.

For Each crTableOld In crDatabase.Tables

crTableNew = GetNewTable(crTableOld, _

strServerName, _

strDatabaseName, _

strUserName, _

strPassword)

crSubreportController.SetTableLocation(strSubreportname, crTableOld, crTableNew)

crTableNew = Nothing

Next

Next

crDatabase = Nothing

crTableOld = Nothing

crSubreportController = Nothing

crDatabaseController = Nothing

CrystalReportViewer1.ReportSource = m_crReportClientDocument

End Sub

Private Function GetNewTable(ByVal crTableOld As Table, _

ByRef serverName As String, _

ByRef databaseName As String, _

ByRef userName As String, _

ByRef password As String) As Table

' Create crTableNew as a new table, then fill its properties.

Dim crTableNew As New Table

Dim crAttributes, crLogonInfo As PropertyBag

' Set some of the properties of the new table object.

crTableNew.ConnectionInfo.UserName = userName

crTableNew.ConnectionInfo.Password = password

crTableNew.ConnectionInfo.Kind = crTableOld.ConnectionInfo.Kind

crTableNew.Name = crTableOld.Name

crTableNew.Alias = crTableOld.Alias

' Make a copy of the connectionInfo.Attributes

' and apply them to the new table.

crTableNew.ConnectionInfo.Attributes = crTableOld.ConnectionInfo.Attributes.Clone(True)

crAttributes = crTableNew.ConnectionInfo.Attributes

' Check to ensure the connection info Kind

' is correct then change the appropriate 

' properties in the PropertyBag.

' These will vary from report to report. To

' find out your properties open your report 

' in the Crystal Reports Designer and go to

' Database , Set Location menu.

' Check the properties of your connection and

' note the name of the properties

' that will change.

If crTableNew.ConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE Then

crLogonInfo = CType(crAttributes("QE_LogonProperties"), PropertyBag)

crLogonInfo("Data Source") = serverName

crLogonInfo("Initial Catalog") = databaseName

End If

' Adjust the qualified name, then return the new table object.

crTableNew.QualifiedName = databaseName & ".dbo." & crTableNew.Name

Return crTableNew

End Function

DOTNET .NET RAS RUN-TIME CHANGE SUBREPORT SQL OLEDB VB.NET DB SWITCH MSSQL Crystal Reports Report Application Server .NET SDK Changing database , c2015898 , KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , Problem

SAP Crystal Reports, developer version for Microsoft Visual Studio ; SAP Crystal Reports, version for Visual Studio .NET 2008