Microsoft SQL Reporting Services (SSRS) can use data from SharePoint web services by configuring a Data Source as XML type. SharePoint web services are found under http://[server]/_vti_bin virtual directory. The commonly used web services are _vti_bin/Lists.asmx and _vti_bin/SiteData.asmx. In this post, I'm going to use GetListItem method in _vti_bin/Lists.asmx web service.

As an example, I decided to create a report for all contacts in Washington state. I'm going to add records to Contacts list. A report will retrieve SharePoint list items from a SharePoint List view which filters contacts whose state is marked as 'WA.'



1. I added 5 new contacts as an example to SharePoint Contact list.







2. Then, I created a View to filter only contacts in Washington state.







3. From Visual Studio, I created a Report Server Project, and created a Shared Data Source as shown on the picture.





4. Then, I added a report, created a DataSet with the following Query:

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

The result is shown below:

:


5. After testing, I completed a query of DataSet by providing viewName, one of optional input parameters for the _vti_bin/Lists.asmx, for "Washington" view. The value of viewName should be GUID.

<Query>
    <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
        <Parameters>
            <Parameter Name="listName">
                <DefaultValue>Contacts</DefaultValue>
            </Parameter>
            <Parameter Name="viewName">
                <DefaultValue>{4b30bf48-7c73-4004-b4bf-a9dce790a729}</DefaultValue>
            </Parameter>
        </Parameters>
    </Method>
    <ElementPath IgnoreNamespaces="true">*</ElementPath>
    <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
</Query>

The result is as shown below:



6. Instead of providing the viewName on the Query, it's possible to provide the viewName from Parameters tab of the Dataset window.



7. By dragging and dropping fields of the DataSet to a Report Layout, I created a simple report.


8. I deployed the report to a SSRS server. Here is the result:





Conclusion:

SharePoint web services can be used as a data source for Microsoft SQL Reporting Services (or SSRS). I will further investigate how I can pass actual CAML query (<Query><Where>....</Where></Query>) to a data source.