Data Connections

What are the limitations of multiple data connections in Crystal Reports?  We are still under analysis phase and I don’t have the software to test out the feature. Please help me with this issue. I guess we can create multiple connections and link/join the tables on Database Expert Pane.  Any good with Business Views?
I can’t use Sub-Reports due to formatting issues. I have to and mix up the fields from different databases and show them in one crosstab or table like structure.   I also have to build some charts based on the data from multiple database tables. So, I think sub-reports are not a good option.

e.g.:  Here is some sample data that needs to be integrated from multiple databases.
Database1.Table1 has the following data:
.Customer.        .Year.      .Revenue.
--------------        -------      ------------
John                    2010            2000
Sally                    2010            3000


Database2.Table2 has the following data:

.Customer.        .Age.  
--------------      --------    
John                     22          
Sally                     28          


Simple report  should look like below:  

.Customer.       .Age.    .Year.        .Revenue.
--------------       ------     -------         ------------
John                      22         2010            2000
Sally                      28         2010            3000



Please help!
ShamanthAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

mlmccCommented:
Crystal doesn't support multiple data sources like that very well.

One method would be to choose one of the databases as the "master" and link to the other tables from there.

Another method wuld be to build a recordset in an application and pass the record set into the report.

mlmcc
0
ShamanthAuthor Commented:
I think we can join the tables from different databases on database expert and use them in the report..isn't it?  Also I think BVM has some options.
I am mostly concerned about joining different tables with different levels of aggregation.
0
mlmccCommented:
You may be able to join the tables in the linking tab.  There is an issue if you try running the reports through an application and have to change the data source.

Joining just sort of Merges the tables so that records with the same common value (link) are joined into a single record and treated as such in the report.

mlmcc
0

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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

ShamanthAuthor Commented:
We will be running the reports thru application. What are the issues?
Other than application issues, are there any other issues while joining the tables from multiple databases?
0
mlmccCommented:
The API only supports a single datasource.

mlmcc
0
ShamanthAuthor Commented:
any other issues with joins, performance etc..?
0
ShamanthAuthor Commented:
how does aggregations work?
0
mlmccCommented:
I have never used 2 data sources in a report so I don't know of other limitations

mlmcc
0
ShamanthAuthor Commented:
Can somebody please look into this issue?

mlmcc, I appreciate your help!!!  Your information is very helpful.

0
James0628Commented:
I've used multiple datasources a few times.  The general warning from CR says:

Please make sure that no SQL Expression is added and no server-side group-by is performed.


 A SQL Expression is basically a subquery in a formula.  "Server-side grouping" just means that the report uses an option to try to get the server to group the data before it's sent to CR.  My guess (and it's just a guess) would be that in both cases, CR just doesn't know where to send the subquery or grouping information, since there are multiple datasources.  But I could be wrong.  IAC, it presumably means that you can't use those options when using multiple datasources.  If you don't need those options, then that's not a problem.


 I've got one report that uses two MS SQL db's and the only issue that I can think of (in addition to the two above, which aren't a problem for that report) is that CR asks for a separate login for each db, because each login only has access to that one db and not any others.  There could be other issues that I'm not aware of.  All I can say is that it appeared to work fine, but it's also a special report that isn't run regularly.  And, for the record, the report is just run from CR 10, not from some other application, so I don't know what issues might be involved if it was run from another application.

 If you can, it would probably simplify things if you could use something like linked servers to make the tables in dbB available from dbA, so that the report would only need to connect to dbA.

 You mentioned "joining different tables with different levels of aggregation", but I don't think having the tables in different db's is really a factor there.  As far as that goes, it seems to me like you'd just have the same issues to deal with that you'd have with any two tables with different levels of detail.

 James
0
ShamanthAuthor Commented:
I appreciate your inputs. Thanks!
0
ShamanthAuthor Commented:
Thanks
0
James0628Commented:
You're welcome, and good luck.

 James
0
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
Windows Server 2003

From novice to tech pro — start learning today.