By this point in the process, you should have a sense from Part 2 of what your native mode instance has in terms of reports, subscriptions, and data sources. Now it’s time to get your hands dirty and configure some servers! I’ll point you to some good references to get you started, then review what we learned about configuring a reports library that you won’t find on TechNet.
Instance Installation & Configuration References
Configuring a Reports Library
Configuring the Default View
Library Security
Gotchas
Instance Installation & Configuration References
- Install SQL Server BI Features with SharePoint
- Configuring SQL Server Denali Reporting Services SharePoint 2010 Integration
- Architecture for Scale-Out SSRS Implementation
- Configuration and Administration of a Report Server (Reporting Services SharePoint Mode)
- How to configure SQL Reporting Services 2012 in SharePoint Server 2010 for Kerberos authentication
- SQL Server Reporting Services Report Schedules
Note that the Reporting Services Add-in for SharePoint has to be installed on any SharePoint web server (e.g. any server in the farm running the Microsoft SharePoint Foundation Web Application service), as that’s what allows SharePoint to render the reports for users to see in libraries or on pages.
Configuring a Reports Library
Now that you have SSRS installed and configured on your SharePoint farm, you need to create the library or libraries where your SSRS reports will live. There’s a default “Reports Library” template available with the installation that serves as a great starting point for this process, as it includes views and custom fields that would be commonly needed for typical uses. Here’s a walkthrough of one way to create and customize a site’s Reports library.
- Navigate to the site that will host the Reports library
- View all the site contents via the applicable menu for your SharePoint installation
- Create a new item (SP 2010) or Add an app (SP 2013)
- Choose “Report Library” from the available templates
- Name the library carefully – the goal is for this library to be easy to access, and a URL like intranet.mycompany.net/sites/sales/sql%server%reporting%services%reports%library/ is not just ugly, it’s also taking up valuable characters. Remember how long report names can be and keep the library name simple, like “Reports”
- Once it’s created, edit the Library Settings
- Add the content types for Report Builder Report and Report Builder Data Source
- Under Content Types, click on the Report Builder Report and Report Data Source content types to customize the settings specific to each type.
- For example, we chose to use only some of the available columns for the Report Builder Report type’s properties:
- Under Content Types, Change new button order and default content type
- Configure the Report Category column values and settings by clicking on it to edit.
Remember that the Report Category column is a valuable way to organize your reports within a view. There’s no need to replicate the nest of layered subfolders you have in your native mode environment if you just need to visually separate the Warehouse Inventory reports from the Warehouse Shipments reports.
Configuring the Default View
The main Reports view in the Reports library is or can become the default view for the site. This is the way you want the reports to be presented a majority of the time, so it’s up to you to determine what besides the reports themselves needs to be immediately available upon navigating to this library.
There’s no right or wrong way to design this, but here’s an example of how our default Reports view is configured. It groups the reports by Report Category, then displays the report name, description, and last modified info so we know who to blame when one breaks so we know whom to contact with questions about a report.
Included columns & their order:
Content sort order & filtering to just reports – no data sources (would just be confusing):
Group by category:
If you do need to build a few subfolders into your report library to manage a few reports that need to be specially secured, or to provide a “development/in progress copies” space, you will want to configure the Folders section:
If your SharePoint farm allows it, create a customized library template to reuse on each site. This allows you to create a consistent look-and-feel across sites using SSRS reports libraries. It’s especially handy for porting over custom views and obscure library settings that you might forget about.
Library Security
Where possible, you can reuse the Active Directory groups used to secure the native mode SSRS folder, especially for report builders/authors. Make sure to review security options with your users to capture what reports must be locked down and what reports are safe to be available for anyone accessing the department’s site. Don’t just employ “security by obscurity”, as the reports will be searchable on SharePoint.
That being said, don’t just assume security is a COPY+PASTE affair from native mode. Who knows what odd security has accumulated on those folders over time?! One sure sign it’s time to rethink: access granted to many individual accounts instead of groups. That’s a maintenance nightmare! Start the business discussion with “If anyone who can see your site can also see these reports, is that OK or is that a problem?” You have all the flexibility of security in SharePoint that you have in native mode SSRS, but that doesn’t mean you need every report to be locked down to a tiny custom audience.
Gotchas
- Don’t forget to Provision Subscriptions and Alerts for SSRS Service Applications or you’ll wonder why your subscriptions aren’t running. The reports themselves will run just fine if you miss this step, but the subscriptions will never fire. Ask us how we know this…
- We found that the option to have drafts and published versions of a report doesn’t seem to be as fully vetted/functional as it may be for other content types in SharePoint. Libraries not configured to allow users to view draft versions not only had problems with search, but also tended to experience random problems connecting to their shared data sources. When we needed a way to allow authors to work on draft copies of reports that were still hosted in the same SharePoint site, we created a “Development” subfolder within the library and had the authors save the copies there.
- Report Shared Schedules for use with Subscriptions and other refresh activities are configured per reports library rather than for the single SSRS instance. There’s no way we found to share them across multiple sites, though in the end we haven’t found a need to do that. The schedule was generally shared across reports within the same folder/library.
Other Gotchas:
- SQL 2012 Reporting Services Gotchas with SharePoint 2010
- Activate the Report Server and Power View Integration Features in SharePoint
- Search Adventures in SSRS Integrated Mode
Gina Meronek 
