Multiple Databases

Is possible to pull data from multiple databases into one report within
Reporting Services 2000? What about RS 2005?No problem at all. Just create a new data source for each database, and
create your datasets based on the different data sources. It might be a bit
more tricky to join data from different databases in the same dataset. But
then you can create a stored procedure in one of the databases, and create
your query just as you would normally do between databases, and reference
your stored procedure in the RS Report. Goes for both RS 2000 and 2005.
Kaisa
"David" <dilworth@.newsgroups.nospam> wrote in message
news:0049509F-B71D-4DEE-9984-1544D9F3E7A0@.microsoft.com...
> Is possible to pull data from multiple databases into one report within
> Reporting Services 2000? What about RS 2005?|||Also, read up on sub reports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David" <dilworth@.newsgroups.nospam> wrote in message
news:0049509F-B71D-4DEE-9984-1544D9F3E7A0@.microsoft.com...
> Is possible to pull data from multiple databases into one report within
> Reporting Services 2000? What about RS 2005?|||David,
The only limit that I know is that reporting services 2000 (Dont know
aboout 2005), is that you can't use 2 data sets for 1 table - so you
cant MERGE data into 1 table.
So I would recommend either creating a store procedure as suggested by
Kaisa that would get data from different databases.
If you want the data in two different tables, then YES you can use 2
tables, or a subreport as suggested by Bruce.
Regards,
Stas K.|||Still true in RS 2005.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sorcerdon" <sorcerdon@.gmail.com> wrote in message
news:1136556065.510433.278670@.g49g2000cwa.googlegroups.com...
> David,
> The only limit that I know is that reporting services 2000 (Dont know
> aboout 2005), is that you can't use 2 data sets for 1 table - so you
> cant MERGE data into 1 table.
> So I would recommend either creating a store procedure as suggested by
> Kaisa that would get data from different databases.
> If you want the data in two different tables, then YES you can use 2
> tables, or a subreport as suggested by Bruce.
> Regards,
> Stas K.
>|||Is it possible to collect data from multiple databases in a report model also?
I'd like to define a data source view made up of three data sources, and it
works well, but when it comes to generating the report model, it seems to me
that only the primary data source is availible for the report model. Is this
true? If so it's a real showstopper for me. The solutions I see for this
problem is:
1. Views to the other databases
2. Building a ssas cube to overcome the limitations
Please help me out!
"Bruce L-C [MVP]" wrote:
> Still true in RS 2005.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sorcerdon" <sorcerdon@.gmail.com> wrote in message
> news:1136556065.510433.278670@.g49g2000cwa.googlegroups.com...
> > David,
> >
> > The only limit that I know is that reporting services 2000 (Dont know
> > aboout 2005), is that you can't use 2 data sets for 1 table - so you
> > cant MERGE data into 1 table.
> > So I would recommend either creating a store procedure as suggested by
> > Kaisa that would get data from different databases.
> >
> > If you want the data in two different tables, then YES you can use 2
> > tables, or a subreport as suggested by Bruce.
> >
> > Regards,
> >
> > Stas K.
> >
>
>|||Sorry, I don't use Report Model so I am not aware of what can or cannot be
done. However, I doubt that it would allow something that is not possible
from with RS Designer.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Erik Hedlund" <ErikHedlund@.discussions.microsoft.com> wrote in message
news:7EDBEB3A-BAE8-431E-AB03-B866C2C179E2@.microsoft.com...
> Is it possible to collect data from multiple databases in a report model
> also?
> I'd like to define a data source view made up of three data sources, and
> it
> works well, but when it comes to generating the report model, it seems to
> me
> that only the primary data source is availible for the report model. Is
> this
> true? If so it's a real showstopper for me. The solutions I see for this
> problem is:
> 1. Views to the other databases
> 2. Building a ssas cube to overcome the limitations
> Please help me out!
> "Bruce L-C [MVP]" wrote:
>> Still true in RS 2005.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Sorcerdon" <sorcerdon@.gmail.com> wrote in message
>> news:1136556065.510433.278670@.g49g2000cwa.googlegroups.com...
>> > David,
>> >
>> > The only limit that I know is that reporting services 2000 (Dont know
>> > aboout 2005), is that you can't use 2 data sets for 1 table - so you
>> > cant MERGE data into 1 table.
>> > So I would recommend either creating a store procedure as suggested by
>> > Kaisa that would get data from different databases.
>> >
>> > If you want the data in two different tables, then YES you can use 2
>> > tables, or a subreport as suggested by Bruce.
>> >
>> > Regards,
>> >
>> > Stas K.
>> >
>>|||Given that report models are based off a data source I would lean towards
"no" without some additional work. Looks like a cube may be your best bet
for gathering data like that.
"Erik Hedlund" <ErikHedlund@.discussions.microsoft.com> wrote in message
news:7EDBEB3A-BAE8-431E-AB03-B866C2C179E2@.microsoft.com...
> Is it possible to collect data from multiple databases in a report model
> also?
> I'd like to define a data source view made up of three data sources, and
> it
> works well, but when it comes to generating the report model, it seems to
> me
> that only the primary data source is availible for the report model. Is
> this
> true? If so it's a real showstopper for me. The solutions I see for this
> problem is:
> 1. Views to the other databases
> 2. Building a ssas cube to overcome the limitations
> Please help me out!
> "Bruce L-C [MVP]" wrote:
>> Still true in RS 2005.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Sorcerdon" <sorcerdon@.gmail.com> wrote in message
>> news:1136556065.510433.278670@.g49g2000cwa.googlegroups.com...
>> > David,
>> >
>> > The only limit that I know is that reporting services 2000 (Dont know
>> > aboout 2005), is that you can't use 2 data sets for 1 table - so you
>> > cant MERGE data into 1 table.
>> > So I would recommend either creating a store procedure as suggested by
>> > Kaisa that would get data from different databases.
>> >
>> > If you want the data in two different tables, then YES you can use 2
>> > tables, or a subreport as suggested by Bruce.
>> >
>> > Regards,
>> >
>> > Stas K.
>> >
>>|||Well, the report model is based upon a data source view, which is an
abstractionlayer on top of the data source(s). I the data source view it is
no problem to use tables from many data sources, but when it comes to the
report model, it seems to me that only the primary data source in the data
soure view is "in reach". I think that this is rather odd. Why should the
report model be concerned about what lies bellow the abstraction layer?
"Chris Taylor" wrote:
> Given that report models are based off a data source I would lean towards
> "no" without some additional work. Looks like a cube may be your best bet
> for gathering data like that.
>
> "Erik Hedlund" <ErikHedlund@.discussions.microsoft.com> wrote in message
> news:7EDBEB3A-BAE8-431E-AB03-B866C2C179E2@.microsoft.com...
> > Is it possible to collect data from multiple databases in a report model
> > also?
> >
> > I'd like to define a data source view made up of three data sources, and
> > it
> > works well, but when it comes to generating the report model, it seems to
> > me
> > that only the primary data source is availible for the report model. Is
> > this
> > true? If so it's a real showstopper for me. The solutions I see for this
> > problem is:
> > 1. Views to the other databases
> > 2. Building a ssas cube to overcome the limitations
> >
> > Please help me out!
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Still true in RS 2005.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Sorcerdon" <sorcerdon@.gmail.com> wrote in message
> >> news:1136556065.510433.278670@.g49g2000cwa.googlegroups.com...
> >> > David,
> >> >
> >> > The only limit that I know is that reporting services 2000 (Dont know
> >> > aboout 2005), is that you can't use 2 data sets for 1 table - so you
> >> > cant MERGE data into 1 table.
> >> > So I would recommend either creating a store procedure as suggested by
> >> > Kaisa that would get data from different databases.
> >> >
> >> > If you want the data in two different tables, then YES you can use 2
> >> > tables, or a subreport as suggested by Bruce.
> >> >
> >> > Regards,
> >> >
> >> > Stas K.
> >> >
> >>
> >>
> >>
>
>