Click here to Skip to main content
15,867,835 members
Articles / Programming Languages / XML
Article

SQL Reporting Services data from SharePoint lists

Rate me:
Please Sign up or sign in to vote.
3.50/5 (6 votes)
18 Mar 2008CPOL7 min read 203.2K   34   15
Create reports in SQL Reporting Services that reads data from SharePoint user lists

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.

GotAList_cropped.png

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.

GotAGuid_cropped.png

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.

NewProjectDlg.png

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.

SimpleQueryTest.png

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.

GenericError_cropped.png

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:

XML
 <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>{9f2c2d37-eef7-43f0-85e6-91af7524d775}</DefaultValue>
         </Parameter>
      </Parameters>
   </Method>
   <ElementPath IgnoreNamespaces="True">*</ElementPath>
</Query>


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.”

Preview.png

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.

StoredCredentials_cropped.png

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Belami
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralPerformance Pin
Goble de gook22-Oct-09 19:37
Goble de gook22-Oct-09 19:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.