Auto Change data source in one report

Hi,
I have one report that needs to connect to 15 databases and generate same
reports every day. Instead of creating 15 reports with 15 data sources, is
there a dynamic way to create one report only by automatically connecting to
various databases? Thanks.
ChuckYes. The key is that you can have T-SQL in the generic query designer. Here
is an example:
declare @.SQL varchar(255)
select @.SQL = 'select name as somename from ' + @.Database + '.dbo.sysobjects
where xtype = ''U'' order by name'
exec (@.SQL)
Note that @.Database is a query parameter that you need to map to a report
parameter. You might have to create the report parameter by hand. Put in the
above (as a test) and then click on ... and map the query parameter to the
report parameter.
Another way is to set the query source to an expression, again in the
generic query designer
= "select name as somename from " & Parameters!Database.value &
".dbo.sysobjects where xtype = 'U' order by name"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:DFF03ED4-84AE-4751-8058-216B02447EDA@.microsoft.com...
> Hi,
> I have one report that needs to connect to 15 databases and generate same
> reports every day. Instead of creating 15 reports with 15 data sources, is
> there a dynamic way to create one report only by automatically connecting
to
> various databases? Thanks.
>
> Chucksql