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. In the Report Data window, right click on the “Data Sources” folder and select “Add Dataset..”
- In the “Name” textbox type “SP” for the name.
- Choose “Use a dataset embedded in my report” from the radio button options
- For the connection type select “Microsoft SharePoint List”
- 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.
- Click on “Credentials” on the left hand side of the Data Source Properties window.
- Select “Use current Windows user. Kerberos delegation might be required.”
- 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:
- Right click the “Datasets” folder and select “Add Dataset”
- Name your dataset something appropriate
- Select “Use a dataset embedded in my report” from the available radio button options.
- In the “Data Source” dropdown select “SP” (this is the data source we created in the previous set of steps
- Click the “Query Designer…” button near the bottom of the screen.
- With the Query Designer open, locate the list you wish to report off of and click the “+” next to it.
- 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.
- Click “OK” on the Query Designer window once you have selected all the fields you need.
- 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.
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!