SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


One RDL with multiple data sources


One RDL with multiple data sources

Author
Message
Triality
Triality
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 270
Is it possible to create a single report that will be ran by multiple clients, each with their own data source / data set? Basically, I want to have the exact same report for many different clients, but don't want to duplicate my RDL files for each of them. If I have to make a change to the report, I would rather not have to edit each client's report individually. If I make a change to my RDL files, I would like it to update for all clients. I have googled this a couple times and found stuff such as "yes you need to use data source substitution", but no recommendations on how that is actually done.

Thanks!
Triality
Triality
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 270
bump
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7947 Visits: 9971
Okay - this is rather a simple google search. I searched for 'SSRS dynamic data source' and the first item in this list is an article from this site.

The first page of results has a lot of results outlining how to set this up.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Sony Francis @EY
Sony Francis @EY
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 293
You can refer this blog

http://social.msdn.microsoft.com/Forums/is/sqlreportingservices/thread/75dbead0-0144-4a68-b2d6-02df12873341
Triality
Triality
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 270
Thank you for the direction, however none of these examples are applicable. My clients have access to these reports, so I don't want a parameter driving the data source, as they should not be able to run reports on our other clients....they should only be able to run reports for themselves.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7947 Visits: 9971
Triality (9/3/2012)
Thank you for the direction, however none of these examples are applicable. My clients have access to these reports, so I don't want a parameter driving the data source, as they should not be able to run reports on our other clients....they should only be able to run reports for themselves.


The parameter can be data driven - store the allowable data sources in a table and they can only select from those options. How would they get to other clients data anyways?

If you are using a single account for the data source that has access to all client's data - you will need to change that so each client is using their own login.

Another way to avoid this is to publish linked reports to a single source report. In each linked report - you would hardcode the client ID and use a table that has both the clientID and the database. The client ID parameter is hidden and they only see those they have been given access to.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Triality
Triality
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 270
Jeffrey Williams 3188 (9/3/2012)
Triality (9/3/2012)
Thank you for the direction, however none of these examples are applicable. My clients have access to these reports, so I don't want a parameter driving the data source, as they should not be able to run reports on our other clients....they should only be able to run reports for themselves.


The parameter can be data driven - store the allowable data sources in a table and they can only select from those options. How would they get to other clients data anyways?

If you are using a single account for the data source that has access to all client's data - you will need to change that so each client is using their own login.

Another way to avoid this is to publish linked reports to a single source report. In each linked report - you would hardcode the client ID and use a table that has both the clientID and the database. The client ID parameter is hidden and they only see those they have been given access to.


Thanks Jeffrey, this is drilling into my question. I guess I have done a poor job asking it. How can I restrict the parameter options for certain users so that the only option they have is their own? If they selected a different clients name from the parameter, they wouldn't be able to see the report because they wouldn't have permission to their database.

Ideally it wouldn't be a parameter at all, it would just run the report with the correct data source based on which user is logging in. Each client has multiple users with logins, so better yet it would look at a windows usergroup or something similar.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7947 Visits: 9971
I would publish the report in an area that is only accessible to my group. Then, I would create linked reports in different folders for each client - or restrict access to those reports to only the correct clients.

In the 'master' report - I would setup a parameter called ClientID and set the source to a query that pulls all clients. My group could then select any client from this list.

The next parameter would be the data source parameter - and it would also be defined from a query that uses the Client parameter to filter the result to only the data sources for that client.

Once that is done, create a linked report - on the linked report parameters I would hard-code the client parameter to a specific client, then set the hidden property. If there is only ever a single data source per client - I would then set the hidden property on that parameter.

Create a separate linked report for each client and only give access to that client by windows group.

The other way is to define the data in a table and setup that table with either the windows group or user account. If you do this, you have to make sure everyone is setup in the database appropriately. Your queries to get the client ID and the data source would have to be able to get the windows group of the logged in user.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Triality
Triality
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 270
Thanks Jeffrey. This gives me some stuff to research. I am not familiar with linked reports at all. Appreciate the help!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search