Hello,
I have a report specification with a simple grid with 4 indicators and an
dimension on rows
Indicator 1,2 and 3 are coming from a cube so i retrive the data with a MDX
query
The last indicator come from a SQL Server (2k5) database in another system.
Today we have a set a reports on the cube and a set of reports on the SQL
darabase.
Can i make this kind of reports with 2 datasources in Reporting services
(2k5)?
Regards,
NicolasMaybe. I haven't done this with a cube but I have done it with relational
data. The concern I have is less where the data is coming from but whether
or not you are using a table control or a matrix to show the data. If a
table control I know it will work.Create a sub report (a sub report is just
a regular report with a parameter you will use to tie the sub report to the
base report). Test the sub report (going agains the SQL data).
Create an extra cell in the row on the table grid. Drag and drop the sub
report onto the cell. Do a right mouse click on the sub report and map the
parameter to the appropriate field.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nicolas Lievain" <n.lievain@.bravosolution.fr> wrote in message
news:OvKBy0efIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a report specification with a simple grid with 4 indicators and an
> dimension on rows
> Indicator 1,2 and 3 are coming from a cube so i retrive the data with a
> MDX query
> The last indicator come from a SQL Server (2k5) database in another
> system.
> Today we have a set a reports on the cube and a set of reports on the SQL
> darabase.
> Can i make this kind of reports with 2 datasources in Reporting services
> (2k5)?
> Regards,
> Nicolas
>
Showing posts with label datasource. Show all posts
Showing posts with label datasource. Show all posts
Monday, March 19, 2012
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.
> >
> >
> >
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.
> >
> >
> >
Friday, March 9, 2012
Multiple Data Sources
Is there away to change the datasource at the time of deployment of a report? I know there is a production and debug deployment for specifying URLs and Folders. Is there anyway to specify the datasource, so you can have one for production and another for debug / test? Do you keep two datasources in the project? The production datasource already exists in the target environment.
Any help would be appreciated.
Thanks!
DotNetNow
Well:
We keep (as I think all people do) environments for test and production - so we test everything with RS and then deploy to the production server and just change (or point to the production environment
within the Project Properties or within the data sources within the Project itself such as:
Data Source=(For Example the Server Name);Initial Catalog=(For Example the data base name)
That means that when a developer is done doing what a developer does - the DataSource Name is changed to the QA group - all applications are deployed to the QA group -
The QA group does what they do and they have a production person change the Data Source parameters to the production environment.
That is one of the features I really like with RS is the deployment capabilities - it all relies on what you define within the data sources within the project or the properties of the project.
Hope this helps!
multiple connects to database
How does reporting services handle connection strings. There is a single report using a shared datasource. Testing this report seems to generate multiple connections to the database for a single user. The report being tested has 4 datasets (3 to obtain parameter value lists) using the same shared datasource but the number of connections for the single user were 20 in one instance. When does reporting services make the connection and when does it release it? It didnt seem to let these go when the report was done executing. Using RS2005 sp1 with DB2The Report Server processes the dataset queries in parallel whenever possible. This is why you see three connections open when you run a report with multiple datasets. Not sure why the number of connections jump to 20. Does the DB2 provider support connection pooling?
Subscribe to:
Posts (Atom)