Multiple Starting points

How can I have multiple datasource in CR 9.

For ex. If I have tables linked as one datasource and if I define a SQL as another datasource, Then I get an error saying "More than one datasource is used....".

But I would like to have columns from both the datasource. They are different fields from different tables under different conditions.

Any inputs appreciated
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.

Crystal recommends using subreports when multiple datasources are needed.


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
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
There are definitely issues when you attempt to join tables from different databases - even if the type of database is the same (two SQL Server Databases on the same machine will still give you fits).  While you can do it, your options for joins and fields are extremely limited as you found out.

Subreports are definitely a viable option, but they might not be the most efficient.  The section in which the subreport is placed and the size and integrity of the tables used can definitely affect performace.  For example, if you place the subreport in the detail level of the master report and there are 5k detail lines in the report, then the subreport will be executed 5,000 times!  This can cause performance nightmares, both in Crystal and on the database.

Other options you have are:

1)  Link all of the tables in MS Access, then use Access as the sole datasource for the report
2)  Create a View in your database - the databases can be joined in the View, which will be used as the sole database for the report
3)  Create a Stored Procedure - the databases can be joined in the Procedure, which will be used as the sole database for the report
4)  Create a SQL Command Object with Crystal Reports 9 - the databases can be joined in the Command, which will be used as the sole database for the report

See some commonality between all of these solutions?  Different ways of accomplishing the same thing, but they can all be viable options.  The benefit to using any one of these solutions is that your performance is pretty much guaranteed to be better than if you use subreports.

Glad i could help

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.