Hello
I created a report that displays data from SQL Server 2005. For that, I am using crystal reports that comes with MS Visual Studio 2003.In the report, I am using crosstab object to display data. It works well. Now, I am trying to change the connection information during runtime. I am not sure how I can do it. Before, I was trying to change the connection info of the report containing subreports. It worked well. Now, I am trying to reuse the code for changing connection information for a crosstab reportobject. It doesn't seem to work as I am not sure how to open a crosstabobject in the reportdocument to change its connection info for all tables. MS Visual studio 2003 doesn't have any property like OpenCrossTab as it has with OpenSubreport. The code for subreports is as follows.
'===================================================================
Dim crossRepDoc As ReportDocument
Dim subRepDoc As New ReportDocument
Dim crSections As Sections
Dim crSection As Section
Dim crReportObjects As ReportObjects
Dim crReportObject As ReportObject
Dim crSubreportObject As SubreportObject
Dim crCrossreportObject As CrossTabObject
Dim crDatabase As Database
Dim crTables As Tables
Dim crTable As Table
Dim crLogOnInfo As TableLogOnInfo
Dim crConnInfo As New ConnectionInfo
Dim crpt205 As r205
crpt205 = New r205
crDatabase = crpt205.Database
crTables = crDatabase.Tables
'Loop through each table in the main report
For Each crTable In crTables
With crConnInfo
.ServerName = "WEBDEV01"
.DatabaseName = "CMS_DB"
.UserID = "sa"
.Password = "a*71584"
End With
crLogOnInfo = crTable.LogOnInfo
crLogOnInfo.ConnectionInfo = crConnInfo
crTable.ApplyLogOnInfo(crLogOnInfo)
crTable.Location = "CMS_DB.dbo." & crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
Next
'Set the sections collection with report sections
crSections = crpt205.ReportDefinition.Sections
'Loop through each section and find all the report objects
'logoninfo to the subreport
For Each crSection In crSections
crReportObjects = crSection.ReportObjects
For Each crReportObject In crReportObjects
If crReportObject.Kind = ReportObjectKind.SubreportObject Then
'If you find a subreport, typecast the reportobject to a subreport object
crSubreportObject = CType(crReportObject, SubreportObject)
=======================================================================================
'I am checking for crosstab object instead of SubreportObject
If crReportObject.Kind = ReportObjectKind.CrossTabObject Then
crCrossreportObject = CType(crReportObject, CrossTabObject)
======================================================================================
'======================Main Problem here: Don't have any OpenCrosstab property ====================
'Open the subreport
subRepDoc = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)
'========================================================================================
crDatabase = subRepDoc.Database
crTables = crDatabase.Tables
'Loop through each table and set the connection info
'Pass the connection info to the logoninfo object then apply the
'logoninfo to the subreport
For Each crTable In crTables
With crConnInfo
.ServerName = "WEBDEV01"
.DatabaseName = "CMS_DB"
.UserID = "sa"
.Password = "a*71584"
End With
crLogOnInfo = crTable.LogOnInfo
crLogOnInfo.ConnectionInfo = crConnInfo
crTable.ApplyLogOnInfo(crLogOnInfo)
crTable.Location = "CMS_DB.dbo." & crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
Next
End If
Next
Next
' Set the ReportDocument to the viewer
' The report previews when the form is constructed
CrystalReportViewer1.ReportSource = crpt205
'===================================================================
Please suggest how I can change the connection info of a crosstab object during runtime.
I created a report that displays data from SQL Server 2005. For that, I am using crystal reports that comes with MS Visual Studio 2003.In the report, I am using crosstab object to display data. It works well. Now, I am trying to change the connection information during runtime. I am not sure how I can do it. Before, I was trying to change the connection info of the report containing subreports. It worked well. Now, I am trying to reuse the code for changing connection information for a crosstab reportobject. It doesn't seem to work as I am not sure how to open a crosstabobject in the reportdocument to change its connection info for all tables. MS Visual studio 2003 doesn't have any property like OpenCrossTab as it has with OpenSubreport. The code for subreports is as follows.
'===================================================================
Dim crossRepDoc As ReportDocument
Dim subRepDoc As New ReportDocument
Dim crSections As Sections
Dim crSection As Section
Dim crReportObjects As ReportObjects
Dim crReportObject As ReportObject
Dim crSubreportObject As SubreportObject
Dim crCrossreportObject As CrossTabObject
Dim crDatabase As Database
Dim crTables As Tables
Dim crTable As Table
Dim crLogOnInfo As TableLogOnInfo
Dim crConnInfo As New ConnectionInfo
Dim crpt205 As r205
crpt205 = New r205
crDatabase = crpt205.Database
crTables = crDatabase.Tables
'Loop through each table in the main report
For Each crTable In crTables
With crConnInfo
.ServerName = "WEBDEV01"
.DatabaseName = "CMS_DB"
.UserID = "sa"
.Password = "a*71584"
End With
crLogOnInfo = crTable.LogOnInfo
crLogOnInfo.ConnectionInfo = crConnInfo
crTable.ApplyLogOnInfo(crLogOnInfo)
crTable.Location = "CMS_DB.dbo." & crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
Next
'Set the sections collection with report sections
crSections = crpt205.ReportDefinition.Sections
'Loop through each section and find all the report objects
'logoninfo to the subreport
For Each crSection In crSections
crReportObjects = crSection.ReportObjects
For Each crReportObject In crReportObjects
If crReportObject.Kind = ReportObjectKind.SubreportObject Then
'If you find a subreport, typecast the reportobject to a subreport object
crSubreportObject = CType(crReportObject, SubreportObject)
=======================================================================================
'I am checking for crosstab object instead of SubreportObject
If crReportObject.Kind = ReportObjectKind.CrossTabObject Then
crCrossreportObject = CType(crReportObject, CrossTabObject)
======================================================================================
'======================Main Problem here: Don't have any OpenCrosstab property ====================
'Open the subreport
subRepDoc = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)
'========================================================================================
crDatabase = subRepDoc.Database
crTables = crDatabase.Tables
'Loop through each table and set the connection info
'Pass the connection info to the logoninfo object then apply the
'logoninfo to the subreport
For Each crTable In crTables
With crConnInfo
.ServerName = "WEBDEV01"
.DatabaseName = "CMS_DB"
.UserID = "sa"
.Password = "a*71584"
End With
crLogOnInfo = crTable.LogOnInfo
crLogOnInfo.ConnectionInfo = crConnInfo
crTable.ApplyLogOnInfo(crLogOnInfo)
crTable.Location = "CMS_DB.dbo." & crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
Next
End If
Next
Next
' Set the ReportDocument to the viewer
' The report previews when the form is constructed
CrystalReportViewer1.ReportSource = crpt205
'===================================================================
Please suggest how I can change the connection info of a crosstab object during runtime.