How to use a Sharepoint list as a datasource for Microsoft Reporting Services

Step 1:

Create Sharepoint List that contains the data you would like to report against.
 Step 2: Once in SSRS:
     1. Create a new datasource
          a. Name – Name of datasource
          b. Type – XML
          c. Connection String – Home page of the Sharepoint site where the list exists.
               NOTE: After the name of the site remove the “default” and replace it with   <</_vti_bin/Lists.asmx>>
               http://<server>/<path>/_vti_bin/Lists.asmx

          d. Use Windows Authentication

     2. Create a new dataset associated with the new Datasource.
          a. Name – Whatever you want to call your dataset
          b. Data source – Set this to be the connection you created in step 1.
          c. Query Type: Text
          d. Query – Copy and paste the following HTML code (replace <<Name of your list>> with the actual name of your list) –

<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
<Parameters>
<Parameter Name="listName">
<DefaultValue><<Name of your list>></DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">*</ElementPath>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
</Query>

RISKS / Things to be aware of:

          RISK: Regardless of list settings you will (by default) only return 100 records (even if your list is set to “return all records”).
          SOLUTION: You will need to go into the list setting for the All Items view and change the “Item Limit - Limit the total number of items returned to: “ to some unattainable number “999999”.

          RISK: You will only return columns where the first row of data is not null. This one can be frustrating.
          SOLUTION: You can always add a “dummy” record called DONOTUSE and put data in each column in order to get the query to return all items. Just be sure to filter this record out in your tables and charts.