I need to make a decision about deployment and security of some SSRS reports and would like some advice on what the most appropriate method is.
We have 40+ sites that have access to information for only their site. Each site has access to a number of reports, say Report A, Report B, etc. For each site, the report name is Report A - Site 1, Report B - Site 1, etc. For each report (Report A), there is a Master report (Report A - Master). The Master report is the report itself - dataset and formatting - and includes a hidden parameter for site name. Each sites' report links to this Master report and passes in the site name. Thus if the report changes, the change is made in one place.
Please note that limiting the reports to site-specific information on the database is not possible. Our data source uses a service account and all users access to the information is handled in the Web Portal.
Now it is time to grant users access to these reports. Currently it is set up that all of the reports and the master reports are in a single folder. Access is controlled on each report individually and users must have access to the Master report as well as their site reports.
I would strongly prefer to set up a folder for each site and control access that way. Obviously that would make it easier as site-specific reports are added, and it's superusers who will be controlling access. My concern is this will make deployment of reports incredibly difficult. Because of the linked mechanism, all 40+ versions (plus Master) are in the same BIDS project - and it seems like a nightmare to deploy to 40 different locations for every change.
I would like to know if anyone else has had a similar challenge and found a good way to solve it. I have played around with linked reports, but then the site report can't "find" the Master report, even if a linked copy is placed in the same folder.
EDIT: To clarify, and use the exact terminology, the Master report is a sub-report of each site report.