Dynamic Connectionstring and Email delivery

Hi,
Im new to reporting services.
My requirement is to develop only one set of reports and to pass the
connectionstring dynamically based on the user logs in.I used the
setdatasourcecontents API as it overwrites the connectionstring and Im
passing it just before the retrieval of the reports.Can anyone suggest me
whether it is the best way or is there any other way to acheive this.
Also the user will have an option of subscribing to a report and he can
specify a scheduled time for that.As the report runs just before the delivery
and Im Overwriting the datasource the mail will be sent with wrong report
data.So,please help me in solving this.
Im using standard edition.
Thanks..What are you trying to accomplish with this? Is it a matter of several
databases on the same server and based on the user you are allowing them to
see data from that database?
What you are doing is very problematic (as you have seen) but there might be
some other solution for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GK" <GK@.discussions.microsoft.com> wrote in message
news:45299590-B2F5-4FEA-9544-9F92EE2EF028@.microsoft.com...
> Hi,
> Im new to reporting services.
> My requirement is to develop only one set of reports and to pass the
> connectionstring dynamically based on the user logs in.I used the
> setdatasourcecontents API as it overwrites the connectionstring and Im
> passing it just before the retrieval of the reports.Can anyone suggest me
> whether it is the best way or is there any other way to acheive this.
> Also the user will have an option of subscribing to a report and he can
> specify a scheduled time for that.As the report runs just before the
> delivery
> and Im Overwriting the datasource the mail will be sent with wrong report
> data.So,please help me in solving this.
>
> Im using standard edition.
> Thanks..
>|||Hello Bruce,
Thanks for your relply,I have seen some articles and custom data extension
might be a solution for this,and I don't know how to implement this and
whether it solves our email issue.
Definitely "Setdatasourcecontents" is not a solution as it applies changes
globally.
Our client have different companies wherein the database structure is
same.So I can create the reports for one company and pass the connection
string based on the employee login,so that he can view the reports specific
to his company.
Also when an employee subscribes for a report,how to pass the connection
string at the time of delivery of the report so that he receives his
company's report data.
"Bruce L-C [MVP]" wrote:
> What are you trying to accomplish with this? Is it a matter of several
> databases on the same server and based on the user you are allowing them to
> see data from that database?
> What you are doing is very problematic (as you have seen) but there might be
> some other solution for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "GK" <GK@.discussions.microsoft.com> wrote in message
> news:45299590-B2F5-4FEA-9544-9F92EE2EF028@.microsoft.com...
> > Hi,
> > Im new to reporting services.
> > My requirement is to develop only one set of reports and to pass the
> > connectionstring dynamically based on the user logs in.I used the
> > setdatasourcecontents API as it overwrites the connectionstring and Im
> > passing it just before the retrieval of the reports.Can anyone suggest me
> > whether it is the best way or is there any other way to acheive this.
> > Also the user will have an option of subscribing to a report and he can
> > specify a scheduled time for that.As the report runs just before the
> > delivery
> > and Im Overwriting the datasource the mail will be sent with wrong report
> > data.So,please help me in solving this.
> >
> >
> > Im using standard edition.
> >
> > Thanks..
> >
> >
>
>|||There are several solutions for this with one exception. The problem is with
subscriptions. There is the User!userid global variable which will work for
everything but a subscription. The subscription will not know who to run as.
I have trouble seeing how to get around that even for a custom data
extension.
Leaving aside subscriptions you can solve the problem without a custom data
extension. Have a table that maps userid to database. Could also use dynamic
sql in Reporting Services but it would be cleaner with Stored Procedures.
Pass the User!Userid to the SP. Look up the database to use and then
assemble your SQL string to use it and execute it. This is clean and cannot
be modified by the user. It does not, however deal with subscriptions. I
don't have a good solution for you for that.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GK" <GK@.discussions.microsoft.com> wrote in message
news:F76DFED5-42A9-4351-9284-E82370257FCB@.microsoft.com...
> Hello Bruce,
> Thanks for your relply,I have seen some articles and custom data extension
> might be a solution for this,and I don't know how to implement this and
> whether it solves our email issue.
> Definitely "Setdatasourcecontents" is not a solution as it applies changes
> globally.
> Our client have different companies wherein the database structure is
> same.So I can create the reports for one company and pass the connection
> string based on the employee login,so that he can view the reports
specific
> to his company.
> Also when an employee subscribes for a report,how to pass the connection
> string at the time of delivery of the report so that he receives his
> company's report data.
>
> "Bruce L-C [MVP]" wrote:
> > What are you trying to accomplish with this? Is it a matter of several
> > databases on the same server and based on the user you are allowing them
to
> > see data from that database?
> >
> > What you are doing is very problematic (as you have seen) but there
might be
> > some other solution for you.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> >
> > "GK" <GK@.discussions.microsoft.com> wrote in message
> > news:45299590-B2F5-4FEA-9544-9F92EE2EF028@.microsoft.com...
> > > Hi,
> > > Im new to reporting services.
> > > My requirement is to develop only one set of reports and to pass the
> > > connectionstring dynamically based on the user logs in.I used the
> > > setdatasourcecontents API as it overwrites the connectionstring and Im
> > > passing it just before the retrieval of the reports.Can anyone suggest
me
> > > whether it is the best way or is there any other way to acheive this.
> > > Also the user will have an option of subscribing to a report and he
can
> > > specify a scheduled time for that.As the report runs just before the
> > > delivery
> > > and Im Overwriting the datasource the mail will be sent with wrong
report
> > > data.So,please help me in solving this.
> > >
> > >
> > > Im using standard edition.
> > >
> > > Thanks..
> > >
> > >
> >
> >
> >