There is a multitude of blogs that discuss how to access SharePoint data from SQL Reporting Services, so why another?  Simply because I had not found one that exposed all of the land mines I seem to hit when doing this.  So, I thought I’d try my hand at creating a detailed guide-map to the mine field.

The Requirements

In order to do this, you must have :

• Visual Studio 2005 (I’m sure this works in 2008 as well, but I haven’t tried it yet)
• SQL Reporting Extensions.  These are installed by default when you install SQL Server
• A SharePoint list exposed Anonymously or via Windows Integrated Authentication (more below)

Accessing the List

The Report Designer requires that the Data Source either require no authentication or uses Windows Integrated authentication.  Other options are available when defining the Data Source but you will not be able to use them as they are not supported for web services by the designer.

Note: If your SharePoint list requires Windows Authentication then your development machine *must* be in the same domain or a trusted domain as the SharePoint server.  If you are developing on a system that is not in the domain of the SharePoint list you are attempting to access, you will not be able to proceed.  Brutal, but there you have it.

The Steps

Start Visual Studio

Select File -> New Project -> Business Intelligence Projects -> Report Server Project.  Name and save the project.

In Solution explorer, right-click on Shared Data Sources and select Add New Data Source

Make sure you specify the Type as XML and put the proper URL to your server’s list.asmx web service page.  This is usually simply http://<server>/<path>/_vti_bin/lists.asmx, replacing <server> with your server name and <path> with the path to the site with the list you are trying to access.

Click the Credentials tab and make sure you set it to Windows Authentication (default) or No Credentials (if your SharePoint site allows anonymous access).

<Query>
    <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
    <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
</Query>

From there, click on the Parameters tab enter your parameters.  See the Parameters section below for more information.

Parameters

There are 5 parameters that can be passed: listName, viewName, query, rowLimit and queryOptions and yes, they must be this exact case.  The catch here is that if you define all those parameters you will get data in the Data view, but then the Preview will fail with a multitude of messages, usually along the lines of “The Value expression for the parameter … contains an error … “.  To get around this, define only the parameters you are actually passing values for.

listName

This tells the web service where to get the data from and it is the only required parameter.  You can give it either a list name or a Guid.  If you are unsure how to get the Guid, fire up Stramit CAML Viewer and browse to the list or simply click Settings->List Settings while viewing the list.  The list Guid will be URL encoded in the querystring after ‘List=”.  You can quickly decode it here.

viewName

The viewName tells the web service what view of listName to query in order to pull back data.  This is not required but strongly recommended because the Report Designer mangles the query parameter with severely limits your options for filtering data from the designer side of this process.  This is also an extremely picky parameter and I have yet to be able to get it to work consistently with a actual name of a view and have had to always use a Guid.

Note: If you do not specify a viewName, it will use whatever view is defined as the default for the list as the filter/sort for the data.  This is usually the All Items view and will likely include way more data than you want.

query

Do not use this parameter.  It is extremely useful for people calling the web service from code but does not work with the report designer.  I suspect that designer does some extra encoding of the CAML that this parameter normally accepts which confuses the web service to no end.  If someone finds a way to actually use this parameter from designer, please let me know!

rowLimit

The number of items to return.  SharePoint defaults to 100 so if you need more than this, you will need to include this parameter along with a number exceeding the number of rows you are likely to get.

queryOptions

Do not use this parameter for the same reason as query: it gets improperly encoded by the report designer.

Click OK and then try to get your list of fields by clicking the Refresh Fields icon ( ) in the data view.  A small + should appear next to your report name.  Click on that to see all the fields it found.  If you don’t see all the fields you were expecting, be sure to read the tip in the Annoyances section at the end of this posting.

Last step – get some data by clicking the Run icon ( ) on the Data tab.  It should pop up a dialog with the parameters you defined earlier.  Make sure all the parameters and values are there that you expect and click OK.

That’s pretty much it.  I do have some general thoughts on the whole process that I’ve tacked on below.  Hopefully, this covers most of the quirks and oddities associated with this process.

General Troubleshooting

I cannot recommend Fiddler highly enough.  With this running on the dev machine you can easily see everything that is going on in the actual SOAP calls that are responsible for those vague errors that the designer throws out.  (click to see details)

Guid’s versus Names

Ok, so do I reference the list using the Guid or name?  There isn’t an easy answer here as this is the classic catch 22.  The Guid is the ID of the item regardless of the name and is the natural thing for developers to want to use.  Unfortunately, if you are in an environment where code is migrated from a Dev farm through test/qa and then to Production, that Guid will change in each environment.  The Name is much friendlier and works across environments, but names have a tendency to change over time which will break your report.  Choose what works best in your environment.

A Word About Formatting

SQL Reports has no clue what to do with many of the columns used in SharePoint, so you might end up having to write some code to handle these.  The first you will probably see of this are the SharePoint fields that contain lookup values because these will show up on your report as something like “245#;My real name“.  Pretty nasty.

What you can do is add the snippet of code below to the Code section of the report.  To get there choose the Layout view, then Click on Report -> Report Properties from the main menu.  Click on the Code tab and paste the code below in the window

function GetNameFromSP(pFullID as string) as string
  dim strRet as string
  dim iPos as integer

  if pFullID = nothing then return ""
  if pFullID = "" then return ""
  iPos = Instr(pFullID, ";")
  if iPos < 1 then return pFullID

  return Mid(pFullID, iPos +2)
end function

Then right-click on the field in the report that you want to fix this with and select Expression.  In the Expression Builder window, set it to the following:

=Code.GetNameFromSP(<your field reference>)

It is possible to use a .Net assembly for this function as well, but that is way beyond the scope of this article.  Besides, this method doesn’t require any special installation steps on the target server.  If you are creating dozens of reports where you need this behavior or others like it, then it makes sense to look into the assembly approach.

A Word About Sorting

Odds are that the second place that you will hit the formatting snag mentioned above is when attempting to sort the report by one of the fields containing such values as it will sort by the ID part of the value string and not the name.  The easy fix is to use the same Expression as above in the Sorting and Grouping section of the report, which now allows you to sort on the real name.

Annoyances About the Report Designer in Visual Studio

Overall, working with the Report Designer in Visual Studio goes pretty good, but there are some soggy areas that you are bound to step in eventually.  I’m pretty sure these are related to working with either web services in general as a data source, or SharePoint web services specifically as I’m not seeing a lot of people reporting this problem over the net.

Vanishing Parameters

For reasons known only to designer itself, it will occasionally completely delete your list of parameters.  If you suddenly start getting errors or the wrong data and haven’t changed anything substantial, make sure your parameters are still defined.  Just make sure you have them written down somewhere where you can refer to them in order to enter them again.  I guarantee you will hit this one at least once.

Erroneous Errors

Sometimes something gets stuck in memory and designer will keep throwing an error when you try to get data or preview what should be a good data call.  Nine times out of ten, just closing the report and re-opening it will take care of this.  It’s very easy to lose an hour or more chasing a problem that isn’t really there from this.

Now you have data, Now you don’t

Fetching the data / previewing the report will work occasionally fail one attempt, then work perfectly the next with *no* changes in between.  This is a minor irritation and can usually be fixed by closing and re-opening the report.  After a while, you get used to trying everything twice.  If it fails on the second attempt, you probably really have an error.

Missing Columns

When you build the list of columns available for the report in Designer, it takes only the data in the first row of data returned.  If any of those columns are null, it won’t include the column.  Make sure all the vital columns have data when you create the report and you should be fine, even if that means manually editing them for a short time to put temporary data in.  Update: Maria has offered a solution to this.  I’m not able to try it at this moment but it looks promising – thanks Maria!

Note: if you know of fixes to any of the above, please, please post it in the comments!

Some Helpful Links

• Accessing SharePoint List Items with SQL Server 2005 Reporting Services very good checklist approach to this topic
• How to: Publish a Report to a SharePoint Library from Report Designer
• Fiddler – Good for snooping on web service requests to find the root causes of those errors
• Stramit CAML viewer
• Reporting Services: Using XML and Web Service Data Sources