Creating a SQL Server Reporting Services Report from a SharePoint List

Posted: October 23, 2009 in SSRS

You can use SharePoint lists as a datasource not through the database 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.

Here is how you do it.

1. Start VS 2005 and create a new Reporting Services project.
2. Add a Shared Datasource and make it of type XML, then add the url to the web service: e.g. http://domainname/_vti_bin/lists.asmx
3. Create a new report and select the datasource as the one you created
4. Now enter the following query string

<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction&gt;

<Method Namespace=”http://schemas.microsoft.com/sharepoint/soap/&#8221; Name=”GetListItems”>

<Parameters>

<Parameter>

<DefaultValue>{C6A6A316-1419-4D3D-8DF6-6537AAA6669C}</DefaultValue>

</Parameter>

<Parameter>

<DefaultValue>{32DE7C48-48BC-4D81-828D-B77762947235}</DefaultValue>

</Parameter>

<Parameter>

<DefaultValue>9999</DefaultValue>

</Parameter>

</Parameters>

</Method>

<ElementPath IgnoreNamespaces=”True”>*</ElementPath>

</Query>

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. You can fine more details for writing code here.

Now Click on the Layout tab. Insert a table or a chart from the toolbox and in the left hand side you will find a Dockable bar with name “Dataset” from where you will find all the columns from the which you can add to your report.
Here parameters listName refers to the GUID of the list you need to access and viewName refers to which view of the list you need. rowLimit is the number of items you need and the order will be same as in the list.

Advertisements
Comments
  1. Scott Kies says:

    This was very helpful. I could not find the syntax necessary to get the view of a list to work. This help me to resolve the issue.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s