Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wolfgam
Starting Member
7 Posts |
 Posted - 2010-03-15 : 05:43:22
|
| Hello,i am trying to build a report based on multiple databases.Sample: need to have one list of employees based on 2 different databases.I created 2 data sources with datasets for employees.Data Source 1 - dataset1: select name, adress from employeesData Source 2 - dataset2: select name, adress from employeesI cannot find the solution how to put the data of 2 different datasets in one list!kindly regardsWolfgang |
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-03-15 : 06:01:48
|
quote:
Originally posted by wolfgam
Hello,i am trying to build a report based on multiple databases.Sample: need to have one list of employees based on 2 different databases.I created 2 data sources with datasets for employees.Data Source 1 - dataset1: select name, adress from employeesData Source 2 - dataset2: select name, adress from employeesI cannot find the solution how to put the data of 2 different datasets in one list!kindly regardsWolfgang
select name, adress from employees_datalist1union allselect name, adress from employees_datalist2 |
 |
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2010-03-15 : 09:17:23
|
| I am having the same problem!! Sad Microsoft! Try Crystal ReportsI did use union and union all, and each gives interestingly different results...Worst is, because the counts retrieved per date, we see differing counts, any number of either zero, negative or positive results. These amounts show up fine in the query, each grouped amount (a count) per date, but are kept seperate, with a different row per date.When I aggregate in the query on date:200902 0200902 -10000005566200902 29563545666200903 0200903 65656200903 -547and the graph then groups the entire set of numbers into one date! Can you see how bad that is? I thought a dataset I get, could be displayed on the graph like that - the way I want it! I want the zero result not shown. The +ve goes above the 0 line and the -ve below, for the same date period! How impossible is that? Can't the graph take a negative and positive dataset and show them like that? Tried that. Then the unioning. Not achievable. How would you do it? |
|
|
|
wolfgam
Starting Member
7 Posts |
Posted - 2010-03-15 : 10:05:55
|
| Sorry, my problem is not the SQL statement.Within BIDS in a report project can be defined several data sources with several data sets.In a single report (within a report project) i can only use one reference to a single data source.So there is no chance to use the union statement for 2 data sources?!May be i am blind, but cannot find how to do it... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-15 : 12:13:20
|
quote:
Originally posted by wolfgam
Sorry, my problem is not the SQL statement.Within BIDS in a report project can be defined several data sources with several data sets.In a single report (within a report project) i can only use one reference to a single data source.So there is no chance to use the union statement for 2 data sources?!May be i am blind, but cannot find how to do it...
why do you need 2 datasources if you use union? you can get all of them in same dataset and then use it in your report------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-15 : 12:15:16
|
quote:
Originally posted by mikebird
I am having the same problem!! Sad Microsoft! Try Crystal ReportsI did use union and union all, and each gives interestingly different results...Worst is, because the counts retrieved per date, we see differing counts, any number of either zero, negative or positive results. These amounts show up fine in the query, each grouped amount (a count) per date, but are kept seperate, with a different row per date.When I aggregate in the query on date:200902 0200902 -10000005566200902 29563545666200903 0200903 65656200903 -547and the graph then groups the entire set of numbers into one date! Can you see how bad that is? I thought a dataset I get, could be displayed on the graph like that - the way I want it! I want the zero result not shown. The +ve goes above the 0 line and the -ve below, for the same date period! How impossible is that? Can't the graph take a negative and positive dataset and show them like that? Tried that. Then the unioning. Not achievable. How would you do it?
thats obvious union and union all gives different resultset. union returns only distinct set of combination.can you explain with some data how you want output to be?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
wolfgam
Starting Member
7 Posts |
Posted - 2010-03-15 : 15:07:05
|
| Found the solution on SQL Server Developer Center, SQL Server Reporting Services FAQ (http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/48de91f9-1844-40c1-9614-5ead0b4b69a5#P1Q11)1) Define linked servers to the databases you need in SQL Server Management Studio (in this case 2 linked servers to 2 different databases)2) Define data source in your report as SQL Server3) refer to the desired tables with full qualified four part table name (linked_server_name.catalog.schema.object_name) E.g. select name, adress from Company1...Customers union all select name, Adress from Company2...Customers |
|
|
|
|
|
|
|
|
Report on multiple databases