• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

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!
0
Shamanth
Asked:
Shamanth
  • 7
  • 4
  • 2
2 Solutions
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now