Sep

9

Use a SharePoint List as a Data Source in a SSRS Report

Posted by 5 Comments

In this example we will use a SharePoint list as a data source in a SSRS report using SQL Server 2012 Report Builder.

Begin by adding a new data source to your report. Select Microsoft SharePoint List under the connection type dropdown. For the connection string you will want to enter the root URL for your site/subsite. For example, if the URL for our list is http://technicaloverload.com/My-Test-List/, then you would enter http://technicaloverload.com/ in the connection sting box.

Data Source Properties Window

Note: Depending on how your site is set up, you may need to add valid credentials in the Credentials section of the Data Source Properties window. See the image below for an example:

Credentials Tab

Next, add a Dataset to the report. Under the Data source dropdown, select the data source we just created then click the Query Designer button.

Dataset Properties Window

In the Query Designer window, find and select your list in the SharePoint Lists tree on the left. You can expand the list and select individual columns as shown below.

Query Designer Window

Now click the Run Query button to see the results.

Query Results

If you click OK and return to the Dataset Properties window, you should see the query XML in the Query box.

Query XML

Now you can work with the new dataset like any other dataset. Here I have created a new table using the dataset from our list.

Results


Adding Parameters

If you want to add parameters to the report, go back in to the Query Designer window. Click the filter icon in the Applied filters section. Here I am going to filter on the column named Column 2. Leave the value as (blank) and check the Parameter checkbox.

Query Designer - Add New Parameter

Click OK and return to the report; you should now see the new parameter under report Parameters in the Report Data section.

Parameter Added

Run the report and try out the new parameter.

Filtered Results

Category: SharePoint, SSRS

Comments (5)

Trackback URL | Comments RSS Feed

  1. DaveH says:

    Nice writeup, Thank you

  2. Robin says:

    Super write up, very clear and exactly what i needed to know, Thanks!

  3. Nick says:

    Nice article. Any idea how to select which list view to use? Right now it just uses the default view. But what if I need to use a different view?

    • Scott says:

      Nick… in case you still need it. Yes, SP Lists always use the default view and the number of records returned will be limited by how many that default view can show on a page.

      If you wish to get the complete list of all items in the SP List dataset, you need to add a generic filter. Just add a filter like:

      Field Name = “Created Date” (or some other mandatory field)
      Operator = “is more than”
      Value = “1900-01-01”

      HTH

  4. Kathleen says:

    I’m having problems with the caching of the data…I set my report to always pull the data (no caching) but if I run the report, then make a change to the sharepoint list, then run the report again, the change does not show up in the data. I’ve checked everything I can find, caching settings on the datasource and datasets, etc. but to no avail. Do you know if there is some location in Central Admin I should look at?

Leave a Reply