How To Dynamically Switch Between Databases In Report

Let me preface this by stating that I am a Reporting Services newbie...
I need to design a report that displays financial data for 2 companies,
Company A and Company B. The problem is that each company has their own
distinct database. The schema is identical between them, just different data
in each.
The report is identical for each company, the only different is that the
report needs to pull from Database A to display the report data for Company
A, and alternatively pull from Database B to display the report data for
Company B. The user wants to be able to specify whether they want to run the
report for Company A, or Company B.
How can I design the report or setup the data source to switch databases
depending on if the user wants to see the report for Company A or Company B?
Can this be done with a report parameter? If so, how?
I really don't want to create and maintain 2 identical reports, the only
difference being the data source.
One possible option I guess would be to create a view that combines data
from identical tables in both databases, and use that view as the datasource
in the report.
Just looking for what others have done in similar circumstances so I don't
spend multiple days architecting the wrong approach.
Thanks!Here is one way. It is an interesting technique in that it uses the ability
to run a batch of SQL statement. Use the generic query designer. Then paste
in the following (as an example).
declare @.SQL varchar(255)
select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where xtype
= ''U'' order by name'
exec (@.SQL)
You can also use an expression but one thing that is nice about the above
method is it will still fill in the field names (sometimes you have to click
on the refresh fields button but it all works). In your case you would have
the @.Database parameter be based on a list they choose from CompanyA,
CompanyB where the value for the selection would be the database name.
To do what you want will require a little more messing around. You could
first develop against one database to make sure you have the query correct
and then change it to be dynamic.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Smit-Dog" <SmitDog@.discussions.microsoft.com> wrote in message
news:43D571CF-ACF4-4BCE-B482-9F49E5817E8C@.microsoft.com...
> Let me preface this by stating that I am a Reporting Services newbie...
> I need to design a report that displays financial data for 2 companies,
> Company A and Company B. The problem is that each company has their own
> distinct database. The schema is identical between them, just different
data
> in each.
> The report is identical for each company, the only different is that the
> report needs to pull from Database A to display the report data for
Company
> A, and alternatively pull from Database B to display the report data for
> Company B. The user wants to be able to specify whether they want to run
the
> report for Company A, or Company B.
> How can I design the report or setup the data source to switch databases
> depending on if the user wants to see the report for Company A or Company
B?
> Can this be done with a report parameter? If so, how?
> I really don't want to create and maintain 2 identical reports, the only
> difference being the data source.
> One possible option I guess would be to create a view that combines data
> from identical tables in both databases, and use that view as the
datasource
> in the report.
> Just looking for what others have done in similar circumstances so I don't
> spend multiple days architecting the wrong approach.
> Thanks!|||Thanks Bruce... Looks like 1 of many possible approaches to this problem.
I just found out that it is likely that the customer will be adding more
companies, hence addtional databases that the report needs to be run against.
I guess I need to go figure out how to setup and pass parameters to the
report to allow the end-user to specify the "root" database name of the
company at runtime.
"Bruce L-C [MVP]" wrote:
> Here is one way. It is an interesting technique in that it uses the ability
> to run a batch of SQL statement. Use the generic query designer. Then paste
> in the following (as an example).
> declare @.SQL varchar(255)
> select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where xtype
> = ''U'' order by name'
> exec (@.SQL)
> You can also use an expression but one thing that is nice about the above
> method is it will still fill in the field names (sometimes you have to click
> on the refresh fields button but it all works). In your case you would have
> the @.Database parameter be based on a list they choose from CompanyA,
> CompanyB where the value for the selection would be the database name.
> To do what you want will require a little more messing around. You could
> first develop against one database to make sure you have the query correct
> and then change it to be dynamic.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Smit-Dog" <SmitDog@.discussions.microsoft.com> wrote in message
> news:43D571CF-ACF4-4BCE-B482-9F49E5817E8C@.microsoft.com...
> > Let me preface this by stating that I am a Reporting Services newbie...
> >
> > I need to design a report that displays financial data for 2 companies,
> > Company A and Company B. The problem is that each company has their own
> > distinct database. The schema is identical between them, just different
> data
> > in each.
> >
> > The report is identical for each company, the only different is that the
> > report needs to pull from Database A to display the report data for
> Company
> > A, and alternatively pull from Database B to display the report data for
> > Company B. The user wants to be able to specify whether they want to run
> the
> > report for Company A, or Company B.
> >
> > How can I design the report or setup the data source to switch databases
> > depending on if the user wants to see the report for Company A or Company
> B?
> > Can this be done with a report parameter? If so, how?
> >
> > I really don't want to create and maintain 2 identical reports, the only
> > difference being the data source.
> >
> > One possible option I guess would be to create a view that combines data
> > from identical tables in both databases, and use that view as the
> datasource
> > in the report.
> >
> > Just looking for what others have done in similar circumstances so I don't
> > spend multiple days architecting the wrong approach.
> >
> > Thanks!
>
>|||Hi,
What i'm currently doing is creating a reporting database that will include
all my reports stored procedure and have those stored procedure accessing my
OLTP data through a linked server. This way, if i need to install my
reporting solution at a new customer site, I only need to modify my linked
server parameters.
Hope this helps,
Eric
"Smit-Dog" wrote:
> Thanks Bruce... Looks like 1 of many possible approaches to this problem.
> I just found out that it is likely that the customer will be adding more
> companies, hence addtional databases that the report needs to be run against.
> I guess I need to go figure out how to setup and pass parameters to the
> report to allow the end-user to specify the "root" database name of the
> company at runtime.
> "Bruce L-C [MVP]" wrote:
> > Here is one way. It is an interesting technique in that it uses the ability
> > to run a batch of SQL statement. Use the generic query designer. Then paste
> > in the following (as an example).
> > declare @.SQL varchar(255)
> > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where xtype
> > = ''U'' order by name'
> > exec (@.SQL)
> >
> > You can also use an expression but one thing that is nice about the above
> > method is it will still fill in the field names (sometimes you have to click
> > on the refresh fields button but it all works). In your case you would have
> > the @.Database parameter be based on a list they choose from CompanyA,
> > CompanyB where the value for the selection would be the database name.
> >
> > To do what you want will require a little more messing around. You could
> > first develop against one database to make sure you have the query correct
> > and then change it to be dynamic.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Smit-Dog" <SmitDog@.discussions.microsoft.com> wrote in message
> > news:43D571CF-ACF4-4BCE-B482-9F49E5817E8C@.microsoft.com...
> > > Let me preface this by stating that I am a Reporting Services newbie...
> > >
> > > I need to design a report that displays financial data for 2 companies,
> > > Company A and Company B. The problem is that each company has their own
> > > distinct database. The schema is identical between them, just different
> > data
> > > in each.
> > >
> > > The report is identical for each company, the only different is that the
> > > report needs to pull from Database A to display the report data for
> > Company
> > > A, and alternatively pull from Database B to display the report data for
> > > Company B. The user wants to be able to specify whether they want to run
> > the
> > > report for Company A, or Company B.
> > >
> > > How can I design the report or setup the data source to switch databases
> > > depending on if the user wants to see the report for Company A or Company
> > B?
> > > Can this be done with a report parameter? If so, how?
> > >
> > > I really don't want to create and maintain 2 identical reports, the only
> > > difference being the data source.
> > >
> > > One possible option I guess would be to create a view that combines data
> > > from identical tables in both databases, and use that view as the
> > datasource
> > > in the report.
> > >
> > > Just looking for what others have done in similar circumstances so I don't
> > > spend multiple days architecting the wrong approach.
> > >
> > > Thanks!
> >
> >
> >|||In this case he has two databases that the customer wants to pick which one
to report off of.
Also, I would be very very careful with linked servers, especially if you
are using the four part naming. You could easily get burned. It takes very
little for SQL Server to decide to bring the whole table over and process it
locally. It is not doing a passthrough query. It seems like it would just
send the SQL to the remote server for processing but that is not what
happens with 4 part naming. This is what happens with OpenQuery but if you
are using 4 part naming then you could easily find yourself with a major
performance headache when you roll out to production.
Just a heads up on the dangers of linked servers.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aiwa" <Aiwa@.discussions.microsoft.com> wrote in message
news:2C43C2F9-BC10-4951-87FD-428CDF9D1D69@.microsoft.com...
> Hi,
> What i'm currently doing is creating a reporting database that will
include
> all my reports stored procedure and have those stored procedure accessing
my
> OLTP data through a linked server. This way, if i need to install my
> reporting solution at a new customer site, I only need to modify my linked
> server parameters.
> Hope this helps,
> Eric
> "Smit-Dog" wrote:
> > Thanks Bruce... Looks like 1 of many possible approaches to this
problem.
> >
> > I just found out that it is likely that the customer will be adding more
> > companies, hence addtional databases that the report needs to be run
against.
> >
> > I guess I need to go figure out how to setup and pass parameters to the
> > report to allow the end-user to specify the "root" database name of the
> > company at runtime.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Here is one way. It is an interesting technique in that it uses the
ability
> > > to run a batch of SQL statement. Use the generic query designer. Then
paste
> > > in the following (as an example).
> > > declare @.SQL varchar(255)
> > > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where
xtype
> > > = ''U'' order by name'
> > > exec (@.SQL)
> > >
> > > You can also use an expression but one thing that is nice about the
above
> > > method is it will still fill in the field names (sometimes you have to
click
> > > on the refresh fields button but it all works). In your case you would
have
> > > the @.Database parameter be based on a list they choose from CompanyA,
> > > CompanyB where the value for the selection would be the database name.
> > >
> > > To do what you want will require a little more messing around. You
could
> > > first develop against one database to make sure you have the query
correct
> > > and then change it to be dynamic.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "Smit-Dog" <SmitDog@.discussions.microsoft.com> wrote in message
> > > news:43D571CF-ACF4-4BCE-B482-9F49E5817E8C@.microsoft.com...
> > > > Let me preface this by stating that I am a Reporting Services
newbie...
> > > >
> > > > I need to design a report that displays financial data for 2
companies,
> > > > Company A and Company B. The problem is that each company has their
own
> > > > distinct database. The schema is identical between them, just
different
> > > data
> > > > in each.
> > > >
> > > > The report is identical for each company, the only different is that
the
> > > > report needs to pull from Database A to display the report data for
> > > Company
> > > > A, and alternatively pull from Database B to display the report data
for
> > > > Company B. The user wants to be able to specify whether they want to
run
> > > the
> > > > report for Company A, or Company B.
> > > >
> > > > How can I design the report or setup the data source to switch
databases
> > > > depending on if the user wants to see the report for Company A or
Company
> > > B?
> > > > Can this be done with a report parameter? If so, how?
> > > >
> > > > I really don't want to create and maintain 2 identical reports, the
only
> > > > difference being the data source.
> > > >
> > > > One possible option I guess would be to create a view that combines
data
> > > > from identical tables in both databases, and use that view as the
> > > datasource
> > > > in the report.
> > > >
> > > > Just looking for what others have done in similar circumstances so I
don't
> > > > spend multiple days architecting the wrong approach.
> > > >
> > > > Thanks!
> > >
> > >
> > >|||Hi Bruce,
Thanks for the heads up.
I'm actually using OpenQuery since it looks like the only way to abstract
the database name when using a linked server.
Is there a better way to do this ?
Thanks,
Eric
"Bruce L-C [MVP]" wrote:
> In this case he has two databases that the customer wants to pick which one
> to report off of.
> Also, I would be very very careful with linked servers, especially if you
> are using the four part naming. You could easily get burned. It takes very
> little for SQL Server to decide to bring the whole table over and process it
> locally. It is not doing a passthrough query. It seems like it would just
> send the SQL to the remote server for processing but that is not what
> happens with 4 part naming. This is what happens with OpenQuery but if you
> are using 4 part naming then you could easily find yourself with a major
> performance headache when you roll out to production.
> Just a heads up on the dangers of linked servers.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aiwa" <Aiwa@.discussions.microsoft.com> wrote in message
> news:2C43C2F9-BC10-4951-87FD-428CDF9D1D69@.microsoft.com...
> > Hi,
> >
> > What i'm currently doing is creating a reporting database that will
> include
> > all my reports stored procedure and have those stored procedure accessing
> my
> > OLTP data through a linked server. This way, if i need to install my
> > reporting solution at a new customer site, I only need to modify my linked
> > server parameters.
> >
> > Hope this helps,
> > Eric
> >
> > "Smit-Dog" wrote:
> >
> > > Thanks Bruce... Looks like 1 of many possible approaches to this
> problem.
> > >
> > > I just found out that it is likely that the customer will be adding more
> > > companies, hence addtional databases that the report needs to be run
> against.
> > >
> > > I guess I need to go figure out how to setup and pass parameters to the
> > > report to allow the end-user to specify the "root" database name of the
> > > company at runtime.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Here is one way. It is an interesting technique in that it uses the
> ability
> > > > to run a batch of SQL statement. Use the generic query designer. Then
> paste
> > > > in the following (as an example).
> > > > declare @.SQL varchar(255)
> > > > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where
> xtype
> > > > = ''U'' order by name'
> > > > exec (@.SQL)
> > > >
> > > > You can also use an expression but one thing that is nice about the
> above
> > > > method is it will still fill in the field names (sometimes you have to
> click
> > > > on the refresh fields button but it all works). In your case you would
> have
> > > > the @.Database parameter be based on a list they choose from CompanyA,
> > > > CompanyB where the value for the selection would be the database name.
> > > >
> > > > To do what you want will require a little more messing around. You
> could
> > > > first develop against one database to make sure you have the query
> correct
> > > > and then change it to be dynamic.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > >
> > > > "Smit-Dog" <SmitDog@.discussions.microsoft.com> wrote in message
> > > > news:43D571CF-ACF4-4BCE-B482-9F49E5817E8C@.microsoft.com...
> > > > > Let me preface this by stating that I am a Reporting Services
> newbie...
> > > > >
> > > > > I need to design a report that displays financial data for 2
> companies,
> > > > > Company A and Company B. The problem is that each company has their
> own
> > > > > distinct database. The schema is identical between them, just
> different
> > > > data
> > > > > in each.
> > > > >
> > > > > The report is identical for each company, the only different is that
> the
> > > > > report needs to pull from Database A to display the report data for
> > > > Company
> > > > > A, and alternatively pull from Database B to display the report data
> for
> > > > > Company B. The user wants to be able to specify whether they want to
> run
> > > > the
> > > > > report for Company A, or Company B.
> > > > >
> > > > > How can I design the report or setup the data source to switch
> databases
> > > > > depending on if the user wants to see the report for Company A or
> Company
> > > > B?
> > > > > Can this be done with a report parameter? If so, how?
> > > > >
> > > > > I really don't want to create and maintain 2 identical reports, the
> only
> > > > > difference being the data source.
> > > > >
> > > > > One possible option I guess would be to create a view that combines
> data
> > > > > from identical tables in both databases, and use that view as the
> > > > datasource
> > > > > in the report.
> > > > >
> > > > > Just looking for what others have done in similar circumstances so I
> don't
> > > > > spend multiple days architecting the wrong approach.
> > > > >
> > > > > Thanks!
> > > >
> > > >
> > > >
>
>|||declare @.SQL varchar(255)
select @.SQL = 'select max(somefield) from ' + @.Server +
'.database.dbo.tablename'
exec (@.SQL)
Four part naming works but it is dangerous. You are better off to use
OpenQuery as you are. Yukon handles 4 part naming better than it does now.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aiwa" <Aiwa@.discussions.microsoft.com> wrote in message
news:D906328F-8E54-425D-8E29-7C3636EEFDFD@.microsoft.com...
> Hi Bruce,
> Thanks for the heads up.
> I'm actually using OpenQuery since it looks like the only way to abstract
> the database name when using a linked server.
> Is there a better way to do this ?
> Thanks,
> Eric
> "Bruce L-C [MVP]" wrote:
> > In this case he has two databases that the customer wants to pick which
one
> > to report off of.
> >
> > Also, I would be very very careful with linked servers, especially if
you
> > are using the four part naming. You could easily get burned. It takes
very
> > little for SQL Server to decide to bring the whole table over and
process it
> > locally. It is not doing a passthrough query. It seems like it would
just
> > send the SQL to the remote server for processing but that is not what
> > happens with 4 part naming. This is what happens with OpenQuery but if
you
> > are using 4 part naming then you could easily find yourself with a major
> > performance headache when you roll out to production.
> >
> > Just a heads up on the dangers of linked servers.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Aiwa" <Aiwa@.discussions.microsoft.com> wrote in message
> > news:2C43C2F9-BC10-4951-87FD-428CDF9D1D69@.microsoft.com...
> > > Hi,
> > >
> > > What i'm currently doing is creating a reporting database that will
> > include
> > > all my reports stored procedure and have those stored procedure
accessing
> > my
> > > OLTP data through a linked server. This way, if i need to install my
> > > reporting solution at a new customer site, I only need to modify my
linked
> > > server parameters.
> > >
> > > Hope this helps,
> > > Eric
> > >
> > > "Smit-Dog" wrote:
> > >
> > > > Thanks Bruce... Looks like 1 of many possible approaches to this
> > problem.
> > > >
> > > > I just found out that it is likely that the customer will be adding
more
> > > > companies, hence addtional databases that the report needs to be run
> > against.
> > > >
> > > > I guess I need to go figure out how to setup and pass parameters to
the
> > > > report to allow the end-user to specify the "root" database name of
the
> > > > company at runtime.
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > Here is one way. It is an interesting technique in that it uses
the
> > ability
> > > > > to run a batch of SQL statement. Use the generic query designer.
Then
> > paste
> > > > > in the following (as an example).
> > > > > declare @.SQL varchar(255)
> > > > > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects
where
> > xtype
> > > > > = ''U'' order by name'
> > > > > exec (@.SQL)
> > > > >
> > > > > You can also use an expression but one thing that is nice about
the
> > above
> > > > > method is it will still fill in the field names (sometimes you
have to
> > click
> > > > > on the refresh fields button but it all works). In your case you
would
> > have
> > > > > the @.Database parameter be based on a list they choose from
CompanyA,
> > > > > CompanyB where the value for the selection would be the database
name.
> > > > >
> > > > > To do what you want will require a little more messing around. You
> > could
> > > > > first develop against one database to make sure you have the query
> > correct
> > > > > and then change it to be dynamic.
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > >
> > > > > "Smit-Dog" <SmitDog@.discussions.microsoft.com> wrote in message
> > > > > news:43D571CF-ACF4-4BCE-B482-9F49E5817E8C@.microsoft.com...
> > > > > > Let me preface this by stating that I am a Reporting Services
> > newbie...
> > > > > >
> > > > > > I need to design a report that displays financial data for 2
> > companies,
> > > > > > Company A and Company B. The problem is that each company has
their
> > own
> > > > > > distinct database. The schema is identical between them, just
> > different
> > > > > data
> > > > > > in each.
> > > > > >
> > > > > > The report is identical for each company, the only different is
that
> > the
> > > > > > report needs to pull from Database A to display the report data
for
> > > > > Company
> > > > > > A, and alternatively pull from Database B to display the report
data
> > for
> > > > > > Company B. The user wants to be able to specify whether they
want to
> > run
> > > > > the
> > > > > > report for Company A, or Company B.
> > > > > >
> > > > > > How can I design the report or setup the data source to switch
> > databases
> > > > > > depending on if the user wants to see the report for Company A
or
> > Company
> > > > > B?
> > > > > > Can this be done with a report parameter? If so, how?
> > > > > >
> > > > > > I really don't want to create and maintain 2 identical reports,
the
> > only
> > > > > > difference being the data source.
> > > > > >
> > > > > > One possible option I guess would be to create a view that
combines
> > data
> > > > > > from identical tables in both databases, and use that view as
the
> > > > > datasource
> > > > > > in the report.
> > > > > >
> > > > > > Just looking for what others have done in similar circumstances
so I
> > don't
> > > > > > spend multiple days architecting the wrong approach.
> > > > > >
> > > > > > Thanks!
> > > > >
> > > > >
> > > > >
> >
> >
> >