Change data source for multiple subreports in Crystal

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 ?
John-S PretoriusSystems EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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.
John-S PretoriusSystems EngineerAuthor 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 ConsultantCommented:
Does each subreport get a different data source?

How often does the report change?

Doesn't Ripplestone have that ability?
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

John-S PretoriusSystems EngineerAuthor 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 EngineerAuthor 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
Mike McCrackenSenior ConsultantCommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John-S PretoriusSystems EngineerAuthor 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.

Mike McCrackenSenior ConsultantCommented:
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

John-S PretoriusSystems EngineerAuthor 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)

John-S PretoriusSystems EngineerAuthor Commented:
Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.