1214777 - How to change the data source using CR for VS .NET application

SAP Knowledge Base Article - Public

1214777 - How to change the data source using CR for VS .NET application

Symptom

An application uses Crystal Reports for Visual Studio .NET as the reporting development tool.

How do you change the database and server in a report at runtime?

For example, if a report is designed against the 'pubs1' database and the 'server1' server, how can you change the database to 'pubs2' and the server to 'server2'?

 

Resolution

To change the database and server at runtime, copy one of the following code samples into your class (use the 'logon' method defined in the code to change the database and server):

 

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

NOTES:

 

" When using ODBC, the DSN name is the server name. The DSN should be under the System DSN and not under the User DSN.

 

" The 'logon' method cannot change the database file name when using DAO (Microsoft Access file[.mdb]). See knowledge base article c2010490 for more information on changing the path to an Access database.

 

" If you have a subreport that connects to a different database or server than your main report, use the 'ApplyLogon' method instead of the 'logon' method. When using the ApplyLogon method, ensure you iterate through each subreport.

 

" It is not possible to use the CR for VS .NET SDK to change a report from using SQL Authentication to Windows Authentication, or vice versa. This functionality is available through the Report Application Server (RAS) .NET SDK or Report Designer Component (RDC).

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

 

--------------------- C# .NET ---------------------

 

// Helper method that iterates through all tables

// in a report document

 

Using CrystalDecisions.CrystalReports.Engine

Using CrystalDecisions.Shared

 

bool ApplyLogon(ReportDocument cr, ConnectionInfoci)

{

TableLogOnInfo li;

// for each table apply connection info

foreach (Table tbl in cr.Database.Tables)

{

li = tbl.LogOnInfo;

li.ConnectionInfo = ci;

tbl.ApplyLogOnInfo(li);

 

// check if logon was successful

// if TestConnectivity returns false, check

// logon credentials

 

if (tbl.TestConnectivity())

{

// drop fully qualified table location

if (tbl.Location.IndexOf(".") > 0)

{

tbl.Location = tbl.Location.Substring(tbl.Location.LastIndexOf(".") + 1);

}

else tbl.Location = tbl.Location;

}

else return(false)

}

return(true);

}

 

 

// The Logon method iterates through all tables

bool Logon(ReportDocument cr, string server,

string db, string id, string pass)

{

ConnectionInfo ci = new ConnectionInfo();

SubreportObject subObj;

 

ci.ServerName = server;

ci.DatabaseName = db;

ci.UserID = id;

ci.Password = pass;

 

if (!ApplyLogon(cr, ci)) return (false);

 

foreach (ReportObject obj in cr.ReportDefinition.ReportObjects)

{

if (obj.Kind == ReportObjectKind.SubreportObject)

{

subObj = (SubreportObject)obj;

if

(!ApplyLogon(cr.OpenSubreport(subObj.SubreportName), ci)) return(false);

}

}

return (true);

}

 

 

---------------------- VB .NET ---------------------

 

Imports CrystalDecisions.CrystalReports.Engine

Imports CrystalDecisions.Shared

 

Function ApplyLogon(ByVal cr As ReportDocument, ByVal ci As ConnectionInfo) As Boolean

 

Dim li As TableLogOnInfo

Dim tbl As Table

 

' for each table apply connection info

 

For Each tbl In cr.Database.Tables

li = tbl.LogOnInfo

li.ConnectionInfo = ci

tbl.ApplyLogOnInfo(li)

 

' check if logon was successful

' if TestConnectivity returns false,

' check logon credentials

 

If (tbl.TestConnectivity()) Then

'drop fully qualified table location

If (tbl.Location.IndexOf(".") > 0)

Then

tbl.Location = tbl.Location.Substring(tbl.Location.LastIndexOf(".") + 1)

Else

tbl.Location = tbl.Location

End If

Else

Return False

End If

 

Next

Return True

End Function

 

 

'The Logon method iterates through all tables

 

Function Logon(ByVal cr As ReportDocument, ByVal server As String, ByVal db As String, ByVal id As String, ByVal pass As String) As Boolean

Dim ci As New ConnectionInfo()

Dim subObj As SubreportObject

ci.ServerName = server

ci.DatabaseName = db

ci.UserID = id

ci.Password = pass

 

If Not (ApplyLogon(cr, ci)) Then

Return False

End If

 

Dim obj As ReportObject

 

For Each obj In cr.ReportDefinition.ReportObjects

If (obj.Kind = ReportObjectKind.SubreportObject) Then

subObj = CType(obj, SubreportObject)

If Not

(ApplyLogon(cr.OpenSubreport(subObj.SubreportName), ci)) Then

Return False

End If

End If

Next

Return True

End Function

Keywords

LOGIN LOGON DATABASE LOCATION DOTNET VS.NET DOTNET SWITCH CHANGE SET DATA SOURCE Crystal Reports for Visual Studio Database connectivity Change Database and Server , c2011464 , KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , Problem

Product

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