Multiple Databases?

[pkav]

Hi,
I'm looking at implementing a SQL Server Reporting Solution to report on a set of databases (across a number of servers). The reports would need to be able to display information from a combination of these databases.

Am I right in thinking that the only way to create the result set for a cross database query is to use database linking at the query/stored procedure level and that the information can't be merged at the Reporting Service level?

thanks,

Kav.

 

[xlbo]

semi correct

You can have multiple data sets within a report and if you display data in textboxes, you can have data from different data sets next to each other.

You can also show data from different data sets in the same table but with the caveat that you have to reference the field within its data set and you must use an aggregate (otherwise you get compile errors) - there is no data set linking technique like there is in BI tools like Business Objects where you can link measures against common dimensions

My most complex report has data from 5 databases on 3 servers - some of the data is joined in stored procedures, others are viewed together in the report - you can do a lot in RS but there are situations where you just can't display the data you want together and have to join it in a query

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question