How to connect Reporting Services (SSRS) to a SharePoint List Using the “Microsoft SharePoint List” Connection Type

SharePoint Lists, they’re in every SharePoint site, and at some point someone is going to want a report about the data they contain. I’ve covered how to pull SharePoint list data into Excel for reporting purposes in previous posts, but that’s not your only option. In this walkthrough we’ll take a look at how you can leverage Reporting Services (SSRS) to report against your SharePoint lists. In this walkthrough we’ll be using SharePoint 2013 (not o365…SSRS is not available in o365 at the time of this writing) and Report Builder 3.0. This walkthrough does assume some basic knowledge of/about SSRS.

The first thing we’ll need to do is open up report builder in our environment. Once you’ve got that open, follow these steps to connect to your SharePoint list.

  1. 1. In the Report Data window, right click on the “Data Sources” folder and select “Add Dataset..”

    clip_image001
  2. In the “Name” textbox type “SP” for the name.
  3. Choose “Use a dataset embedded in my report” from the radio button options
  4. For the connection type select “Microsoft SharePoint List”
  5. In the “Connection String” box, you’ll need to enter the URL to the site or sub-site that contains the list you want to report off of. For example: the list I want to report off of is in a sub-site and the full URL to my list is http://portal.contoso.com/SPCincy2015/Lists/Trouble%20Ticket/AllItems.aspx. In this case I would only want to enter http://portal.contoso.com/SPCincy2015 into the connection string box.

    clip_image003
  6. Click on “Credentials” on the left hand side of the Data Source Properties window.
  7. Select “Use current Windows user. Kerberos delegation might be required.”

    clip_image005
  8. Click OK.

You should now have a data source named “SP” showing under your Data Sources folder in the Report Data window. Next we’ll want to create a Dataset to tell reporting services specific data we want to pull. To create a dataset, follow these steps:

  1. Right click the “Datasets” folder and select “Add Dataset”
  2. Name your dataset something appropriate
  3. Select “Use a dataset embedded in my report” from the available radio button options.
  4. In the “Data Source” dropdown select “SP” (this is the data source we created in the previous set of steps
  5. Click the “Query Designer…” button near the bottom of the screen.
  6. With the Query Designer open, locate the list you wish to report off of and click the “+” next to it.
  7. Place checkmarks next to the fields you would like to have in your report. As you select items they should also appear in the “Selected fields” section of the Query Designer window. It is important to note that you can only select items from one SharePoint list. If you wish to report off of multiple lists, you’ll need to create additional data sets. On the bright side, if the list you’re reporting off of has a lookup to another list, those values should also appear in the list that has the lookup. In the image below, my Trouble Ticket list has a lookup to the Store list, so it brings over the associated values from the Store table and shows them as Store:ColumnName.

    clip_image007
  8. Click “OK” on the Query Designer window once you have selected all the fields you need.
  9. Now that you’re back on the Dataset Properties window, make sure your screen looks something similar to the one below, and then click ok.

    clip_image008

You should now see your new data set under the “Datasets” folder. From here you can simply expand your data set and drag your values onto your report or into a table or matrix just as you would any other data set values.

If you happen to be reporting off of a document library and have folders to contend with, you might want to take a look at my previous post about dealing with Folders.   That post can be found here: http://tavislovell.com/using-ssrs-with-sharepoint-library-folders/

Hopefully this helps unlock some of your data kept in SharePoint lists…until next time, happy reporting!

How to create filters for external data columns in SharePoint lists

If you have a custom SharePoint list that uses columns that are of the type “External Data”, you may have noticed that when you’re adding a new item to the list (and more specifically when you’re picking the external data to associate with your new item), that there’s a filter dropdown when you’re browsing your external data. By default the dropdown only contains <Select Filter> and if we click on the dropdown button, we’ll see that no filters are actually available.

clip_image001

If you only have a few values available in your external content type (as seen in the image above) not having a filter isn’t really that big of a deal. However if your external content type contains a large amount of data, not having filters available can really be an inconvenience. In this walkthrough, we’ll take a look at how you can create filters for your external content types so your users are able to quickly locate the data that they need. In this particular example we’ll be using SharePoint 2013 and SharePoint Designer 2013.

This walkthrough assumes you’re already somewhat familiar with how to create an external content types using SharePoint Designer. If you’re not all that familiar with external content types you might want to check out my previous walkthrough covering the basics. You can find that walkthrough here: http://tavislovell.com/using-bcs-and-external-content-types-in-sharepoint-to-update-sql-tables/

The first thing that we’ll want to do is open the site that contains our external content type in SharePoint designer. To do that follow these steps:

  1. With SharePoint Designer open, click the “Open Site” button

    clip_image002
  2. In the “Site Name” textbox, type the full URL to your site and click “Open”. In my case I’m going to create the external list on a sub-site named “TavisBIDemo”

    clip_image003

Once connected you should see a screen similar to the following:

clip_image005

Now that we’ve got our site open in SharePoint designer, the next thing we want to do is edit the “Read List” operation for our external content type. The read list operation is where we’ll actually need to add our filter. To edit the read list operation and add a filter complete the following steps:

  1. In SharePoint Designer, click on “External Content Types” under the “Site Objects” window
  2. Double click the existing External Content Type that you want to add the filter to
  3. Open the existing “Read List” operation by double clicking on it under the “External Content Type Operations” window

    clip_image006
  4. On the “Operations Properties” screen of the wizard that comes up, click next
  5. You should now be on the “Filter Parameters” screen. This is where we’re going to be configuring our filters. In our case, we’re going to make a filter for the “First Name” column that exists in our ECT dataset. Click the “Add Filter Parameters” button

    clip_image008
  6. In the “Properties” section on the right hand side of the screen. Use the “Data Source Element” dropdown to choose the column you want to create the filter for (in my case, “FirstName”)

    clip_image010
  7. In the same properties window, click the “(Click to Add”) link next to the “Filter:” label
  8. In the “New Filter” textbox, type a name for you new filter (This will be the text that the users see when choosing the filter, so you’ll want to name it something meaningful
  9. You can adjust the additional “Filter type” and “Operator” dropdowns to suit your particular need. In my case I will also be checking the “Ignore filter if Value is:” option and selecting “NULL”

     clip_image011
  10. Click “OK” on the “Filter Configuration” window
  11. Since I chose the “Ignore filter if Value is:” option and “NULL” for my filter, I’m also going to set the default value for my filter to NULL. This will cause all possible values to show up if the filter has not been applied

    clip_image013
  12. Click the “Next” Button on the “Filter Parameters” window
  13. Click the “Finish” Button on the “Return Parameters” window
  14. Save the save button at the top left hand corner of SharePoint Designer to save your changes

That should do the trick. If we go back to our list with the ECT (refresh the browser) and bring up the picker for our ECT data we should see our “First Name” filter available. Not only that but all values should be listed since we said to ignore the filter if the value was null, and the default value is null. If we type a value into the filter and click the search button, we should see our results filtered as seen below.

clip_image014

That should do it. Congratulations you’ve created a filter for your users to quickly locate the data they need when picking from an ECT! Hope this helps.

How To Import SharePoint List Data Into A Database Table Using SSIS

For a variety of reasons you might need to import data that currently resides in a SharePoint list into a relational database table. Lucky for us there are some SharePoint Web Services we can leverage as data sources. This example will walk through pulling some example data from a SharePoint list I created and storing it in an SQL Server Table.

This walkthrough assumes you have some hands on experience/ knowledge of SQL Server Integration Services (SSIS).

In order to import our SharePoint list data we're going to leverage the "SharePoint List Adapters" for SSIS. You'll need to download them at http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652. Once you've installed "SharePointListAdaptersSetup.msi" you'll probably need to add the new source and destination objects to your toolbox. Start off by making sure you're on the "Data Flow Tab" and then right click the "Choose Items..." option as shown below.

clip_image001[4]

In the new window that comes up, you'll want to select the "SSIS Data Flow Items" tab, and then check "SharePoint List Source" then click OK.

clip_image003[4]

Now that we've added our connection objects to the Toolbox, all we need to do is drag the "SharePoint List Source" object onto our Data Flow canvas and then configure it to point to our list.

clip_image004[4]

Double click on the "SharePoint List Source" object. You'll want to configure the "Site URL" and "Site List Name" of the SharePoint list you're wanting to import (shown below highlighted in yellow).

clip_image006[4]

That's pretty much it. You'll probably also need to convert your column values from Unicode using the SSIS conversion transformation object before directing them to your destination data source. Overall though, pretty simple if you're familiar with SSIS. A quick proof of concept using a list of video games I made worked perfectly the first time.

From SharePoint List:

clip_image007[4]

To database table:

clip_image008[4]

almost effortlessly. Hope this helps.