Using SSRS with SharePoint Library Folders

Overview

About once a year someone asks me about pulling data from a SharePoint library when folders have been created within the library to help organize the content. In Reporting Services, if you connect to a SharePoint list by creating a data source using the connection type of “Microsoft SharePoint List” you will only be able to see the items that exist outside of any folders that have been created in the library. The names of the folders that exist within the library may also be visible, but returning the items inside a given folder will require a different approach. It’s certainly not difficult to imagine scenarios were you might want to do either of the following:

  • Pull only items from a specific folder in the library
  • Pull all items in the library regardless of if the item is in a folder or not.

In this walkthrough we’ll be taking a look at how we can accomplish both of those goals by abandoning the “Microsoft SharePoint List” connection type, and leveraging the “XML” connection type within SSRS and the “GetListItems” web service within SharePoint.

Example Data

Our example data for this walkthrough will consist of a SharePoint document library named “FolderTest”. This document library will have two folders in it named “Folder A” and “Folder B”. Folder A will have 2 documents in it, and folder B will have 1 document. There will also be 1 document that exists outside of the folders. Examples of the document library can be seen below.

clip_image002

clip_image004

clip_image006

Pulling items from a specific folder

For this example we’re going to pretend we have a scenario where we only want to pull items that are in “Folder A” of our “FolderTest” SharePoint Library. As mentioned earlier we’re going to leverage the SSRS XML connection type for our data source along with the “GetListItems” web service within SharePoint. We can outline our plan of attack as follows:

  1. Create an XML Data Source
  2. Format our webservice call in a dataset
  3. Reap the sweet rewards of our dataz!

So let’s get started!

Create an XML Data Source

  1. Open report builder
  2. Right click the “Data Sources” folder and select “Add Data Source”
  3. Enter “XMLTest” as the data source name
  4. Select “Use a connection embedded in my report”
  5. Select “XML” as the “Connection Type”
  6. For the connection string you’ll want to enter the URL to your site plus “/_vti_bin/lists.asmx”. For example, my library exists on the site http://portal.contoso.com, so the URL for my connection string will be http://portal.contoso.com/_vti_bin/lists.asmx. You can verify the connection string by entering it into your web browser. Doing so should return a list of web services for the site as shown in the image  below. (If the particular library you’re try to pull from happens to exist on a subsite, you’ll want to include the subsite in your URL…ex: http://portal.contoso.com/subsite/_vti_bin/lists.asmx)

    clip_image008
  7. Click on the “Credentials” menu item on the left hand side of the “Data Source Properties” window
  8. Select the option “Use the current Windows user. Kerberos delegation might be required.”
  9. Click the “OK” button on the “Data Source Properties” window.

Format our webservice call in a dataset

Now that we’ve got our data source created, we’ll need to create a dataset to pull the specific data we’re after. For our scenario, we’re going to say that we only want to return items that are in the “Folder A” folder of our “FolderTest” library. To create the data set, complete the following steps:

  1. Right click on the “Dataset” folder in Report Builder and select “Add Dataset”
  2. Name the Dataset “ListItemsInFolderA”
  3. Select “Use a dataset embedded in my report”
  4. In the “Data source” dropdown, select the “XMLTest” data source we created earlier
  5. Click the “Query Designer” button
  6. In the “Command type” dropdown, select “Text”
  7. Paste the following code into the “Query Designer” window. You’ll need to change the highlighted areas of the code to contain your own SharePoint Site URL, Library Name, and Folder Name

    <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>FolderTest</DefaultValue>
          </Parameter>
          <Parameter Name="queryOptions" Type="xml">
            <DefaultValue>
              <QueryOptions>
                <Folder>http://portal.contoso.com/FolderTest/FolderA</Folder>
              </QueryOptions>
            </DefaultValue>
          </Parameter>
        </Parameters>
      </Method>
    <ElementPath IgnoreNamespaces="True">*</ElementPath>
    </Query>
  8. Click the red “!” to execute the query. In our example scenario we should see 2 rows of data returned. One for each item in “Folder A” of our library.

    clip_image010

  9. Click the “OK” button on the “Query Designer” window
  10. Click the “OK” button on the “Dataset Properties” window

Now we should be able to interact with data associated with items in “Folder A”, just like we would any other dataset in reporting services.

Pulling all items for the library

Pulling data from 1 specific folder in a library is a nice trick to have up your sleeve, but another common request I see quite a bit is to pull all items from a given library, regardless of if the items are in a folder or not, we just want everything. In our example data this would be 4 total items: 2 items from Folder A, 1 item from Folder B and 1 items that’s not in either folder. To do that we’re going to use the same approach of leveraging the XML data source along with the “GetListItems” web service within SharePoint. We’ll need to change up our query a little though to look something like the following:

<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>FolderTest</DefaultValue>
      </Parameter>
      <Parameter Name="queryOptions" Type="xml">
        <DefaultValue>
          <QueryOptions>
                      <ViewAttributes Scope="Recursive" />
         </QueryOptions>
        </DefaultValue>
      </Parameter>
    </Parameters>
  </Method>
<ElementPath IgnoreNamespaces="True">*</ElementPath>
</Query>

Note that you still have to modify the query to point it to your specific list shown in green above. The main difference between our first query and this new query that gets all the items is that we’ve replaced the section that pointed to a specific folder with the line <ViewAttributes Scope="Recursive" /> that tells the query to get all of the items in the library. As you can see below, all 4 library items are now being returned by our query.

clip_image012

That should do it for this post. I’ve got a few other go to tricks I seem to use over and over when reporting off of SharePoint lists that I plan on sharing in upcoming posts. In the meantime, if you’re looking to milk a little more functionality out of how you query your lists using the XML data source and the “GetListItems” web service within SharePoint, here a nice reference link: https://msdn.microsoft.com/en-us/library/lists.lists.getlistitems(v=office.12).aspx

Happy reporting!