Replace a database connection for report and all subreports

Question

Is there is any way to change the datasource location for a report and all of it's subreports without having to open each of them manually?

Solution 1

Here is how I set my connections at runtime. I get the connection info from a config location.

        #'SET REPORT CONNECTION INFO
        For i = 0 To rsource.ReportDocument.DataSourceConnections.Count - 1
            rsource.ReportDocument.DataSourceConnections(i).SetConnection(crystalServer, crystalDB, crystalUser, crystalPassword)
        Next

        For i = 0 To rsource.ReportDocument.Subreports.Count - 1
            For x = 0 To rsource.ReportDocument.Subreports(i).DataSourceConnections.Count - 1
                rsource.ReportDocument.OpenSubreport(rsource.ReportDocument.Subreports(i).Name).DataSourceConnections(x).SetConnection(crystalServer, crystalDB, crystalUser, crystalPassword)
            Next
        Next

Solution 2

If you are just doing this as a one-shot deal, my suggestion might not help. But, if you change data sources frequently, it might be useful.

Disclaimer: I haven't worked with Crystal since version 9.0, so I don't know if they have improved on this. I always used UDL files. Basically, it is a pointer to a data source. Set up your report to point to the UDL, and the UDL points to the data source. If the source changes, just update the UDL.

This is incredibly useful if you have multiple reports. You only have to update one file when the server changes.

Solution 3

Linked sub-reports (at least in CR XI) share the main report's datasource - presumably your report is already configured so that's not an option for you?

Solution 4

@Unsliced I think the problem he is getting at is when you take a crystal report someone developed against another database, and you bring it up in Crystal Reports XI, you have to do a Change Datasource for each field, including those in subreports. If you just change source on the top level of the report, it often errors. (I think that is a known issue in Crystal Reports).

View more solutions

Comments

[Manuel Hoffmann]

I found this to be the most helpful for setting connections at runtime.