Problem: In SharePoint Designer (SPD) 2007, it was trivial to create a linked data source. However, in SPD 2010, it would seem that I can’t create a linked data source when I add my Data View; what gives Microsoft?!?!
Background: For the uninitiated, a Linked Data Source in SharePoint allows you to Merge or Join two data sources into one logical unit. While the sources can be varied (SharePoint lists, XML, SQL) this post will focus on source that are SharePoint lists only; however the concepts will hold true when using other data sources. For additional reading on this, check out this Microsoft article: Add a linked data source
As a practical example, let’s consider that I am building a project management portal. I’ve decided that it will have a relatively simple hierarchy of Accounts –> Projects. Basically, an Account has a zero-to-many relationship to a Project. A Project has a 1:1 relationship with an Account. What I’d like to display is a web part where I show all of the projects under a particular account. I will manage a record of all my ‘Account’ sites with a list called ‘Accounts’; likewise will be performed with a list called ‘Projects’.
Solution (w/linked Data Sources): Down to the nitty-gritty then…So how do I create a linked data source?
- First, fire up SPD 2010 and open up the site in question.
- Next, resist the urge to go drop in your web part right away (in other words, don’t do anything yet).
- Relax, Breathe, and click on the ‘Data Sources’ option in the left-hand tree menu.
- From the ribbon, click on Linked Data Source button.
- On the popup that appears, click ‘Configure Linked Source…’
- Add your two data sources (SharePoint lists) and then click Next –>
- Select that you’d like to ‘Join’ (unless of course you want to Merge, but for my example we’re joining). Press Finish
Now, you can create your Data View as usual (open a SharePoint page and add the Data View Web Part); you’ll notice your new shiny Linked Data Source in the list now so go ahead and select that. The initial view that is created is a little ugly, but we can fix that by tweaking with the settings in SharePoint Designer.
Okay, so I lied a little…While it IS possible to make this very pretty in SPD, it takes a while. Regardless, I think you can get the idea of what I’m trying to accomplish below. Basically, each Account has a row, and within that account row all of it’s related projects are listed.
Arguably, the easier solution is to simply forget about linked data sources, and just use web part connections! The general idea here is to drop in a web part for Accounts (Standard List View) and a web part for Projects. After formatting the web parts how you would like them to appear, you will configure the web part connection. If you’re in SPD still, it’ll go something along the lines of:
- Add the two web parts to the page, right click on one and enter ‘Manage Connections’, and click Add.
- From the web part we want to change (Projects) we’ll want to ‘Get Filter Values From.
- Choose to connect to a Web Part on this page.
- Select the Target Web Part of ‘Accounts’ and a target action of ‘Send Row of Data To’; press Next.
- The final step is the association of columns, in our case I have entered in the associated ID of each Account on every Project line item, so my screen will look like this.
When you’re finished, you should have a page that looks like the pictures below. If a user clicks on any of the little black arrows, the right hand web part pane will change based on that new filter value. Also, the filtered column (if shown) will display the little filter funnel to let you know that the web part has been filtered on that column.
Linked data sources have their uses, but sometimes the complexity in setting them up and maintaining them can be bypassed by using a slightly different approach. Granted, both of my solutions were pretty simplistic, but I hope it’s given everyone some ideas on how they can extend this further As always, I hope this was helpful!