dynamic datasource

Our application is used by multiple companies. Each company has its own SQL
Server 2k database. Each company has access to the same list of reports in
SRS. We would like to deploy one physical copy of each report in the report
database for simplest maintenance of RDL files. Our application is deployed
in a web browser as an ASP application.
When a user in company A accesses Report 2 we need to render the report with
datasource parameters that will connect him to the correct db. At the same
time, users in companies B, C and D may be running the same report, each with
the correct datasource parameters for their companies.
Hence, as a report is requested by each user, we need to set up (or connect
to) the correct datasource parameters dynamically and then render the report
for the user.
Is there a way to do this with SRS? If multiple ways I would appreciate
knowing some of the options. If there are good books that discuss these
options I would appreciate references to them as well.
TIAHi joe,
We do something similar. We have multiple clients who use their own SQL
Database. We are using a custom data source (using ADO.net dataset). There
are numerous articles on the web which show you how to create one. Its pretty
simple and straightforward. The connection string is dynamically formed based
on the SQL Database name passed in as a parameter. This way we are able to
support multiple clients using a single copy of the rdl files.
Thanks,
George Tharakan
"Joe" wrote:
> Our application is used by multiple companies. Each company has its own SQL
> Server 2k database. Each company has access to the same list of reports in
> SRS. We would like to deploy one physical copy of each report in the report
> database for simplest maintenance of RDL files. Our application is deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report with
> datasource parameters that will connect him to the correct db. At the same
> time, users in companies B, C and D may be running the same report, each with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or connect
> to) the correct datasource parameters dynamically and then render the report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>|||Are you using the Report Manager or are you integrating with your own
application using either URL integration or Web services?
Assuming you are using Report Manager you can do the following:
1. have a report parameter that is based on the query. For the query use
pass the User!userid to a stored procedure (or use in a query) to query a
table and find out what database they should be using. Have this parameter
hidden so it can not be seen or modified by the user.
2. Using the generic query designer have the query use an expression like
this:
= "Select * from " & Parameters!DBName & ".dbo.mytable"
I usually test all this out with a report that has a single text box set to
this expression so I can see the query string and be sure it is correct
before I set a dataset source to it.
But, one issue you will have (with both this and the other solution on
creating your own extension) is that the UserID global parameter is not
usable from subscriptions. Really any solution this will be a problem
because you have to set the subcription to be for someone or it is run as
someone.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <dexter@.newco.com> wrote in message
news:28360A4A-BB83-4B5B-B55E-83A9F3B378E9@.microsoft.com...
> Our application is used by multiple companies. Each company has its own
> SQL
> Server 2k database. Each company has access to the same list of reports
> in
> SRS. We would like to deploy one physical copy of each report in the
> report
> database for simplest maintenance of RDL files. Our application is
> deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report
> with
> datasource parameters that will connect him to the correct db. At the
> same
> time, users in companies B, C and D may be running the same report, each
> with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or
> connect
> to) the correct datasource parameters dynamically and then render the
> report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>|||Thanks very much for both of your responses.
I have been attempting to build an extension. It's hard to tell how far I
am from success because I don't know how to debug the code. Can you offer
advice on this?
I'm using the VS.NET Report Designer to build my reports. I can see the new
extension there, but cannot get a connection to work.
FYI - I'm using the ReportViewer control to embed the reports in my web app.|||Sorry, I have not done an extension. I suggest posting a new subject with
your questions for that so people that have done extensions know that is
what the question is about and can jump in and help you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <dexter@.newco.com> wrote in message
news:3537E448-CE86-4E68-87C7-C8719A62112B@.microsoft.com...
> Thanks very much for both of your responses.
> I have been attempting to build an extension. It's hard to tell how far I
> am from success because I don't know how to debug the code. Can you offer
> advice on this?
> I'm using the VS.NET Report Designer to build my reports. I can see the
> new
> extension there, but cannot get a connection to work.
> FYI - I'm using the ReportViewer control to embed the reports in my web
> app.|||We have struggled with the same issue.
We were able to implement the @.server parameter and use that parameter to
dynamically choose the sql server at runtime.
Have fun trying to figure out the authentication nightmare you will encounter.
"Joe" wrote:
> Our application is used by multiple companies. Each company has its own SQL
> Server 2k database. Each company has access to the same list of reports in
> SRS. We would like to deploy one physical copy of each report in the report
> database for simplest maintenance of RDL files. Our application is deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report with
> datasource parameters that will connect him to the correct db. At the same
> time, users in companies B, C and D may be running the same report, each with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or connect
> to) the correct datasource parameters dynamically and then render the report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>