In the SSRS 2005 and SSRS 2008, one of the biggest limitations of the SSRS integration with the SharePoint technologies in the native mode or integrated mode was that there were are no intuitive ways to report against the SharePoint lists into the Reporting Services reports. Only way you can access the SharePoint Lists from the SSRS reports is using the XML as a data source to consume the SharePoint lists. Please review my previous blog for more details: http://nikspatel.wordpress.com/2010/04/30/step-by-step-consuming-sharepoint-lists-data-in-the-ssrs-reports/
Like SSRS 2005 and SSRS 2008, although query designer has improved the overall experience of consuming SharePoint 2010 lists in the SSRS 2008 R2, SSRS can still accesses only one list at a time using the SharePoint Lists 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. Fortunately, 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 by joining multiple lists into single list required by the SSRS. Alternatively, you can use the SharePoint 2010 list enhancements and list relationships to define the joined list schema in the SharePoint.
In this article, I am going to demonstrate the step by step process to access the Single SharePoint list from the SSRS Reports using SharePoint Lists as a data source. This data source supports inbuilt query designer which would allow end users or developers to select the list, define the parameters and filters on the report without understanding detailed SOAP or CAML as a Query language.
Let’s assume that we have a 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 document libraries or web part pages to host the SSRS reports. 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 Products 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://siteurl/_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 SharePoint List as a data source type and specify the SharePoint Site URL hosting the lists 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. New Query designer support for the SharePoint Lists as a data source, allows the developers (from the BIDS tool) or end users (from the Report Builder tool), browse through the SharePoint lists, select the specific list, and define the filters and parameters without knowing detailed SOAP or CAML query language. As stated earlier, one of biggest limitations of this approach is SharePoint Lists as a data source doesn’t support selecting data from the multiple lists. One way you can avoid joining multiple lists is define the list relationships and bring the additional fields along with lookup column. For more complex joins, you can create the custom web service. Custom web service requires XML as a data source and specify the SOAP command to retrieve the data from the SharePoint Web Service in the query designer.
Here is the sample Query to access the web service through the SharePoint SSRS data interface
<RSSharePointList xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema“>
<ListName>Product</ListName>
<ViewFields>
<FieldRef Name=”Title” />
<FieldRef Name=”Product_x0020_Line” />
<FieldRef Name=”Product_x0020_Description” />
</ViewFields>
<Query>
<Where>
<Contains>
<FieldRef Name=”Title” />
<Value Type=”Text”>
<Parameter Name=”Title” />
</Value>
</Contains>
</Where>
</Query>
</RSSharePointList>
Step 7: Validate the fields returned by the Data Set
In the Query designer, you can verify if query is valid and 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.
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 SharePoint.
Step 9: Deploy the Report to the SharePoint
You can use the BIDS or upload the RDL files to the SharePoint Document Libraries directly.
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, we have SharePoint Integrated environment and SharePoint document library is enabled to host the SSRS reports.
