Easily Import External Data into a SQL Query Using SSRS 2008 R2 and SharePoint 2010

by Joshua Shapland

I had a requirement to create an “exceptions list” on an extract to exclude certain employees from the extract. This exceptions list needed to be easily managed by non-technical business users, so it needed to have an easy to use interface. In addition to this it needs to be up and accessible at any point in time because the process that generates the extract is an automated job runs after hours. All the data in the extract resides in SQL server tables.

My first thoughts were to create a table to hold the exceptions on the same server that the database and use Business Connectivity Services (BCS) to update the table. However, if you’ve ever worked with BCS before you know the initial setup can be a little time consuming and tedious to move from environment to environment (DEV -> TST -> PRD). So, after thinking about it for a while I came up with a solution that I am happy with: use a generic list in SharePoint and within my RDL file reference that list as a dataset and use that dataset to populate a parameter that is sent to the SQL query.

Step 1: Create the List

  1. Navigate to All Site Content.
  2. Click on Create.
  3. Search for “Custom List”.
  4. Give it a name & Push Create.
  5. Add some values to the list that you want to pass to SQL server.

Depending on your requirements you may want to create a more advanced list, but since that isn’t the focus of this article, I’m going to stick with a very basic list. You can find more detailed instructions on how to create a list in SharePoint 2010 here.

Step 2: Configuring the RDL Data Source

  1. Create a new RDL file.
  2. Create a new data source and choose Type: “Microsoft SharePoint List”.
  3. Give the connection string of your SharePoint site collection the list resides under. (ex: http://sharepoint2010.com/sites/MySiteCollection)        

Step 3: Setup the Dataset

  1. Create a new Data set and choose the option “Use a dataset embedded in my report.”
  2. In the Data Source dropdown select the data source we just created.
  3. Leave the Query Type set to Text and click the “Query Designer…” button near the bottom.
  4. Find the list you just created and check it. If you click the plus next to your list you can select on the fields you actually need. In this case I unchecked everything except for title since that’s all I need.
  5. If you click "Run Query" it will show you any values that have been added to that list.      
  1. Click “OK” twice to close the dataset editor and you should see your dataset underneath the Datasets folder now with the fields you selected.

Step 4: Setup the Parameter and Pass it to the Query

  1. Create a new parameter and name it however you’d like.
  2. Leave the data type set to text.
  3. Check Allow Multiple Values.
  4. Set the visibility to hidden (unless you want the users to select the values).
  5. Under the Default Values select the radio button “Get Values From Query”.
  6. Choose the dataset you created in step 2 as the dataset in the drop down.
  7. Select the value field you want to pass in, in this case “Title”.
  8. If you want users to select the values, repeat steps f & g for the available values and also set the label field to “Title”.
  9. Click ok, we are done creating the parameter.        
  1. Open the dataset properties on the dataset you want to pass the values to.
  2. On the parameters tab add a new parameter (assuming you don’t already have one you had planned to pass the data to).
  3. Give it a name that will match the parameter name in the query.
  4. Set the value to the parameter you created previously.

Step 5: Use the Parameter in the Query

  1. Declare the parameter in your Stored Procedure Declaration:
CREATE PROCEDURE MyDatabase.[dbo].GetEmployees (@MySharePointParameter VARCHAR(MAX))
  1. If you don’t already have a .Split() function of some sort that you use in your environment, I recommend creating one. An example of .Split() in SQL Server can be found here.
  2. SQL Server passes the data from the list in a comma delimited string, so we declare the variable as varchar(max).
  3. To utlize the data in the paratmeter is very simple with the split function, here’s how I am doing it:
WHERE E.EmployeeName NOT IN(SELECT * from Split(@MySharePointParameter, ','))

This seems like a lot of steps, but it is actually fairly quick to implement. It is a lot less painful than setting up BCS to accomplish the same thing. It also provides the business with a quick and easy way to add as many exceptions as they’d like without breaking the query or requiring developer interaction. Hope this helps and if you have any questions please let me know!