SQL Reporting Services – Using SharePoint List Data

SQL Reporting Services allows you to leverage SharePoint data via XML query.  The process is fairly simple, although it is hard to find good details on how to do it.  The method below assumes some knowledge with Microsoft Visual Studio 2005 with Business Intelligence (or SQL Server Business Intelligence Development Studio).  You can get this by install the SQL 2005 tools.  The below should also work with the SQL 2008 development tools.

To start out with, you need to create a data source (Shared or not).  You will be create an XML data source with a connection string like:

http://sitename/_vti_bin/lists.asmx

You can use Windows Credentials or No Credentials.

Create a new dataset (on the Data tab).  Name it something logical and choose the data source you just created.  The command type is Text.

For the query itself, you will want Stramit SharePoint Caml Viewer.  This will make the process of getting the List GUID, View GUID, and Field names much, much easier.

After getting the above information from Stramit Caml Viewer, create a text query like this:

The above query will pull the ID and Title field from the (un-) specified List and View.  You’ll note that the row limit is set very high, beyond what you’d likely find in a list.  If you set the row limit too low, your query will not gather all items from the List.  However, one thing you must also do is go to the View you’re using and set the Item Limit to the maximum allowed (2147483647, or a smaller number that will work for you).  If the Item Limit is too low, again, you will not see all results in your query.

Field names must begin with “@ows_FieldName and separated by a comma.  Once you’ve built your query, hit the execute button (!) to see your query results.  You can then proceed to build your report!

3 Comments

  1. Hi i was hoping you could help me, basically i have used your query and i have got results back, however not all my fields for my view are showing, i was wondering if there was a way to set an field limit and set it as 99999 for example so all fields show? Reason i ask is because i have removed the elemant path selecting just ID and Title, if i try to select one of my fields using your original way then i cant call one field because i have a / in there so it wont pull through and at this point i cant really afford to create a new field in sharepoint because loads of views use that field.

  2. If you go to the list in SharePoint and do “Export to Excel” with save of the generated query to your drive, you may open the file with notepad and find the URL with all that is needed upto “&RowLimit=0”. Discard the rest of the URL and you are fine :-)
    BTW: RowLimit=0 means no limit. In SP2010, RowLimit=1 gives me 2 items of data, RowLimit=2 gives me 3 items.
    I am still looking for the value to get only the first item on its own … any idea?

Leave a Reply