Complete reporting services integration with SharePoint 2013

In this article we learn how to configure reporting services integration with SharePoint 2013. SQL Server 2012 Service Pack 1 (SP1) is a version of Microsoft SQL Server 2012 that supports Microsoft SharePoint 2013 Excel Services usage of Excel workbooks containing data models and Reporting Services Power View reports.

What's new in SQL Server 2012 SP1 Installation for Business Intelligence:

·                     PowerPivot for SharePoint: Analysis Services provides a backend service for Excel Services to load, query, and refresh PowerPivot data models so that users can interact with Excel workbooks that contain data models in the browser. Analysis Services in SharePoint mode is fully independent of, and external to, SharePoint, although the server running Analysis Services must be on the same network and the Active Directory Domains Services (AD DS) forest as the SharePoint farm. You install and manage Analysis Services using SQL Server installation media and tools. Choose the setup option PowerPivot for SharePoint. After you install Analysis Services in SharePoint mode, the only additional configuration tasks are to grant the SharePoint services account's server administrator permissions in Analysis Services and to configure Excel Services to point to the Analysis Services instance.
 

·                     spPowerpivot.msi: A Windows Installer package available with the SQL Server 2012 SP1 Feature Pack. The installer enhances the PowerPivot for SharePoint experience with more features such as PowerPivot Gallery and Schedule Data Refresh. The .msi deploys Analysis Services client libraries, the PowerPivot for SharePoint 2013 Configuration tool, and copies PowerPivot for SharePoint 2013 installation files to SharePoint servers.
 

·                     Reporting Services: The overall Reporting Services installation remains the same with SQL Server 2012 SP1 as it was with SQL Server 2012. There is an updated Reporting Services for SharePoint add-in that supports SharePoint 2013.

we are only discussing the reporting services configuration. We will see the remaining sessions, described previously, in future articles. Fundamentally, there are no real differences with how this installs when compared to installing SSRS 2012 on a SharePoint 2010 farm in SharePoint mode, so if you’ve landed here looking for 2010 information, it should be valid, but the screens will look a little different.

To start with, it’s important to understand that SSRS will install as a SharePoint service application. This obviously means that it must be installed on a machine that is part of the SharePoint farm. What this does NOT mean is that you should install SharePoint on your SQL server and join it to the farm (please DON’T do that!). In a single SharePoint front end environment it is much better to add SSRS to your SharePoint server than it is to add SharePoint to your SQL server. Obviously, if you have a separate SharePoint application server, that’s the best place for it.

To install, obtain the SQL Server 2012 SP1 (or greater) media and mount it on your SharePoint server. Run the installer, choose new install and follow the prompts. Eventually you will get to the feature section screen, and assuming that machine has no prior SQL on it will look something like the following when completed.

You’ll notice that everything selected is under the Shared Features section, which means that it is not installed as part of a SQL instance. In fact, you’ll notice that we don’t have the data engine installed at all. The two Reporting Services options shown are the only items that are actually required for SSRS Integrated mode to work. As you can see, I’ve also selected SQL Server Data Tools (formerly BIDS) and Management Tools as well. I like to install these tools as a matter of course on SharePoint servers, as they can come in handy for connectivity testing or quick BI project building.

Follow the remaining prompts until the installation is complete.

Another thing that you should note is that the order of operations is important here. If you install Reporting Services – SharePoint prior to installing SharePoint on the farm, the option to create a Reporting Services application will not appear. That’s because it won’t be registered with the farm as a service application. If this happens, you can run the following PowerShell to register the Service Application

Install-SPRSService

Install-SPRSServiceProxy

Once registered, the service application can be created as below. If you install Reporting Service – SharePoint after the server has been joined to the farm, then the above steps are taken care of for you automatically.

The next thing that you need to do is to provision the service application. From Central Administration, navigate to Manage Service applications. Then, from the new menu, Select SQL Server Reporting Services Service Application.

Fill out the resulting form as appropriate, and select OK. Make sure that you navigate to the bottom of the form and select the applications to activate SSRS on.

 

Click on Provision Subscripts and Alerts

 

Click on Download script, save script and run in your SQL Server

 

 

Back in to the Provision Subscripts and Alerts settings, I have entered sureshnv\Administrator since it was sysadmin on the SQL Server.

 

Click ok. Once the service application and proxy have been created, click on it to access the management screen.

 

You’ll want to access each of the sections and fill out the appropriate options for your installation. The instructions are fairly self-explanatory, so I won’t go into them here. At a minimum, you should back up your encryption key in the key management section, Set your unattended execution account (the default account to use when no credentials are available), and your email server settings if you want to be able to deliver reports via email. If you want to enable self-service subscriptions and alerts, fill out that section, and it contains instructions for setting up the SQL agent service to support it.

The most important section is System Settings, which controls the bulk of how Reporting Services will run. Clicking on it accessed the service itself, and it’s the first place that you’ll see an error if you have configuration problems. In early builds, I have seen an error similar to the following:

The requested service, ‘http://localhost:5555/SecurityTokenServiceApplication/securitytoken.svc/actas’ could not be activated

(5555is a local port which varies from farm to farm)

This indicated a problem with the SecurityTokenService, which you can see by accessing IIS. After doing a little poking around, I tried to access the service directly in a browser via its base url:

http://localhost:5555/SecurityTokenServiceApplication/securitytoken.svc

I was then presented with an error indicating that the server was too low on memory. The solution? Allocate more RAM. It was running with 4 GB and only SharePoint installed, but it did have most of the service applications activated. The lesson – if you want all the services to work, give your server enough memory. Bumping it to 8 GB did it in my case.

If you can access your system settings, then you should be good to go. The next step is to enable SSRS in you site collections Log in to the site with Admin credentials. In SharePoint 2013, apps and libraries are added using the little gear symbol in the upper right corner.  Click the gear and click ‘Add an App’.

 

From the list of Apps, click ‘Document Library’.  I named mine ‘Reports’

 

  Once the App is created, you will be presented with an empty library.  We need to change the library to accept SSRS items, so click the ‘Library Settings’ button.

  In the settings, click Advanced Settings so we can start managing the content types.

 Now you will have the option to select Content Types.  Just under the list of settings, is a new section ‘Content Types’.  Under that, Click ‘Add from existing site content types.

 A new window opens with selector boxes allowing you to choose content types.

Highlight in the left box and click Add to add the following types:

·         Report

·         Report Builder Model

·         Report Builder Report

·         Report Data Source

It should now look as follows.

Click OK.

You have now configured SharePoint 2013 to work with SQL Server Reporting Services in Integrated mode.

You should now be able to deploy reports.  In my example library above named ‘Reports’ on a server named DEVBOX the deployment URL will be:  http://devbox/Reports

If you want to separate shared data sources and data set files,  you need to navigate to the empty library and use the Files menu ‘New Folder’ button create the folders before deploying the project.  Each setting requires the full URL to the subfolder.  If you used spaces, click the elipses (…)  to get the proper url.

Here is the view of an SSRS Project Properties box ready to deploy.

If this has been helpful, leave a comment… :)