Programmatically Change a Crystal Reports datasource location

Will_Rickards (Will Rickards) — Wed, 16 May 2007 20:30:09 GMT
I had an aspx.net project where I needed to generate a pdf.
I used crystal to do the heavy lifting of geneating the pdf for me.
But as I needed to deploy it to multiple databases/clients I needed a way of programmatically changing the datasource location of the report. So with a bit of help from the crystal knowledgebase here is the code I use.
There is a lot of debugging information in here and trust me when it fails you need it.
The .dbo of the location reference obviously means I was working with sql server.


   ' --------------------------------------------------------------------------

   ' SetupReport

   '

   ' Description:

   '    sets up the crystal report

   '

   ' Arguments:

   '    objCrystalReportDocument             - crystal report document object

   '

   ' Dependencies:

   '    GetConnnectionInfo

   '    CrystalDescisions

   '

   ' History:

   ' 03/17/2006 - WSR : created

   '

   Private Function SetupReport(ByRef objCrystalReportDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument) As System.Boolean



      ' a heck of a lot of objects used

      Dim crParameterDiscreteValue As CrystalDecisions.Shared.ParameterDiscreteValue

      Dim crParameterFieldDefinitions As CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinitions

      Dim crParameterFieldLocation As CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition

      Dim crParameterValues As CrystalDecisions.Shared.ParameterValues

      Dim crSections As CrystalDecisions.CrystalReports.Engine.Sections

      Dim crSection As CrystalDecisions.CrystalReports.Engine.Section

      Dim crReportObjects As CrystalDecisions.CrystalReports.Engine.ReportObjects

      Dim crReportObject As CrystalDecisions.CrystalReports.Engine.ReportObject

      Dim crSubreportDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument

      Dim crSubreportObject As CrystalDecisions.CrystalReports.Engine.SubreportObject

      Dim crConnectionInfo As CrystalDecisions.Shared.ConnectionInfo

      Dim crDatabase As CrystalDecisions.CrystalReports.Engine.Database

      Dim crTables As CrystalDecisions.CrystalReports.Engine.Tables

      Dim aTable As CrystalDecisions.CrystalReports.Engine.Table

      Dim bTable As CrystalDecisions.CrystalReports.Engine.Table

      Dim crTableLogOnInfo As CrystalDecisions.Shared.TableLogOnInfo

      Dim blnTest As System.Boolean

      Dim strLocation As System.String

      Dim blnErrors As System.Boolean



      ' instantiate the debug page

      m_strDebugPage = New System.Text.StringBuilder(4096)

      blnErrors = False



      crConnectionInfo = GetConnectionInfo()



      crDatabase = objCrystalReportDocument.Database



      crTables = crDatabase.Tables



      'For intCounter = 0 To objCrystalReportDocument.Database.Tables.Count - 1

      For Each aTable In crTables



         crTableLogOnInfo = aTable.LogOnInfo



         OutputDebugLine("BEFORE")

         OutputDebugLine("TABLE NAME: " & aTable.Name)

         OutputDebugLine("TABLE LOC: " & aTable.Location)

         OutputDebugLine("SERVER: " & crTableLogOnInfo.ConnectionInfo.ServerName)

         OutputDebugLine("DB: " & crTableLogOnInfo.ConnectionInfo.DatabaseName)

         OutputDebugLine("UID: " & crTableLogOnInfo.ConnectionInfo.UserID)

         OutputDebugLine("PWD: " & crTableLogOnInfo.ConnectionInfo.Password)

         OutputDebugLine("RN: " & crTableLogOnInfo.ReportName)

         OutputDebugLine("TN: " & crTableLogOnInfo.TableName)



         crTableLogOnInfo.ConnectionInfo = crConnectionInfo

         aTable.ApplyLogOnInfo(crTableLogOnInfo)

         strLocation = crConnectionInfo.DatabaseName & ".dbo." & aTable.Location.Substring(aTable.Location.LastIndexOf(".") + 1)

         OutputDebugLine("New Location: " & strLocation)

         Try

            aTable.Location = strLocation

         Catch ex As Exception

            OutputDebugLine("Set Location Error: " & ex.ToString)

            blnErrors = True

         End Try



         OutputDebugLine("AFTER")

         OutputDebugLine("TABLE NAME: " & aTable.Name)

         OutputDebugLine("TABLE LOC: " & aTable.Location)

         OutputDebugLine("SERVER: " & crTableLogOnInfo.ConnectionInfo.ServerName)

         OutputDebugLine("DB: " & crTableLogOnInfo.ConnectionInfo.DatabaseName)

         OutputDebugLine("UID: " & crTableLogOnInfo.ConnectionInfo.UserID)

         OutputDebugLine("PWD: " & crTableLogOnInfo.ConnectionInfo.Password)

         OutputDebugLine("RN: " & crTableLogOnInfo.ReportName)

         OutputDebugLine("TN: " & crTableLogOnInfo.TableName)

         Try

            blnTest = aTable.TestConnectivity()

            OutputDebugLine("CONNECTED? " & blnTest.ToString())

         Catch ex As Exception

            OutputDebugLine("CONNECTED? NO")

            OutputDebugLine(ex.ToString)

            blnErrors = True

         End Try



         '// THIS STUFF HERE IS FOR REPORTS HAVING SUBREPORTS 

         '// set the sections object to the current report's section 

         crSections = objCrystalReportDocument.ReportDefinition.Sections



         '// loop through all the sections to find all the report objects 

         For Each crSection In crSections



            crReportObjects = crSection.ReportObjects



            '//loop through all the report objects in there to find all subreports 

            For Each crReportObject In crReportObjects



               If crReportObject.Kind = ReportObjectKind.SubreportObject Then



                  crSubreportObject = CType(crReportObject, CrystalDecisions.CrystalReports.Engine.SubreportObject)

                  '//open the subreport object and logon as for the general report 

                  crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)

                  crDatabase = crSubreportDocument.Database

                  crTables = crDatabase.Tables



                  For Each bTable In crTables



                     crTableLogOnInfo = bTable.LogOnInfo



                     OutputDebugLine("BEFORE")

                     OutputDebugLine("TABLE NAME: " & bTable.Name)

                     OutputDebugLine("TABLE LOC: " & bTable.Location)

                     OutputDebugLine("SERVER: " & crTableLogOnInfo.ConnectionInfo.ServerName)

                     OutputDebugLine("DB: " & crTableLogOnInfo.ConnectionInfo.DatabaseName)

                     OutputDebugLine("UID: " & crTableLogOnInfo.ConnectionInfo.UserID)

                     OutputDebugLine("PWD: " & crTableLogOnInfo.ConnectionInfo.Password)

                     OutputDebugLine("RN: " & crTableLogOnInfo.ReportName)

                     OutputDebugLine("TN: " & crTableLogOnInfo.TableName)



                     crTableLogOnInfo.ConnectionInfo = crConnectionInfo

                     bTable.ApplyLogOnInfo(crTableLogOnInfo)

                     strLocation = crConnectionInfo.DatabaseName & ".dbo." & bTable.Location.Substring(bTable.Location.LastIndexOf(".") + 1)

                     OutputDebugLine("New Location: " & strLocation)

                     Try

                        bTable.Location = strLocation

                     Catch ex As Exception

                        OutputDebugLine("Set Location Error: " & ex.ToString)

                        blnErrors = True

                     End Try



                     OutputDebugLine("AFTER")

                     OutputDebugLine("TABLE NAME: " & bTable.Name)

                     OutputDebugLine("TABLE LOC: " & bTable.Location)

                     OutputDebugLine("SERVER: " & crTableLogOnInfo.ConnectionInfo.ServerName)

                     OutputDebugLine("DB: " & crTableLogOnInfo.ConnectionInfo.DatabaseName)

                     OutputDebugLine("UID: " & crTableLogOnInfo.ConnectionInfo.UserID)

                     OutputDebugLine("PWD: " & crTableLogOnInfo.ConnectionInfo.Password)

                     OutputDebugLine("RN: " & crTableLogOnInfo.ReportName)

                     OutputDebugLine("TN: " & crTableLogOnInfo.TableName)

                     Try

                        blnTest = bTable.TestConnectivity()

                        OutputDebugLine("CONNECTED? " & blnTest.ToString())

                     Catch ex As Exception

                        OutputDebugLine("CONNECTED? NO")

                        OutputDebugLine(ex.ToString)

                        blnErrors = True

                     End Try



                  Next bTable



               End If



            Next crReportObject



         Next crSection



      Next aTable



      ' get parameter fields from report

      crParameterFieldDefinitions = objCrystalReportDocument.DataDefinition.ParameterFields



      '    ' Set the first parameter

      '    ' - Get the parameter, tell it to use the current values vs default value.

      '    ' - Tell it the parameter contains 1 discrete value vs multiple values.

      '    ' - Set the parameter's value.

      '    ' - Add it and apply it.

      '    ' - Repeat these statements for each parameter.

      '    '

      crParameterFieldLocation = crParameterFieldDefinitions.Item("@psindex")

      crParameterValues = crParameterFieldLocation.CurrentValues

      crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue

      crParameterDiscreteValue.Value = m_intReportID

      crParameterValues.Add(crParameterDiscreteValue)

      crParameterFieldLocation.ApplyCurrentValues(crParameterValues)



      ' if there were errors

      If blnErrors Then



         ' display debug page

         OutputDebugPage()



      End If



   End Function

   ' --------------------------------------------------------------------------





   ' --------------------------------------------------------------------------

   ' GetConnectionInfo

   '

   ' Description:

   '    retrieves the connection information from the data layer object

   '

   ' Arguments: none

   '

   ' Dependencies:

   '    DataLayer.GetConnectInfo

   '

   ' History:

   ' 03/17/2006 - WSR : created

   '

   Private Function GetConnectionInfo() As CrystalDecisions.Shared.ConnectionInfo



      Dim objConnectionInfo As CrystalDecisions.Shared.ConnectionInfo

      Dim strDSN As System.String

      Dim strDB As System.String

      Dim strUID As System.String

      Dim strPWD As System.String

      Dim blnTrust As System.Boolean



      ' get connection information from data layer

      m_objDataLayer.GetConnectInfo(strDSN, strDB, strUID, strPWD, blnTrust)



      ' create new crystal connection info object

      objConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo



      ' populate it

      objConnectionInfo.IntegratedSecurity = False

      objConnectionInfo.ServerName = strDSN

      objConnectionInfo.UserID = strUID

      objConnectionInfo.Password = strPWD

      objConnectionInfo.DatabaseName = strDB



      ' return object

      GetConnectionInfo = objConnectionInfo



   End Function

   ' --------------------------------------------------------------------------



   ' --------------------------------------------------------------------------

   ' OutputDebugLine

   '

   ' Description: appends a line to the debug string builder

   '

   ' Arguments: text to add

   '

   ' Dependencies:

   '    m_strDebugPage

   '

   ' History:

   ' 03/17/2006 - WSR : created

   ' 2007.04.25 - WSR : revised to use string builder

   '

   Function OutputDebugLine(ByVal strLine As System.String) As System.Boolean



      m_strDebugPage.Append("" & Server.HtmlEncode(strLine) & "")



   End Function

   ' --------------------------------------------------------------------------





   ' --------------------------------------------------------------------------

   ' OutputDebugPage

   '

   ' Description: sends debug string builder to response

   '

   ' Arguments: none

   '

   ' Dependencies:

   '    m_strDebugPage

   '

   ' History:

   ' 2007.04.25 - WSR : created

   '

   Function OutputDebugPage() As System.Boolean



      With Response

         .ClearHeaders()

         .ClearContent()

         .ContentType = "text/html"

      End With



      Response.Write("Debug Page")

      Response.Write(m_strDebugPage.ToString())

      Response.Write("")



      Response.Flush()

      Response.End()



   End Function

   ' --------------------------------------------------------------------------
DZone Snippets: crystal code

Programmatically Change a Crystal Reports datasource location
