About Me

My photo
I'm currently the CTO for Hedgehog Technology a business dedicated to delivering Cloud based productivity solutions in Logistics

Friday, May 18, 2007

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


Microsoft SQL Server 2005 Service Pack 2



Anonymous said...

Excellent post Ed! This is great material and it helps me out big time. However, it looks like the image URL's have changed or something, as none of them are showing up. Any way you get the images back up? Thanks!

Ed Richard said...

Hi Brandon, the images are on a server that is off-line at the moment but should be back up after the weekend.

If you still have issues after a few days let me know and I’ll send you a complete copy in Word.

I’m also preparing a version based on SQL Server 2008.


Dunc said...

Thanks, this was a life-saver! Didn't realise that you had to add report content types to be able to add a data source...

Vivek said...

Hi Ed. I am struggling with passing more parameters to Report Viewer Web part. I am working in integrated mode and have implemented ITransformableFilterValues interface in my custom web part that provides 4 connection providers. But when I try to set up connections in the connection pop up, no parameter from report viewer comes up there for the mapping. Though I can run the report by passing the parameters directly in the parameters pane on RV web part.

Please help.

Vivek B

Vivek said...

Hi Ed. I am struggling with passing more parameters to Report Viewer Web part. I am working in integrated mode and have implemented ITransformableFilterValues interface in my custom web part that provides 4 connection providers. But when I try to set up connections in the connection pop up, no parameter from report viewer comes up there for the mapping. Though I can run the report by passing the parameters directly in the parameters pane on RV web part.

Please help.

Vivek B

Ed Richard said...

Hi Vivek,

Have you tested your custom web part with other web parts that support connections?

How do you know your custom web part works? If this happened to me I would also build another custom web part to test the connectivity.


Vivek said...

Hi Ed

Yes My provider web part works with other web parts. But with RV Web part When I click on connections, it open the connection configuration pop up with two tabs 1. Choose Connection with a Drop Down listing all the provider connection parameters. The other tab is Configure Connection when I shifts to this tab it just becomes a white pop up without anything in screen.

Ed Richard said...

That does not sound like a problem with the RV Web Part, SharePoint pops up the dialog and I'm thinking it doesn;t have all the info it needs to populate the second tab. Are you saying when you use it with other web parts, the second tab does work as expected? I'm assuming you are using 2010? A way I sometimes trouble shoot web part pages and connections is by trying it from SharePoint Designer, can you try that?


Ed Richard said...

Doing some research here:


looks like you have to use multiple filter parts, one for each parameter. I have not done this, but I think that's what it says on this page.


Vivek said...

Hi Ed

Ok let me explain what I am doing. I need multiple connection providers from a single web part. I added 4 classes in my web part project.Implemented ITransformableFilterValues in each class. Now in my webpart.cs class I exposed 4 connection providers and returned objects of the classes where I implemented the interface. It seems this is creating problem with Connection configuration pop up. Could you please point out whats wrong with this. and is this the right way to expose multiple connection providers of same interface type from a web part?

Best Regards
Vivek Batra

Ed Richard said...

I think I understand what you are trying, and its likely correct, the problem is that the report viewer web part only supports 1 parameter.

"A Web Part that implements Microsoft.SharePoint.WebPartPages.IFilterValues can provide one parameter value to the Report Viewer Web Part. "

So the only way to connect to multiple parameters would be to have multiple web parts. According to my research that seems to be consistent in everyone else's attempts.

Only other thing I could think of is that your web part actually posts back to the url with multiple url parameters and you connect the rv web part to the url parameters. That's what we have done in some cases with other web parts.

Sorry, but that's all I got fo you Vivek, you can also open a support case with MS I guess, but you are likely to get the same answer.


Vivek said...

Its ok Ed. Thanks a lot for your help.