Linked reports in SSRS

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.

Another part of this puzzle was that the files that weren't able to send were created in a temporary location where they would then be deleted.

This issue is now completely resolved - in brief (after several false starts such as checking file/folder permissions and discovering reports would be sent via subscription as pdf if they contained a graph and not a matrix); we ended up using SMTP (set up on SQL Server) to send the email from the SQL Server to Exchange, which would then handle the rest of the journey.

Thank you all for your suggestions.