Replace a database connection for report and all s

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?

Answer 1

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.

Answer 2

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?

Answer 3

@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).

Answer 4

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

Answer 5

I'm guessing you're talking about .rdl files from Reporting Services? (If not, my answer might be wrong)

They're basically just XML, so you could load each one of them in and do an XPath query to get the node that contains the datasource and update it.