Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

One RDL with multiple data sources Expand / Collapse
Author
Message
Posted Tuesday, August 21, 2012 4:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 28, 2013 3:07 PM
Points: 72, Visits: 245
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!
Post #1348125
Posted Sunday, September 02, 2012 7:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 28, 2013 3:07 PM
Points: 72, Visits: 245
bump
Post #1353300
Posted Sunday, September 02, 2012 8:21 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:17 AM
Points: 4,379, Visits: 9,470
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1353311
Posted Sunday, September 02, 2012 10:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 03, 2014 4:01 AM
Points: 63, Visits: 277
You can refer this blog

http://social.msdn.microsoft.com/Forums/is/sqlreportingservices/thread/75dbead0-0144-4a68-b2d6-02df12873341
Post #1353321
Posted Monday, September 03, 2012 9:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 28, 2013 3:07 PM
Points: 72, Visits: 245
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.
Post #1353575
Posted Monday, September 03, 2012 9:12 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:17 AM
Points: 4,379, Visits: 9,470
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1353576
Posted Monday, September 03, 2012 9:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 28, 2013 3:07 PM
Points: 72, Visits: 245
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.
Post #1353582
Posted Monday, September 03, 2012 9:30 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:17 AM
Points: 4,379, Visits: 9,470
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1353585
Posted Monday, September 03, 2012 9:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 28, 2013 3:07 PM
Points: 72, Visits: 245
Thanks Jeffrey. This gives me some stuff to research. I am not familiar with linked reports at all. Appreciate the help!
Post #1353587
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse