generating a report from multiple datasources

Question

generating a report from multiple datasources

Nomvula

Hall of Fame

Points: 3778
More actions
June 5, 2009 at 3:53 am

#205504

hi experts
i need to create one single management report and the data i'm calling from more that 10 databases sitting in one server, how do i go about creating multiple datasources in reporting services

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

a lot of that depends on how you'll be using the data. If you're using data from various datasets in separate objects, just add another dataset, point it to a separate datasource and you should be all set. If however you are looking to merge data from the various databases into a common table/matrix/list, you may find it's easier to do this in a view or stored procedure and just return the required data in a single dataset. You could use a combination of the two techniques if that best fits your scenario as well.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Nomvula Hall of Fame Points: 3778 More actions · Answer 2

thanks for the response

data is coming from different databases and the tables i'm calling are all the same in the DB but with different data ofcause, i'll have to create datasource for each of the DB and each database i call them client since they are our client db and when displaying on the report i'll have to group them per each client/db e.g.

ClientName Field1 Field2 Field3 Total

Client1 5 2 1 8

Client2 3 0 1 4

Client3 1 3 5 9

so after creating my datasources how do i link them together to produce data as above

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

I would create a view in one of your databases and use that to consolidate all of your data into one logical place, then query that view from your reporting server. That way you only need 1 datasource pointing to the database with the view, and you create your report as you normally would.

Your view definition could be something like this...

CREATE VIEW test_View

AS

SELECT 'client1' AS ClientNAme,

Col1,

Col2,

Col3

FROM db1.dbo.myTable t1

UNION ALL

SELECT 'client2' AS ClientNAme,

Col1,

Col2,

Col3

FROM db2.dbo.myTable t2

Then you would just query it and do the totals either in your query or in your report, whichever works best for your data.

-Luke.

Edit: corrected query

To help us help you read this[/url]For better help with performance problems please read this[/url]

Nomvula Hall of Fame Points: 3778 More actions · Answer 4

thanks Luke

i'm going to try that and i think it's gonna work.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

Glad to have helped, I did just take a second look at my SQL syntax and realized I never specified more than 1 database, but hopefully it got you moving in the right direction anyway.

Let us know how you get it worked out.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]