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.
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.
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:
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
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.
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.