Crystal Report Source 1

[graphix03]

Hi,
I created some reports, but they are
right now poiting to the development
database. Later on, at some point,
they would be move to the production
database. Is there a way to connect
these reports to the production WITHOUT
have to remove the database source
and replace all the fields all over
again?

thanks much

 

[vidru]

Can you give a little more info:
1) CR version
2) Development environment
3) Database Type/Connectivity method.

From your previous posts, I can gather that you're using VB6 and CR 8.5, but my crystal ball is hazy as far as your db type.

I've got a pretty tried and true method of changing the db at runtime when using an ODBC connection to SQL Server with Crystal 8.5.

-dave

 

[graphix03]

Hi dave, thanks for your response. your guess
is correct. but the crystal is by itself 8.5, it's
not a part of vb6. then, once i'm done with
these reports then they would be incorporated
into vb (using vb as the interface to show the
reports).

right now, i'm not using odbc. i'm connecting
directly to sql server because the report
runs on the stored procedures.

 

[vidru]

One of these days, I'll turn this into a FAQ:

'General Declarations
Dim crxApp As New CRAXDRT.Application
Dim crxRpt As CRAXDRT.Report
Dim crxTables As CRAXDRT.DatabaseTables
Dim crxTable As CRAXDRT.DatabaseTable

Private Sub Form_Load()

    'Open the report
    Set crxRpt = crxApp.OpenReport("C:\MyReport.rpt") 

    'Set the connection for the report
    crxRpt.Database.Tables(1).SetLogOnInfo "SERVER_NAME", "DB_NAME", "USER_ID", "PASSWORD"

    'This removes the schema from the Location property.
    'The report will now use the Server and Database
    '  that were passed in the SetLogOnInfo method.
    Set crxTables = crxRpt.Database.Tables
    For Each crxTable In crxTables
        With crxTable
             .Location = .Name
        End With
    Next

    'View the report
    Viewer.ReportSource = crxRpt
    Viewer.ViewReport
    Exit Sub

-dave

 

[graphix03]

Hi dave, thanks very much for the code,
only you would want to turn it into FAQ,
because you care . thanks again.
you're a star.

 

[WdnUlik2no]

Vidru,

I hope you still come here. I am running into the exact same problem as graphix03, only that I still can't get my app to change the database location of the crystal report. I have seen your FAQ, but that still does not help me. what could I be doing wrong? I am using CRXI and VB6. Here is my code (well your code) that I used.

Dim crxApp As New CRAXDRT.Application
Dim crxRpt As CRAXDRT.Report
Dim crxTables As CRAXDRT.DatabaseTables
Dim crxTable As CRAXDRT.DatabaseTable
Dim crxSubreportObject As CRAXDRT.SubreportObject
Dim crxSubReport As CRAXDRT.Report
Dim crxSections As CRAXDRT.Sections
Dim crxSection As CRAXDRT.Section

Private Sub Command1_Click()
Viewer.Refresh
End Sub

Private Sub Form_Load()
'Variable declarations
Dim strServerOrDSNName As String
Dim strDBNameOrPath As String
Dim strUserID As String
Dim strPassword As String

strServerOrDSNName = "dev.ourapp.com/appdev"

strDBNameOrPath = "com.mysql.jdbc.Driver;"
strUserID = "appuser"
strPassword = "apppassword"

Set crxRpt = crxApp.OpenReport("C:\test.rpt")


crxRpt.Database.Tables(1).SetLogOnInfo strServerOrDSNName, _
strDBNameOrPath, strUserID, strPassword

Set crxTables = crxRpt.Database.Tables
For Each crxTable In crxTables
With crxTable
.Location = .Name
End With
Next

'Loop through the Report's Sections to find any subreports, _
and change them as well
Set crxSections = crxRpt.Sections

For i = 1 To crxSections.Count
Set crxSection = crxSections(i)

For j = 1 To crxSection.ReportObjects.Count

If crxSection.ReportObjects(j).Kind = crSubreportObject Then
Set crxSubreportObject = crxSection.ReportObjects(j)

'Open the subreport, and treat like any other report
Set crxSubReport = crxSubreportObject.OpenSubreport
'*****************************************
Set crxTables = crxSubReport.Database.Tables

For Each crxTable In crxTables
With crxTable
.SetLogOnInfo strServerOrDSNName, _
strDBNameOrPath, strUserID, strPassword
.Location = .Name
End With
Next


'****************************************
End If

Next j

Next i

'View the report
Viewer.ReportSource = crxRpt
Viewer.ViewReport



End Sub


No matter what I set strServerOrDSNName and strDBNameOrPath to, the report still connects to our test DB as defined in the .rpt file. In fact if I leave these two fields blank it still connects the the test DB with no problem. What am I doing wrong? Thanks.

 

[vidru]

I've never used JDBC/mysql to connect. Does the DSN have a default database property? With the above code, if the report should attempt to whatever database the DSN is pointing to.

-dave