August 21, 2012 at 4:06 pm
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!
September 2, 2012 at 7:27 pm
bump
September 2, 2012 at 8:21 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 2, 2012 at 10:02 pm
September 3, 2012 at 9:06 am
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.
September 3, 2012 at 9:12 am
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 3, 2012 at 9:20 am
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.
September 3, 2012 at 9:30 am
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 3, 2012 at 9:37 am
Thanks Jeffrey. This gives me some stuff to research. I am not familiar with linked reports at all. Appreciate the help!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply