Monday, May 18, 2009

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.

8 comments:

Pritchardini said...

Thanks for looking into this Charles. For us, reporting directly from SharePoint lists will probably not be needed, but its always good to ask these questions as it presents a rounder view of the product.

Regards,

Gary - Plan.

Ethan said...

Thank you for this post, Charles. It's excellent and covers some of the significant "gotchas" with SSRS, lists.asmx and the XML data source. By chance, have you tried to pull back a SharePoint Notes field type? I'm working on this issue, but haven't yet found a solution.

Chanakya said...

Great Man.. I was trying this more than 4 hours and was getting some error. I was not able to figure out what was the problem. I tried the way you have explained and it worked for me.. Thanks!!!

anyam_99 said...

I can't get the deployment to work correctly although I can get my report on my development workstation. I get the error:
"Cannot impersonate user for data source".

Any idea how I can fix this?

c said...

Thanks for your comprehensive post. I was just stuck on 100 records from sharepoint list and was looking to get complete report and just found on your blog.

Keep it up

anyam_99 said...

Anyone know how to deal with fields with null values? If the first row has a field with a null value then all the values in the subsequent rows will not return any data. Creating an explicit list of column names doesn't help since for some reason some of those columns return empty as well.

hema said...

We create the list views manually and hence the GUID changes from DEV to PROD...How to address this change in SSRS query parameter :(.We are stuck up with this.Please help!

optisol biz said...

Excellent Blog! I certainly like the layout and the theme. The information is also excellent and I look forward to coming back again later on.

Hire SSRS developers