Introduction

SharePoint lists are stored in a SQL Server database, so you would think that connecting SQL Reporting Services to a SharePoint 2007 list would be trivial, but it’s not. There are a number of pitfalls to be avoided that are not entirely clear and do not provide clear error messages. In this white paper, I’ll outline one approach to attach a report to a list. I’ll include common mistakes and ways to avoid them as well as tips for determining causes of problems you might find along the way.

There were a couple sites that helped me figure out how to get things wired up.

RockStarGuys had nearly all the ducks in a row to get everything working. My problem was that their final query didn’t work. In fact, the new report wizard didn’t like their second query string at all, and I couldn’t get past that point in the wizard.

While looking around for help on this query, I stumbled across a blog that shows a more detailed query. From this, I was able to come up with my actually working query text.

Step 1: Get Thee A List

On the one hand, it’s silly to spend a lot of time thinking of reporting on a list that doesn’t exist, and SharePoint does make list creation a simple process. Maybe the site in question has had an existing list for a long time, already. Or, maybe this is a new site that you’re currently building.

The trick is that you not only need an existing list to build a report, you need the list ID. The protocol we will be using in this example allows the use of a name to identify the list, but it only seems to recognize the names of the built-in lists. For all other lists, it requires the list ID. This will be a GUID that identifies the list in the site.

There are a few ways you can try to get the list GUID from a SharePoint site. Sometimes, opening the list page in a new window will work. Sometimes, hovering the mouse over the link works. In this example, hovering the mouse shows a JavaScript command that includes the GUID ID for the list.

Unfortunately, there are multiple GUIDs in this list. By trial and error, I determined it was the second GUID listed in this example (starting with “9f2c2…”).

If you have access to Site Settings and can see the list in question through Site Libraries and Lists, the Customize link includes a single GUID that appears to be the correct one.

Step 2: Make a Report Project with a Shared Data Source

If you’re not familiar with Reporting Services development, you’ll want to make sure you have the proper tools in your environment stack. If you don’t have this option in your new project dialog, you probably need to make sure you have the developer edition of SQL Server installed on your box.

There are a few options to set at this point.

First of all, the credentials default to using Windows Authentication. This is good, because none of the other options (other than no authentication) work, apparently. This means that Reporting Services needs to be running on an account that SharePoint recognizes as a valid user. Because of this and other issues that can hamper a connection, I recommend making a simple report before attempting to get any real work done.

Second, the Type needs to be set to “XML”, and the connection string needs to have the URL of the lists web service. You can find this by typing

http://[server name]/[optional site name]/_vti_bin/lists.asmx

as an address in a browser and verifying the address is correct. If the browser can’t find the web services, Report Services won’t be able to find it, either.

Step 3: Small Moves

At this point, we might have a connection to the SharePoint server, but there could be a number of things wrong with it. The Reporting Services credentials might not be working or the address could still be wrong (especially if you didn’t check it in a browser, first).

For these and other reasons, I recommend making a simple dummy report first and verifying that it works before moving on to the special sauce. The blogs show a GetListCollection query that you can make for a small report that needs no parameters to work. Skipping this step can make any problems you find much harder to track down. The query given by the RockStarGuys blog works out of the box for this simple case.

The blogs also make note that the namespace does not have a trailing slash. This is important to remember, since something along the line is too picky about trailing slashes.

At this point, you should be able to test the query from the data tab, and get some rows back.

If anything has gone wrong, you’ll get a generic error message. Fortunately, there are details that might be helpful hidden behind a button that looks like a small icon.

In my troubleshooting, I found the faultstring of the last message to generally be more helpful than the rest. At this point, you’re most likely looking at an addressing or security problem.

Step 4: Time to Actually Accomplish Something

If you see the rows of data, you’re ready to get some real reporting done. The primary trick for this step is the query string. This is where the second blog was more helpful, except that it did not tell you that the list name field really wants to use a GUID for user-defined lists. You will want to use the GUID you found in the first step (or try the ones you find until you see the results you like) in place of the list name as shown in the blog. In this example, the following query worked:

As you can see, the GUID goes in the DefaultValue tag in the listName parameter tag. One option would be to leave this default value blank, set the query parameter to a report parameter, and allow access to more than one list. The caveat here is that the lists must have the same definition.

Assuming everything has gone well, you should be able to see your list. Chances are the column names are decorated with “ows_” or something similar. You can change the header text easily enough to fix that. Also, the column names will not match the names in the list. In this example, the original “Title” column was renamed to “Address,” but the underlying table still uses “Title.”

After that, it’s just a matter of details. Dates come across as strings, and need to be reconverted back to dates using CDate before they can be formatted and sorted properly. The same will apply for numbers as well.

Publish It!

Security concerns multiply when it comes to actually getting your report on a server where it can be used. The documentation clearly states that only Integrated Windows Authentication and anonymous authentication are supported. This is not entirely correct. In fact, anonymous doesn’t work at all, and Integrated Windows Authentication only seems to work from a browser on the Reporting Services server itself. The Prompt User for Authentication option (called “Credentials supplied by the user running the report” in the Report Manager) does work. This might be acceptable if you are showing the report as a kind of dashboard for occasional access, but it won’t do for scheduled reports or for Internet-facing public reports. Fortunately, you can also use the option to store credentials locally, but this only appears to work as long as you have the “Use as Windows Credentials…” check box checked.

While not completely painless, the process was actually easier than I had anticipated. In this case, most of the limitations were on the Reporting Services side. Web services are ubiquitous enough that SRS should be able to make that connection method more painless. For example, connecting to the service could use a location dialog like the one used in Visual Studio would be much more helpful than a simple text box. How hard can this be? The editor is already in Visual Studio…

From there, the WSDL is available to provide a list of methods to query as well as the parameters required. The error message dialog could make it a little clearer that the icons in the lower left corner are actually buttons without requiring you to move your mouse over them to see the borders. One can only hope that future versions of SRS and SP will make more of the espoused integration they claim.

Comments and Discussions

hi it is working fine, now i want to pass parameter for this query, i have used likeParameter Name="ows_ID">Default Value /Default ValueParameterbut no parameter is getting created for that report, like how it will happen in normal sql query when we give any parameter in query with '@'can any body help(i removed tags here intentionally, if adding they are not getting displayed in this message)

While this approach works, it is prone to performance issues and often experiences major issues with users changing the lists schema.

I've researched and attempted to use most techniques and 3rd Party products for this very task, but ended up writing a SharePoint Add-on that makes this vastly simpler and fast and validates list schemas.

Bamboo's SQL view web part generates the SQL required to create a view in the database for any list. I can then connect straight to that view using Crystal Reports and, using another project from this site, embed the Crystal Report back into my Sharepoint site.

My dataset is fetching valid fields (with the expected "ows_" prefixes) and is fetching real data from my SharePoint list, but it isn't fetching ALL of the fields. It stops at 34 fields. I've looked for references to a limit, either as a SOAP limit or a Visual Studio limit, with no luck. This particular list has 70+ fields.

you are article is very interesting and opens up a large number of possibilities, in terms of creating reports on SharePoint lists.

Questions 1: Can I also ask about some other optimizations to the datasource? I am thinking of having lists which have hundreds or maybe even thousands of items. In this case, the query you are sending is retrieving all the items in the List, even if you may be reporting on a small subset of this information. Therefore how can you optimize the amount of items retured? Is it possible to use a CAML query, or some other way of optimizing the query ideally via dynamic parameters sent from the report. E.g. if I have a status in the List, I can create a Status parameter in the report, and only bring up items which have the specified status.

Questions 2: Is it possible to create a report based on data from several lists, which are related via SharePoint lookups?

One thing though, when it comes to publishing it to the Report Server, and actually running the report, I get an error with the credentials.

An error has occurred during report processing. Cannot impersonate user for data source 'DataSource1'. Logon failed. For more information about this error navigate to the report server on the local server machine, or enable remote errors

Any thoughts? I signed-out of SharePoint and used the same ID password and I was successful with SP.I am using also Windows Authentication.

i use reporting services in integrated mode. The report looks fine in Visual studio but after adding it to a reports library when I try to view in a reporting services webpart, I get the following error:

"The report server cannot process the report. The data source connection information has been deleted. (rsInvalidDataSourceReference)"