Programmatically Change A Crystal Reports Datasource Location

• Tweet
• 

        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("<div>" & Server.HtmlEncode(strLine) & "</div>")

   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("<html><head><title>Debug Page</title></head><body>")
      Response.Write(m_strDebugPage.ToString())
      Response.Write("</body></html>")

      Response.Flush()
      Response.End()

   End Function
   ' --------------------------------------------------------------------------

Tags:
• crystal
• datasource
• Location
• Reports
• vb.net

Comments

Snippets Manager replied on Thu, 2012/01/05 - 6:25pm

Are you still around Will. I was wondering why i get an erro that crdb_oracle.dll could not be loaded. Thanks.

• Login or register to post comments

Scott Thornton replied on Tue, 2009/09/01 - 12:13am

Thanks for the great post. Would you happen to have a classic .asp example of this? I am trying to change report location at runtime, but with little success.

• Login or register to post comments