Multiple Datasource as Parameter

This question has been asked numerous times, but I'm still stumped. I have
done several research and some people say it's impossible, while others say
it can be done. One I heard is the dataset extension. I'm still a little
confuse how this work since I'm quite new to working with Reports, especially
in Reporting Services. Hopefully someone can help shed some lights here.
Here's the situation. We have a couple of databases with same fields,
tables, and schema. Each database belongs to a department within the
company. I don't want to have to create each report for each different
database. What I wanted to do is be able to create a report that will be
able to use a parameter for the different databases. The parameter will be a
dynamic one, where the user can just select the database that they want and
then click view.
Can someone shed some lights here?
Thanks.Various approaches for dynamic database connections in RS 2000 have been
discussed on this newsgroup:
* If the databases are on the same server, use a dynamic query text (i.e.
="select * from " & Parameters!DatabaseName.Value & "..table")
* Use the linked server functionality of SQL Server; please check this
thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If you're just toggling between two or three databases, you can publish
the same report 3 times with 3 different names using 3 different data
sources and write a main report that shows/hides the correct subreport based
on whatever criteria you want.
* Use a custom data processing extension. For detailed dicussions see also:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=12f4a06c-57a5-42cf-8e7b-03e0b093129c&sloc=en-us
Native support (expression-based connection strings) is planned to be
included in the next version.
Hope this helps,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"chang" <chang@.discussions.microsoft.com> wrote in message
news:EAB6C460-3C29-4B55-9ABD-8CDFB79EC6BF@.microsoft.com...
> This question has been asked numerous times, but I'm still stumped. I
have
> done several research and some people say it's impossible, while others
say
> it can be done. One I heard is the dataset extension. I'm still a little
> confuse how this work since I'm quite new to working with Reports,
especially
> in Reporting Services. Hopefully someone can help shed some lights here.
> Here's the situation. We have a couple of databases with same fields,
> tables, and schema. Each database belongs to a department within the
> company. I don't want to have to create each report for each different
> database. What I wanted to do is be able to create a report that will be
> able to use a parameter for the different databases. The parameter will
be a
> dynamic one, where the user can just select the database that they want
and
> then click view.
> Can someone shed some lights here?
> Thanks.|||Hello Chang,
What you want to do is pretty easily done - without having to step into
creating custom data extensions...
Bill and I explain this in great detail in our book "Hitchhiker's Guide to
SQL Server 200 Reporting Services" p 298 - 306.
However you may be able to follow these brief instructions:
1) Execute a query in the DataSet Designer that will pull back the fields
that you want. - this should populate the Fields List.
2) Then replace the query string in the DataSet Designer with a Visual
Basic.NET expression (include the =) - for example:-
=iif(Parameters!Source.Value ="NorthWind", "SELECT * FROM
xpvs2003.NorthWind.dbo.sysobjects", "SELECT * FROM
xpvs2003.pubs.dbo.sysobjects")
3) Create for yourself the Report Parameter - in the above example this is
called "Source".
In my simple example here in VB.NET expression I'm just returning a Select
String which will obtain it's content from the sysobjects tables in either
the Pubs or NorthWind example Databases (which are on my xpvs2003 machine)
dependant upon the parameter.
If the databases are on different servers then what I'd do is include the
full schema name of the target including the servername in the FROM clause -
after having linked the SQL servers (see sp_linked servers in Books On Line)
This should get you started, - but much more comprehensive details and
explanation is in the book.
Best Wishes
Peter Blackburn
www.sqlreportingservices.net
"chang" <chang@.discussions.microsoft.com> wrote in message
news:EAB6C460-3C29-4B55-9ABD-8CDFB79EC6BF@.microsoft.com...
> This question has been asked numerous times, but I'm still stumped. I
> have
> done several research and some people say it's impossible, while others
> say
> it can be done. One I heard is the dataset extension. I'm still a little
> confuse how this work since I'm quite new to working with Reports,
> especially
> in Reporting Services. Hopefully someone can help shed some lights here.
> Here's the situation. We have a couple of databases with same fields,
> tables, and schema. Each database belongs to a department within the
> company. I don't want to have to create each report for each different
> database. What I wanted to do is be able to create a report that will be
> able to use a parameter for the different databases. The parameter will
> be a
> dynamic one, where the user can just select the database that they want
> and
> then click view.
> Can someone shed some lights here?
> Thanks.|||Thank you Peter and Robert. Those advice are extremely helpful.
Peter I have been following up on your website forum and I found a lot of
neat stuff that I can use for my reports. I'm in the process of getting my
boss to order me a copy of your book.
Regards,
Chang
"Peter Blackburn (www.sqlreportingservice" wrote:
> Hello Chang,
> What you want to do is pretty easily done - without having to step into
> creating custom data extensions...
> Bill and I explain this in great detail in our book "Hitchhiker's Guide to
> SQL Server 200 Reporting Services" p 298 - 306.
> However you may be able to follow these brief instructions:
> 1) Execute a query in the DataSet Designer that will pull back the fields
> that you want. - this should populate the Fields List.
> 2) Then replace the query string in the DataSet Designer with a Visual
> Basic.NET expression (include the =) - for example:-
> =iif(Parameters!Source.Value ="NorthWind", "SELECT * FROM
> xpvs2003.NorthWind.dbo.sysobjects", "SELECT * FROM
> xpvs2003.pubs.dbo.sysobjects")
> 3) Create for yourself the Report Parameter - in the above example this is
> called "Source".
> In my simple example here in VB.NET expression I'm just returning a Select
> String which will obtain it's content from the sysobjects tables in either
> the Pubs or NorthWind example Databases (which are on my xpvs2003 machine)
> dependant upon the parameter.
> If the databases are on different servers then what I'd do is include the
> full schema name of the target including the servername in the FROM clause -
> after having linked the SQL servers (see sp_linked servers in Books On Line)
> This should get you started, - but much more comprehensive details and
> explanation is in the book.
> Best Wishes
> Peter Blackburn
> www.sqlreportingservices.net
>
>
>
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:EAB6C460-3C29-4B55-9ABD-8CDFB79EC6BF@.microsoft.com...
> > This question has been asked numerous times, but I'm still stumped. I
> > have
> > done several research and some people say it's impossible, while others
> > say
> > it can be done. One I heard is the dataset extension. I'm still a little
> > confuse how this work since I'm quite new to working with Reports,
> > especially
> > in Reporting Services. Hopefully someone can help shed some lights here.
> >
> > Here's the situation. We have a couple of databases with same fields,
> > tables, and schema. Each database belongs to a department within the
> > company. I don't want to have to create each report for each different
> > database. What I wanted to do is be able to create a report that will be
> > able to use a parameter for the different databases. The parameter will
> > be a
> > dynamic one, where the user can just select the database that they want
> > and
> > then click view.
> >
> > Can someone shed some lights here?
> >
> > Thanks.
>
>|||Will Peter, my boss has just ordered me your book from Amazon.com. Can't
wait to get it and join your forum community.
Regards,
Chang
"chang" wrote:
> Thank you Peter and Robert. Those advice are extremely helpful.
> Peter I have been following up on your website forum and I found a lot of
> neat stuff that I can use for my reports. I'm in the process of getting my
> boss to order me a copy of your book.
> Regards,
> Chang
> "Peter Blackburn (www.sqlreportingservice" wrote:
> > Hello Chang,
> >
> > What you want to do is pretty easily done - without having to step into
> > creating custom data extensions...
> >
> > Bill and I explain this in great detail in our book "Hitchhiker's Guide to
> > SQL Server 200 Reporting Services" p 298 - 306.
> >
> > However you may be able to follow these brief instructions:
> > 1) Execute a query in the DataSet Designer that will pull back the fields
> > that you want. - this should populate the Fields List.
> > 2) Then replace the query string in the DataSet Designer with a Visual
> > Basic.NET expression (include the =) - for example:-
> >
> > =iif(Parameters!Source.Value ="NorthWind", "SELECT * FROM
> > xpvs2003.NorthWind.dbo.sysobjects", "SELECT * FROM
> > xpvs2003.pubs.dbo.sysobjects")
> >
> > 3) Create for yourself the Report Parameter - in the above example this is
> > called "Source".
> >
> > In my simple example here in VB.NET expression I'm just returning a Select
> > String which will obtain it's content from the sysobjects tables in either
> > the Pubs or NorthWind example Databases (which are on my xpvs2003 machine)
> > dependant upon the parameter.
> >
> > If the databases are on different servers then what I'd do is include the
> > full schema name of the target including the servername in the FROM clause -
> > after having linked the SQL servers (see sp_linked servers in Books On Line)
> >
> > This should get you started, - but much more comprehensive details and
> > explanation is in the book.
> >
> > Best Wishes
> >
> > Peter Blackburn
> > www.sqlreportingservices.net
> >
> >
> >
> >
> >
> >
> > "chang" <chang@.discussions.microsoft.com> wrote in message
> > news:EAB6C460-3C29-4B55-9ABD-8CDFB79EC6BF@.microsoft.com...
> > > This question has been asked numerous times, but I'm still stumped. I
> > > have
> > > done several research and some people say it's impossible, while others
> > > say
> > > it can be done. One I heard is the dataset extension. I'm still a little
> > > confuse how this work since I'm quite new to working with Reports,
> > > especially
> > > in Reporting Services. Hopefully someone can help shed some lights here.
> > >
> > > Here's the situation. We have a couple of databases with same fields,
> > > tables, and schema. Each database belongs to a department within the
> > > company. I don't want to have to create each report for each different
> > > database. What I wanted to do is be able to create a report that will be
> > > able to use a parameter for the different databases. The parameter will
> > > be a
> > > dynamic one, where the user can just select the database that they want
> > > and
> > > then click view.
> > >
> > > Can someone shed some lights here?
> > >
> > > Thanks.
> >
> >
> >|||Will Peter, my boss has just ordered your book for me from Amazon.com. Can't
wait to get the book.
Regards,
Chang
"chang" wrote:
> Thank you Peter and Robert. Those advice are extremely helpful.
> Peter I have been following up on your website forum and I found a lot of
> neat stuff that I can use for my reports. I'm in the process of getting my
> boss to order me a copy of your book.
> Regards,
> Chang
> "Peter Blackburn (www.sqlreportingservice" wrote:
> > Hello Chang,
> >
> > What you want to do is pretty easily done - without having to step into
> > creating custom data extensions...
> >
> > Bill and I explain this in great detail in our book "Hitchhiker's Guide to
> > SQL Server 200 Reporting Services" p 298 - 306.
> >
> > However you may be able to follow these brief instructions:
> > 1) Execute a query in the DataSet Designer that will pull back the fields
> > that you want. - this should populate the Fields List.
> > 2) Then replace the query string in the DataSet Designer with a Visual
> > Basic.NET expression (include the =) - for example:-
> >
> > =iif(Parameters!Source.Value ="NorthWind", "SELECT * FROM
> > xpvs2003.NorthWind.dbo.sysobjects", "SELECT * FROM
> > xpvs2003.pubs.dbo.sysobjects")
> >
> > 3) Create for yourself the Report Parameter - in the above example this is
> > called "Source".
> >
> > In my simple example here in VB.NET expression I'm just returning a Select
> > String which will obtain it's content from the sysobjects tables in either
> > the Pubs or NorthWind example Databases (which are on my xpvs2003 machine)
> > dependant upon the parameter.
> >
> > If the databases are on different servers then what I'd do is include the
> > full schema name of the target including the servername in the FROM clause -
> > after having linked the SQL servers (see sp_linked servers in Books On Line)
> >
> > This should get you started, - but much more comprehensive details and
> > explanation is in the book.
> >
> > Best Wishes
> >
> > Peter Blackburn
> > www.sqlreportingservices.net
> >
> >
> >
> >
> >
> >
> > "chang" <chang@.discussions.microsoft.com> wrote in message
> > news:EAB6C460-3C29-4B55-9ABD-8CDFB79EC6BF@.microsoft.com...
> > > This question has been asked numerous times, but I'm still stumped. I
> > > have
> > > done several research and some people say it's impossible, while others
> > > say
> > > it can be done. One I heard is the dataset extension. I'm still a little
> > > confuse how this work since I'm quite new to working with Reports,
> > > especially
> > > in Reporting Services. Hopefully someone can help shed some lights here.
> > >
> > > Here's the situation. We have a couple of databases with same fields,
> > > tables, and schema. Each database belongs to a department within the
> > > company. I don't want to have to create each report for each different
> > > database. What I wanted to do is be able to create a report that will be
> > > able to use a parameter for the different databases. The parameter will
> > > be a
> > > dynamic one, where the user can just select the database that they want
> > > and
> > > then click view.
> > >
> > > Can someone shed some lights here?
> > >
> > > Thanks.
> >
> >
> >