Creating a SQL Server Reporting Services Report from a SharePoint List

Although I've found blogs that explain how you can create a SQL Server Reporting Services (SSRS) report from a SharePoint list I've found just as many that say it can't be done. The explanation for the two conflicting views is (IMHO) because there is an epectation that because SharePoint lists are stored in SQL Server that you can query them directly at the database level. That is certainly not the case. If you try accessing the database directly then you deserve all the bad things that will befall you!

The correct answer is yes, you can use SharePoint lists as a datasource but via the SharePoint web services. The SharePoint web services can be found in the _vti_bin directory of each SharePoint web application and the web service you want to interrogate to get at Lists is called lists.asmx. This blog on Code Project explains how to do it quite well.

So here is how you go about it. Start off in VS 2005 and create a new Reporting Services project. Add a Shared Datasource and make it of type XML, then add the url to the web service: e.g. http:///_vti_bin/lists.asmx. You can optionally add the full path to the site e.g. http:///sites//_vti_bin/lists.asmx because this still works although you won't find this path in IISAdmin.
Then create a new report based on this datasource and add the following for the Query string

<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
<Parameters>
<Parameter Name="listName">
<DefaultValue>{DCED6771-E498-4BC5-B44D-BE71C5D7B6C8}</DefaultValue>
</Parameter>
<Parameter Name="viewName">
<DefaultValue>{C63CFF34-647C-4FFA-9E20-DA1B6737380A}</DefaultValue>
</Parameter>
<Parameter Name="rowLimit">
<DefaultValue>9999</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="True">*</ElementPath>
</Query>

Although only the listName parameter is mandatory you should always add viewName because otherwise the default view is used and this can be changed by a user and may mess up you report. Also be sure to add rowLimit because the default is 100 rows and you want to be sure you report on all the data.

Note that all the parameter names are case sensitive and you need to use the GUID rather than the name of the list and view. You might get some success with using the name but several other bloggers have reported this doesn't always work. You can find the GUID in SharePoint by simply editing the list or the view and you will see it in the URL of the Internet Explorer address bar. Just replace %2D with a hyphen and the %7B and %7D with curly braces {}.

Once you added this as your query string you should see a list of fields which you can add to your report. Note that SSRS doesn't recognise the data type of the fields and everything returned is treated as a string. Use the CInt and CDate functions (and the other converstion functions) to convert the data into the correct format - particularly if you want to include totals in your reports.

The 'lookup' and 'people and groups' fields are of the format 99#;text where 99 is the ID. To handle this goto Visual Studio, select the Report menu -> Report Properties and select the Code tab and then embed a function to strip off the stuff you don't need. This blog had a good example of the code you need.

Once you've built your report, then deploy it to Reporting Services in the same way but create a datasource in Report Manager using the same approach you did in Visual Studio (connection type: XML, connection string: http:///_vti_bin/lists.asmx). Set the credentials to be Windows Integrated and be sure that users running the report are able to access the SharePoint list.

One final point. If you were hoping to combine data from multiple lists within a site collection (where the list structure is identical) then you will have to write your own web service to do this and then call it from SSRS using the XML connection type. Performance won't be blistering if you have a lot of sites but you can get around this by scheduling the report to run at night.