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.
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:
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.
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.
Now click the Run Query button to see the results.
If you click OK and return to the Dataset Properties window, you should see the query XML in the Query box.
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.
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.
Click OK and return to the report; you should now see the new parameter under report Parameters in the Report Data section.
Run the report and try out the new parameter.