If you’re not sure what SSRS stands for or aren’t sure what it is, this might refresh your memory:
Hopefully that didn’t cause any flashbacks! SSRS, or SQL Server Reporting Services, is one of the major reporting technologies you’ll see in any Microsoft BI solution, and although some newer technologies have made headway, it’s still one of the most common ones out there. This means that it’s especially common to see SSRS reports in SharePoint sites.
I’ve been working with SSRS and SharePoint for several years now in multiple complex engagements, and I’d like to share some of the tips I’ve learned throughout my projects. Hopefully these tips can help your organization get the most out of their reporting technology!
Tip #1: Place your reports in sub-sites
Is putting all your reports in one giant ‘Reports’ library at the top level of your portal tempting? Yes. Should you do this? Probably not.
Chances are, your site has sub-sites that the different areas of your organization uses – your Finance department has its own content that Marketing is not going to be interested in, so most of the time they’ll have their own sub-site to store this content. Reports are a really good example of this kind of content, so you should take advantage of this ‘separation of concerns’ and make sure your reports follow the same pattern as the rest of your content.
By using your department or team-specific sub-sites to house reports, you also can prevent any unauthorized access to more sensitive reports (since you can assign permissions to sites separately), while at the same time simplifying your users’ lives. It’s a much better user experience to only see 5 reports that you care about as opposed to having to pick them out of an ever-expanding list of reports you don’t care about.
If you’d like users to see their reports on arriving at your portal, you can use a Content Search Web Part to help accomplish that – and if your users have reports in more than one sub-site you can “roll up” all of the reports to the home page this way. And if you’re really interested in a seamless integration, you can follow my next recommendation – using the Report Viewer web part.
Tip #2: Use the SSRS Report Viewer Web Part
By default, if you click on a report in SharePoint, it will open up a new, unbranded page. This page will spin for a bit and show you your report. It works perfectly fine, but it’s not the best solution.
This isn’t ideal because it takes the user away from your portal experience. Sometimes, this is okay: if the report is enormous and you want all the space you can get, opening a new page makes sense. However, most of the time it just means a user has to click to see the report and then click to go back to your site and go somewhere else.
By using the Report Viewer Web Part, you can embed reports directly into your pages alongside your other content. You could put two reports on one page so the user can compare the data, for example, or you could use Audience Targeting and show one report to your Finance users and another report to your Marketing users.
The Report Viewer Web Part also has a slightly better user experience than the standalone viewer page because you can control the appearance – e.g., if there’s only one page, you can hide the pagination controls. Unfortunately, there can be some performance issues in older browsers. If you’re still using Internet Explorer 8, consider using the standalone page when possible – the report viewer will experience about 10-15 seconds of extra loading time in this browser. For everyone else, the web part will provide a user experience more in line with the rest of SharePoint 2013.
Best Practice #3: Decide on a deployment plan early
One of the things I’ve noticed about using SSRS with SharePoint is that it can be very difficult to deploy reports to SharePoint, especially when you need to deploy them frequently. There is a manual option, but if you have any complex reports and plan on making updates, it will get annoying very fast. Let’s review the options:
Option 1: Manual Deployment
To manually deploy reports, you can take your report files and upload them to SharePoint by dragging them in from a folder. Easy enough. You’ll need to upload any data connections you have as well, but it’s a similar process. This is the standard approach.
Option 2: Deploy from Visual Studio
Report developers can use Visual Studio’s Deploy option to deploy the reports they’ve created to SharePoint automatically. You’ll need to publish the reports afterwards, however, if you’re using major and minor versioning.
Option 3: Deploy automatically using custom C# code
You can deploy reports and data connection to SharePoint using the Reporting Service Web Service – however, this approach requires some amount of development time and a good understanding of SharePoint development. There’s also a fairly glaring lack of documentation on troubleshooting (Hint: if all else fails, restart Reporting Services service from SharePoint Central Administration)! Still, custom code will give you all the flexibility you need – as long as you have time to create a solution.
Here’s a quick breakdown of when you’d want to use one option over the others:
|Option||# of Reports||Frequency of Updates||Staff on Hand|
|Manual Deployment||Low (1-10)||Low to Medium – tedious but not too time consuming for low # of reports.||System Administrator or Super User|
|Visual Studio||High (10+)||Medium – difficulty doesn’t scale with # of reports, rather with # of subsites. Due to publishing, can get annoying if you have to deploy often. Not worth using if you have a low # of reports.||Report Developers – must be familiar with Visual Studio|
|C# Code||Any number – large # may require more coding, but not always.||High – this is most useful when you have to deploy a lot (say, a self-service type of use case) and don’t want staff on hand all the time to do the deployment.||C# Developer – required for when you need to make changes to your deployment strategy.|
As you can see, there’s a lot of things to take into consideration, so I highly recommend planning for the future when choosing a plan. You can always start with manual deployment, but you don’t want to get stuck doing that if you do end up with hundreds of reports being updated monthly in the future.
Overall, the SharePoint platform is a great way to expose SSRS reports to your business. Not only does it make it easy to control access to reports, it makes it easy to add them to your site and enhance your existing content. By taking extra care in how you manage, display and deploy these reports, you can maximize the business value delivered and minimize your headaches.