Consuming SharePoint 2010 Lists Data in the SSRS 2008 R2 Reports

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.