'******************************************************************************************************************
'Procedure: SetReportDataSources
'Description: ' Crystal saves the Login Information and Location as part of the report. This procedure
' overrides the login information and location for all the report's DatabaseTables including any
' Subreports. It assumes all tables use the same login criteria (ie all data is from the same database).
' It also assumes that all reports are created from stored procedures(System standard).
' In order to override the Location we need to parse the DatabaseTable.Location property.
' This allows us to easily support several "environments" but we need to tell the Crystal engine
' which Login information to use and which stored procedure to use.
' For example - An application has three environments. Production, QA and Dev. The QA and Dev
' databases are called Pubs_Dev and Pubs_QA on server Handel. A developer creates a report
' using the Pubs_Dev database and a stored procedure called selAuthorsbyState. Crystal will save the
' Location as pubs_dev.dbo.Proc(selAuthorsByState). Note that the location written does depend on the
' author's options/preferences. We are not going to rely on a everyone following a "Standard Practice".
' When a user named TestUser runs the application, selects the QA environment at login,
' and prints the report - the data is still retrieved from the Pubs_Dev database(even if we tell Crystal
' to login to the QA database).
' We must change the Location to pubs_QA.dbo.Proc(selAuthorsByState) or simply selAuthorsByState
' this ensures the data is retrieved from the QA database tables.
'******************************************************************************************************************
Private Sub SetReportDataSources(CrystalReport As CRAXDRT.Report, ServerName As String, DatabaseName As String, UserName As String, Password As String)
On Error GoTo ErrorHandler
Dim lngStartPos As Long
Dim lngEndPos As Long
Dim objSubReport As CRAXDRT.Report
Dim objDB As CRAXDRT.Database
Dim objTables As CRAXDRT.DatabaseTables
Dim objTable As CRAXDRT.DatabaseTable
Dim objSections As CRAXDRT.Sections
Dim objSection As CRAXDRT.Section
Dim objReportObjects As CRAXDRT.ReportObjects
Dim objSubReports As CRAXDRT.SubreportObject
Dim lngIdx As Long
Set objDB = CrystalReport.Database
Set objTables = objDB.Tables
'set the login information and location for each table in the report
For Each objTable In objTables
lngStartPos = InStr(1, objTable.Location, "(", vbTextCompare) + 1
lngEndPos = InStr(lngStartPos, objTable.Location, ")", vbTextCompare)
If lngEndPos = 0 Then
lngEndPos = Len(objTable.Location) + 1
End If
lngEndPos = lngEndPos - lngStartPos
objTable.Location = Mid$(objTable.Location, lngStartPos, lngEndPos)
objTable.SetLogOnInfo ServerName, DatabaseName, UserName, Password
Next
'set the login information and location for each table in the all subreports
'Access to the sub reports is through the Sections collection
Set objSections = CrystalReport.Sections
For Each objSection In objSections
Set objReportObjects = objSection.ReportObjects
If objReportObjects.Count > 0 Then
For lngIdx = 1 To objReportObjects.Count
'make sure the report object is a subreport.
If objReportObjects(lngIdx).Kind = crSubreportObject Then
'open the subreport and set the login information
Set objSubReports = objReportObjects(lngIdx)
Set objSubReport = CrystalReport.OpenSubreport(objSubReports.SubreportName)
Set objDB = objSubReport.Database
Set objTables = objDB.Tables
For Each objTable In objTables
lngStartPos = InStr(1, objTable.Location, "(", vbTextCompare) + 1
lngEndPos = InStr(lngStartPos, objTable.Location, ")", vbTextCompare)
If lngEndPos = 0 Then
lngEndPos = Len(objTable.Location) + 1
End If
lngEndPos = lngEndPos - lngStartPos
objTable.Location = Mid$(objTable.Location, lngStartPos, lngEndPos)
objTable.SetLogOnInfo ServerName, DatabaseName, UserName, Password
Next
End If
Next
End If
Next
Exit Sub
ErrorHandler:
Err.Raise Err.Number, "SetReportDataSources", Err.Source, Err.Description
End Sub
Crystal Asking A Parameter
Reply With Quote
Originally Posted by brucevde
