Joshua Shapland

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

Posted on 4/6/2013 8:53:26 PM

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.
   
6. 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.
   
10. Open the dataset properties on the dataset you want to pass the values to.
11. On the parameters tab add a new parameter (assuming you don’t already have one you had planned to pass the data to).
12. Give it a name that will match the parameter name in the query.
13. 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))

2. 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.
3. SQL Server passes the data from the list in a comma delimited string, so we declare the variable as varchar(max).
4. 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!

Unable to Delete Content Type

Posted on 12/19/2012 11:26:04 PM

Recently I’ve been doing a lot more work in SharePoint, specifically 2010. As anyone who has worked in SharePoint for any amount of time knows it has a lot of quirks! One that recently has been giving me some troubles is the error message “Unable to delete content type. The content type is in use.” This was troubling to me because I knew I had deleted all the lists and everything that was referencing the content type.

Finally after digging around on the internet I came across a solution:

After you’ve deleted all the lists that you know of you need to also delete them from the Site Collection recycle bins as well. You’ll need to be Site Collection Admin in order to do the following.

1. Navigate to the root site collection’s settings.aspx page.
2. Click on Recycle Bin under the “Site Collection Admin" section.
   
3. Delete anything that referenced the content type (i.e. – the list) from the “End User’s Recycle Bin items”.
4. Click on “Deleted from end user Recycle Bin” and delete anything that referenced the content type (i.e. – the list).
   

This helped me resolve the Content Type in use issue. Hope it helps!

Now Featuring MVC3 and Responsive Design

Posted on 8/26/2012 7:09:18 PM

A goal of mine for some time now has been to switch my site to ASP.NET MVC3 from web forms. I can gladly say the conversion is now complete! My site now features HTML5, clean URLs, well formatted permalinks for blog posts, and cleaner JavaScript and jQuery. This process has allowed me to learn MVC and has given me some practical experience working with it. Overall I can say I really love the MVC design pattern as it makes a lot of sense and is a great way to build your website’s API.

In addition to this, since I was already revamping the whole site, I figured I’d utilize responsive design techniques to make my site mobile friendly as well. If you are unfamiliar with what responsive web design is I recommend checking out Digital Inspiration’s Responsive Web Design – A Dummies Guide. It will get you up to speed on what it is and how to implement it on your site.

To get started I utilized Zurb’s Foundation Framework. This helped grasp the basics of responsive design and gave me a good starting point to go off of. Once I got rolling and really understood how it worked I customized a lot of the underlying CSS within the framework to make it my own.

Since I’ve finished my undergraduate degree and now have a nice fulltime job, I should have a lot more time to focus on this project, as well as a few others that I’ve been considering. This has been a huge undertaking completely redoing the site and there are still a couple of bugs (mostly in the CSS) that I’ve added to my backlog, but over all I’m very happy with where the site stands right now and hope to start releasing more beneficial content.