One of the biggest limitations of the SSRS 2005 and SSRS 2008 integration with the MOSS 2007 in the native mode or integrated mode is there are no intuitive ways to report against the SharePoint lists into the Reporting Services reports. Although SharePoint as a data provider in the SSRS can be challenging, there are ways to access the SharePoint lists with in the SSRS report.
- SharePoint List Web Service – SharePoint exposes all the list data as a list web service and SSRS has a XML as a data source which can take advantage of connecting SharePoint list web service to consume the SharePoint lists. Only limitation here is SSRS can access only one list at a time as a data source. For many real-world scenarios, this could be show stopper where reports need to access the data from the multiple lists and join them together for final presentation.
- Custom Web Service – One of the biggest limitations of the SSRS is it can’t join the data retrieved from the multiple data sources. If you ever wanted to join multiple SharePoint lists in SSRS, you can’t bring the individual SharePoint lists through multiple web service calls as multiple data sources to join them together in the SSRS. Since SSRS supports XML as a data source, SSRS reports can consume data from any custom web service which could be wrapper of multiple SharePoint list web service calls and join multiple lists into single list required by the SSRS.
In this article, I am going to demonstrate the step by step process to access the Single SharePoint list from the SSRS Reports. Let’s assume we have requirement that we need to create the reports against the SharePoint lists and we need to host the SSRS reports on the SharePoint dashboards. To accomplish this, we need to have SharePoint List, SSRS reports consuming the SharePoint list, and SharePoint web part page to consume the SSRS report. In nutshell, we have a SharePoint as data provider and data presenter and SSRS as Reporting mechanism.
Step 1: Ensure that you have a SharePoint List.
In this demo, we will report against SharePoint Tasks List.
Step 2: Understand the SharePoint List Web Service Interface.
SharePoint has a lists.asmx web service which provides interface to download the SharePoint lists data in the non-SharePoint systems. You can access the SharePoint list web service using the http://siterurl/_vti_bin/lists.asmx. You can use the GetListItems method which returns dataset to download all the list items for the specific list.
Step 3: Create new Shared Data Source and Report Server Project
Create a new Report Server Project in the Business Intelligence Development Studio (BIDS). First step of configuring the SSRS report is creating the new shared data source. Select the XML as a data source type and specify the SharePoint Web Service URL for the connection string.
On the credentials tab, specify either windows authentication or no authentication if anonymous authentication is enabled on the SharePoint Site. Please do no select any other options. None of the other options would work for the SharePoint List Web Service.
Step 4: Create a new Report.
Next steps would be creating the report specific data source and data set to configure the data for the report. Make sure Report Data pane is available in the designer.
Step 5: Create a new Report Data Source.
Using the Report Data pane, create the new report data source. Specify the Shared Data Source as a report data source.
Step 6: Create a new Report Data Set.
Using the Report Data pane, create the new data set. Specify the Report Data Source as a data source and using the Query designer specify the SOAP command to retrieve the data from the SharePoint Web Service. In the SOAP command, specify the web service method name (GetListItems), list name (list name or list guid would work), and optionally you can specify the viewname (only view Guid works, view name doesn’t work), query (CAML query), and query options.
Here is the sample Query to access the web service through the SOAP interface
<Method Namespace=”http://schemas.microsoft.com/sharepoint/soap/” Name = “GetListItems”>
<Parameter Name=”query” Type=”xml”>
<Parameter Name=”viewFields” Type=”xml”>
<FieldRef Name=”Title” />
<FieldRef Name=”ID” />
<Parameter Name=”queryOptions” Type=”xml”>
Step 7: Validate the fields returned by the Data Set.
In the Query designer, if you run the query using “!”, you can validate that query is valid and it returns the fields. Optionally, you can click on the fields tab on the data set to see if query returns the data fields contained by the data set. Calling SharePoint list web service returns both list columns and internal columns in the result set. List columns are prefixed by the “ows_”.
Step 8: Design the Report and Preview the Report in BIDS.
Once you have properly configured the data source, data set, and fields, you can design the report by dragging and dropping the fields on the report designer. In this scenario, we will create a simple tabular report and preview the report to make sure reports renders fine in the BIDS before publishing to the Report Manager web site.
Step 9: Deploy the Report to the SSRS Report Manager Web Site.
You can use the BIDS or upload the RDL files to the Report Manager site. Verify that report works fine in the Report Manager site.
Step 10: Verify the SSRS Report in the SharePoint
You can use Report Viewer or Report Explorer web parts if SSRS is installed in the native mode to consume the SSRS reports in the SharePoint. Alternatively, you can use the SharePoint SSRS integrated mode to create and consume SSRS reports more collaboratively by enabling the SSRS report content types in the document libraries. In this scenario, since reports are deployed to the SSRS manager, we will use the Report Viewer Web Part to reference the SSRS Manager Reports from the SharePoint site.
On the parting note, SharePoint as a data provider for the SSRS is getting improved in the SQL Server 2008 R2. SharePoint 2010 Reporting Services Add-in comes with the SharePoint list data extension with query designer to consume data easily in the SSRS reports if you are deploying the reports in the integrated mode. I haven’t seen the demo or played with it but something to look out for in the future.
Production Deployment Considerations – Accessing SSRS Reports Remotely
As you have seen earlier, SharePoint List web service connection supports only two kind of authentication – Windows and Anonymous Authentication. When you deploy the reports and data sources on the Report Manager Site, SharePoint List Data Source will be deployed as below.
Integrated Windows Authentication only seems to work from a browser on the Reporting Services server itself. In most of the real-world scenario, users will be accessing these reports by accessing Report Manager or SharePoint interface from their computer. It means passing windows credentials from the User computer to the SSRS server to the SharePoint Server will have multiple hops. Unless Kerberos is enabled on the SSRS and SharePoint Servers, users will not be able to access the reports remotely.
Fortunately the default data source settings can be override on the Report Manager Site. “Credentials supplied by the user running the report” in the Report Manager will work fine in the scenario where user accessing reports in occasional basis but it won’t work for the scheduled reports, Internet-facing public reports, or seamless SharePoint-SSRS integration with the web parts. Fortunately, you can also use the option to store credentials locally, “Use as Windows Credentials When Connecting to the Data Source” check box checked, access the SharePoint List Web Service using the unattended account as shown below. Please make sure this unattended account is added as Site Owner so, users can access the SharePoint Lists in the SSRS reports using this unattended account.
Hope this will be helpful.