Sql-server – How should I deploy these SSRS reports

sql-server-2008ssrs

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.

Best Answer

Well... I figured it out!

Here's what I did:

  • Deploy all the reports from the BIDS project to a main folder, say Source Reports.
  • On the ReportServer, create site-specific folders in another folder, say Site-Specific Reports. Also in that folder, create a Master Reports folder.
  • Copy the Master reports to the Master Reports folder.
  • For each site report, create a linked report in their site-specific folder under Site-Specific Reports.

Now here's the "tricky" part... if you edit the report in the Source Reports folder in Report Builder, you can set up the sub-report to point to a specific folder structure on the ReportServer. This enables you to use sub-reports anywhere on your ReportServer, not just in the same local directory. My assumption is you can also type in the full path of the deployed report you wish to use as a sub-report into BIDS, but with Report Builder you can browse and click.

So, in this case, I have made all of the site-specific reports point to the Master report in the Master Reports folder.

This is a great solution for us. Now we can grant user access at the folder level without creating excessive burden on the report deployment process.

Related Question