Configuring Reporting Services Reports in SharePoint

Getting started with the SharePoint Reporting Services Add-in in SQL Server 2005 SP2

Update Jan 2008: MS has finally posted a good article about RS and SharePoint integration on msdn. SQL Server 2005 Integration with Microsoft SharePoint Products and Technologies Troubleshooting Integration with SQL Server 2005 and Microsoft SharePoint Technologies

After installing and configuring SQL Server 2005 SP2 and the Reporting Services Add-in for SharePoint your Office SharePoint Server can be used for delivering and managing Reporting Services Reports. This assumes you have installed Reporting Services in SharePoint integrated mode. This article won't be about installing and configuring SP2 and Reporting Services because that is described pretty well in the various readme's that come with SP2 and books on-line. This article will start with how to setup your document libraries, how to create data sources and reports and focus on how to create pages in your site that access those reports. We'll have a look at how to simply execute Reports but also how Filter web parts can provide parameter values and how you can use Business Data Catalog field values for filtering.

The target audience for this article is designers or developers that have some experience using SharePoint, Web Part pages and connectable Web Parts looking for a quick introduction in how to integrate Reporting Services Reports.

Installing the Add-in installs Reporting Services specific content types, what you will need to do is add these content types to a document library that you will use to store the actual Report Definitions.

In the Document Library settings first enable management of Content types in the Advanced Settings page of the Document Library Settings. After doing this you will have a section called Content Types and a link 'Add from existing site content types'.

Figure 1, Accessing Document Library Settings

Figure 2, Adding content types to Document Library

Click that link and on the following select the 'Report Server Content Types' from the top combo box, now add all content types, you screen should look like the figure 3.

Figure 3, Selecting the Reporting Services Content Types

Now click ok and your Document Library is ready to start storing, managing and executing reports. If you have created a Document Library with no other goal in mind, now would be a good time to delete the default Content Type (Doc) and make Report Builder Report the default. Strictly speaking if you are not going to allow users to build their own reports you do not need the Report Builder Model Content type.

Figure 4, Content Types for a Document Library after configuring it

Come release time you should be able to deploy Visual Studio Report Projects directly to a Reporting Services server in SharePoint integration mode. In the CTP build I found it less problematic to go through the deployment process manually and also gain the insight in the different pieces of the puzzle.

The first piece is a Data Source, you might have noticed this type of file as one of the available content types. Create a new Data source by opening the Document library and selecting Data Source from the 'New' dropdown in the toolbar.

Figure 5, Adding a DataSource Item

Fill out the fields just like any other Data source using a conventional connection string and the authentication method you would like to use. Click ok, this will save an. rsds file in the Document Library.

Next create a Report Definition file (.RDL) using the Business Intelligence Studio. Don't use the Content type Report Builder Report just yet, this will install and launch the user friendly Report Builder application but will rely on a previously uploaded Report Builder Model which we don't have yet.

After uploading right click the uploaded file and look at the various options. You'll see that the Add-in has all the Report Management options available on the RDL File Type.

Figure 6, New options added for the RDL Content Type

We can make the Report use the previously created Data Source by choosing Manage Data Sources. Click on the Data source, choose Shared Data source and point to the previously created Data Source file.

Figure 7, Modify the Datasource to use a Shared Data Source

Using a Shared Data Source is convenient because this will enable you to modify the connection settings for multiple reports in one place but you can obviously use custom Data Sources for each Report should you need that.

Clicking on the Report actually executes the Report by opening a new page containing a Report viewer control created specially for SharePoint (the RSViewerpage.aspx) another component installed by the Add-in.

Figure 8, A Report executed by simply clicking.

Now add another report that uses a Report Parameter.

Figure 9, Default Report with a Parameter

Figure 10, Configuring the Filter.

This (Figure 9) is what the default Report Viewer page generates for a Report with one Parameter. Using the connectable Web Parts infrastructure in SharePoint we can do a lot better. First of all we need to create a Web Part page that will show our Report using a Report Viewer Web Part. In a full blown site you probable have a specific Document library to create those kinds of pages in, or you create them using SharePoint Designer. For the Demo I have added the Web Part Page content type to our Document Library and created the page here in the same Document Library as the reports. Go ahead and open the page and choose Edit Page from the Site Actions. Click Add a Web Part and add the Reporting Services Viewer part from the miscellaneous category. Open the tool pane and open select the appropriate Report. Click Exit Edit Page and you will see pretty much the same Report View as in the previous version using the RSReportViewer page only this time the actual Report is nested in a Web Part. This means we can now add other Web Part that can communicate with our Report. You can for example opt for a 'Choice Filter' Web Part from the Filter category and configure it as demonstrated in Figure 10.

Next connect it to the Viewer Web Part using the context menu for the Web Part in Page Edit mode.

Figure 11, Connecting the Filter part to the Viewer
You can click the grey bar next to the Report to collapse the viewers' parameter panel as the parameter now gets the value from the filter part. If there are multiple parameters in a Report, SharePoint will show a popup allowing you to choose how to hook them up. Beware that you have allowed Popup's on the site you are configuring.

It is even possible to use a Filter Part that displays a list in SharePoint using a filtered view that provides the Report Name to the Viewer Part. Or use a Business Data Catalog filter to use a lookup from a Line of Business System to control Report Parameters. They all work pretty much in the same consistent way.

Mixing and matching the right Web Parts this way allows you to quickly build reasonably complex dashboards without any programming. Note that you can have one Filter Part controlling multiple Report Viewer part at the same time! Combining this with some of the other Web Part types allows you to build sophisticated dashboards. There are Web Parts for Analysis Services, Excel, KPI's and Business Data forms and lists. More than enough options to impress the boss or clients I would think.

Download inks:

Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=1e53f882-0c16-4847-b331-132274ae8c84


Microsoft SQL Server 2005 Service Pack 2

http://www.microsoft.com/downloads/info.aspx?na=47&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=1e53f882-0c16-4847-b331-132274ae8c84&u=details.aspx%3ffamilyid%3dD07219B2-1E23-49C8-8F0C-63FA18F26D3A%26displaylang%3den