Change data source for multiple subreports in Crystal

John-S Pretorius
John-S Pretorius used Ask the Experts™
on
I have a Crystal Report (2016) with multiple sub reports, I'm distributing the same report to multiple ODBC data sources (25). Is there an easier way to change the ODBC data source for each sub report rather than having to manually change each sub report ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet Software

Commented:
Sounds like you intend to create 25 versions of the same report.
Are you aware that there are 3rd-party Crystal Reports viewers/schedulers that allow you to switch the ODBC DSN on the fly?
This means that interactively, the user can select from a list of alternative DSNs to use. Or via a setting/argument you can direct the process to use a different DSN.
See: http://kenhamady.com/productlinks/default.html
John-S PretoriusSystems Engineer

Author

Commented:
Thank you Ido, I put a request in with Report Tag earlier, still waiting for a 14 day trial download link. I don't need to schedule as I'm already using Ripplestone. I'm trying to make the tedious manual task changing the DSN for each sub-report for each site that will utilize the same report.

I have 25 physical DSN locations and another 25 local archived database DSN's used as a database warehouse.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Does each subreport get a different data source?

How often does the report change?

Doesn't Ripplestone have that ability?
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

John-S PretoriusSystems Engineer

Author

Commented:
Each sub report gets the same data source, just different to connect to each new site.

The report's data source changes for every new site (Hub and spoke, central database warehouse)

after you configure the data source you will configure the same dsn data source in Ripplestone (these are already configured and working)

My tedious challenge is updating the report to each new location as I have to physically open each  sub-report and manually change the data source connection.
John-S PretoriusSystems Engineer

Author

Commented:
I wanted to share more details:-

I attached the actual report. Also see below DNS sourcelist when I change a report location, as mentioned I have to do this for each subreport as the main report does not have an actual datasource:-

ODBC Source selection for each sub report
Below image is all the sub reports that has to be updated manually:-

Below are all the subreports that needs to be updated to the same DNS source
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Have you tried giving the main report the same data source?

I believe when you update that one it will update the sources for the susbreport.

mlmcc
John-S PretoriusSystems Engineer

Author

Commented:
I don't like using sub reports and have tried adding a data source to the main report, with all the current sub reports in place as is the processing goes of the wheels when I add data source to the main report. I will play around with adding a data source to the main report.

Thanks.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Just add the data source.  No need to link it tot he subreports or to have a query for the main report.

If it is the same source as the subreports I believe changing it will also change the subreports

mlmcc
John-S PretoriusSystems Engineer

Author

Commented:
I was able to get thru this by adding a source to the main report and changing the source to another DNS connection. After all the sub-reports synced I was able to get the report to process without errors. I was also able to create the same report for all sites (DNS connections)

Thanks.
John-S PretoriusSystems Engineer

Author

Commented:
Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial