1,804 articles and 14,820 comments as of Monday, May 9th, 2011

EndUserSharePoint has combined resources with NothingButSharePoint.com. You can now find End User (Mark Miller), Developer (Jeremy Thake) and IT Pro SharePoint content all in one place!

This site is a historical archive and is no longer being updated. Please update your favorites, bookmarks and RSS feeds.

NothingButSharePoint.com
Thursday, April 15, 2010

SharePoint – Group By a Column with Multiple Values

Guest Author: James Love
Chronicles of a Chronic E-Junkie

This was something that seemed impossible for Sharepoint to do, but with a little trickery with XPath under the bonnet of Sharepoint Designer, proved incredibly simple.

If you already know how to Link Sharepoint lists and include a field as a Joined Subview, you can quickly scan over this section and skip to the code snippet below with its explanation.

Let’s say we have a List of Cars, and each car has a set of features.

We’ll start with a custom list for the Features with just the Title column, and this list stores one row for each "feature".


We’ll also have a custom list of Cars, with a Title column for the car name, and a Lookup column to the Features List created above, that allows multiple values.


What we are going to generate is a list of all Features, with a list of cars beside each feature which has those respective features.

Create a Web Part Page and open it up in Sharepoint Designer.

Create a Linked Source from the Data Source Library, and select our Cars and Features List. Set the Link Type to "joined" and close the Dialogue Box. Click our new Linked Source and hit "Show Data". In the Data Source Details, find the "Features" Row List, and drag the Title into the Web Part Zone.

Create a new column in this table, and place the cursor in the empty cell. Find Cars->Rows->Row->Features in the Data Souce Details pane, select Features and select Insert Selected Fields as… and click "Joined Subview". Click "Title" from the left column and "Features" from the right.

You should now have a lot of blank tables in the right hand side.

Go into the code view, find the dvt2 template. Find the "Rows" variable and change the select parameter so the line reads from this this:

<xsl:variable name="Rows" select="../../../Cars/Rows/Row[@Features=$dvt_ParentRow/@Title]" />

To This:

<xsl:variable name="Rows" select="../../../Cars/Rows/Row[contains(@Features,$dvt_ParentRow/@Title)]" />

If you noticed, you’ll see that the Select XPath query basically changes from finding where the @Features equals @Title, to seeing if @Features contains @Title. This is essentially how our group by works. Jump back to Code View and we have our grouped view. Modify the markup and table headers & footers to your liking. You could also use jQuery on the cell which contains the Subview to have the grouped row collapsable.

Here is our end result:


Guest Author: James Love
Chronicles of a Chronic E-Junkie

James Love works as an Information Officer for a small non-profit organisation in York, UK. Whilst developing solutions for the company’s intranet environment, he also spends time looking after IT operations and strategy. As well as web development & design, James has a keen interest in Information Architecture best practices for the corporate environment. He is a regular attendee of Sharepoint User Group UK events in Northern England.

 

Please Join the Discussion

36 Responses to “SharePoint – Group By a Column with Multiple Values”
  1. Les Flue says:

    Very nice.

    Would there be any way of performing numeric calculations, such as a count of all CD Players, or if a car had the miles per gallon recorded, what would the average miles per gallon be?

  2. Shalin Parmar says:

    Hi James,

    Excellent article. Couple of suggestions/questions to make it even more useful:

    1) Can we use Choice columns instead of lookup to do the same?
    2) As Les asked can we do sum of cars having a feature?
    3) Can you suggest the collpase/expand code in jQuery?

    Thanks! Shalin

  3. James Love says:

    Hi Les & Shalin.

    Thanks for your comments & feedback.

    It is certainly possible to do sum of cars for each feature. The cars Subview creates a new Rowset object for each feature, inside the dvt2 template. All you need to do is to count the number of items in the Rows variable and you have a total, which you can place at the end of the dvt2 template.

    Create a table row TR node after the rowview template call, retrieve the count of the Rows variable and display it.

    If you store a miles per gallon for each car, you can just include that field in the subview, and perform a similar calculation to get the average fuel consumption using the XPath Expression Builder (look for the “average” function).

    As for single list with a “Choice” column, this method wouldn’t work as my solution starts life as a slightly relational model with 2 lists. I import one list and map it to the other.

    With a choice field, the definition of which values are available to select for the column are stored in the list properties itself. In order to produce this view we’d need to first extract what is available to pick – I’m not fully sure on how to do that via the DataSource, so if anyone does know this, please share!

    And finally – I’ve yet to properly look at jQuery with Sharepoint, but do look at Marc Anderson’s blog (http://mdasblog.wordpress.com/tag/jquery-library-for-sharepoint-web-services/) as a starting point. I really don’t think there is much to it (if you already know jQuery), the tough part for this kind of solution would be finding the table generated by our DVWP – and even that can be handled easily by adding ids and classes to our HTML table rows – to perform the show/hide functionality.

    • Shalin Parmar says:

      Hi James,

      Thanks for your detailed reply. Yes I am already thinking of implementing both and jQuery I was almost done on Friday.

      My point for asking was just that if you have a preferred or complete solution available then it would make the article even better!!

      Cheers! Shalin

  4. Graham says:

    James, interesting article as I am currently tracking document criteria from a lookup table in Access and also revision dates. I’m using SP workflows to obtain edits to the documents. I would like to have the same lookup ability in SP without having to code as I am not the site owner. Any thoughts?
    thanks
    Graham

    ex Pat from Norfolk !!!!

    • James Love says:

      Hi Graham,

      Is the Access DB connected to a Sharepoint List? If so, this can be easily done with Sharepoint Designer using nearly the same the technique as in this article.

  5. Michelle says:

    Hi Graham,

    Is the Access DB connected to a Sharepoint List? If so, this can be easily done with Sharepoint Designer using nearly the same the technique as in this article.

  6. Andrew Burns says:

    Random aside – you can do that sort of Group By with the output of the Lists web service.

    http://www.novolocus.com/2008/06/10/why-i-was-getting-duplicate-rows-from-the-sharepoint-lists-web-service/

  7. istoiche says:

    James, very useful.

    What sources, online or books, would you recommend to the new-to-XPath person?

    Cheers,

    Jeremy

    • James Love says:

      Hi, sorry for the delayed response.

      All of my XPath knowledge has came from scouring the web for all sorts of examples and trying and experimenting my own. Probably not the most efficient way of learning, and most definitely takes me longer to learn best practices – but hopefully some others can offer good sources.

  8. Chris says:

    Hi james,

    sorry for previous entry, used to hitting tab for indents.

    I need to get the grouping done as you have however, whenever I enter the code snippet it errors in designer saying that ” [–>contains (@column …….. ) insert data view –> Insert Full Name from Stakeholders –> Create new column –> insert Stakeholder group (as sub view) –> Enter code view and modify the dvt_2 as shown above and error appears.

    Any help will be greatly appreciated.

    Chris

    • James Love says:

      Hi Chris,

      Can you first check the full syntax of the XPath query, ensuring all the brackets are in the right places.

      Then, could you take a screenshot of the error in Designer? Upload to tinypic.com if you need to.

      Cheers

      • Chris says:

        Hi James,

        It all looks like its in the right place, however I am notorious for never finding what I am looking for so who knows.

        The screenshot can be found here: http://tinypic.com/r/24ctwdc/5

        The error message says an invalid number of arguments.

      • James Love says:

        Hi Chris,

        You have an equals sign instead of a comma in the contains() method :o)

      • Chris says:

        well… I hang my head in shame and will commit to an appointment with the eye doctor :)

      • James Love says:

        I’ve developed a knack for spotting missing or incorrect punctuation over the years… maybe I should get out more :)

        Give me a shout if you have any more issues.

        James / @jimmywim

  9. Kevin says:

    Thanks James! This helped me take care of things….

  10. Harish says:

    Hi,

    Thanks for the post, This is what I am looking for…
    I did everything what you briefly explained and every thing is working great…But out of the 3 group by columns I am using one of these columns has multiple values.. So what should I do so that I can split the multiple values of the column and then make the item appear under each value when grouped..

    like:

    If list contains the following fields =
    [Title] [Skills]
    Joe Microsoft, Oracle, Lotus
    John Microsoft, Tivoli
    Jack Microsoft, Lotus, Adobe

    then I want to view the same list but as :

    +Adobe (1)
    Jack
    +Lotus (2)
    Joe
    Jack
    + Microsoft (3)
    Joe
    John
    Jack
    + Oracle (1)
    Joe
    +Tivoli (1)
    John
    This is what I did :
    http://techtrainingnotes.blogspot.com/2008/11/sharepoint-group-by-on-more-than-2.html

    After following the above link I was able to group by 3 columns but now I need to split the values of a column.

    Thank you.

    Harish.

  11. Harish says:

    Hi James,
    How can I sum up XSLT three column level group by functionality and the one you explained above….My previous comment is my actual requirement…

    Any help is appreciated…

    Thanks.

    Harish.

  12. Melissa says:

    Is there a way to add a third column to this? A discription field. So to use your exaple there would be two lists. A list of cars and a list of features. The feature list would have two coulmns one for the feature name and one for the feature description. The list of cars would have a title column and a lookup column with multiple selections for features.

    The end result would have a joined data view with three columns.

    Car1 Feature1 Description1
    Car2 Feature1 Description1
    Feature2 Description2
    Feature 3 Description3
    Car3 Feature6 Description6

    Does that make sense? I have gotten it to work if there is only one feature selected but if there are multiple features selected then no descriptions are returned.

    Thanks for your help.
    ~Melissa

  13. Francois says:

    Nice job and simple ! jus twhat I needed :-)

  14. KevD says:

    I know this is the right place, but I’m a bit stuck (very)!! I need to take a SharePoint 2010 list with columns broken into three sets and I need to display them on three different rows for the list item, can this do done?

    FanQs
    Kev

    • James Love says:

      Absolutely, but I wouldn’t use the method in this article, I’d use a XslListViewWebPart in SharePoint Designer 2010, then modify the Xsl so that it renders 3 rows for every row of 3 columns of your list.

      Look out for Marc Anderson’s DVWP tutorials on customising the XSL templates, or my own tutorial on something similiar: http://e-junkie-chronicles.blogspot.com/2010/03/sharepoint-designer-dataview-web-parts.html

      Cheers!

      • KevD says:

        James, thanks, and I think I’m going to need to play with SPD2010 to try and understand how to do that. But, I’m also trying to create a SharePoint 2010 page that has fields that are dynamically calc’d when a list item is updated, like an Excel sheet, is this possible too?

        Thanks greatly
        Kev

    • James Love says:

      Could possibly do that with a calculated column, depends on your calculation requirements and how often you need the data value refreshed.

      Otherwise, you can also do the same with a DataviewWebPart/XslListViewWebPart – with relevant bits in the XSL taking your value and doing whatever calculation you need on it.

      • KevD says:

        Ok, let’s put it another way, if you had a big FO finance XLS (lots of columns/rows calc’d on each other) how would you model that in SharePoint/could you model it in SharePoint (2010)?

        I’m VB.NET/C# developer and SharePoint farm/site collection admin, so all this XSL/ASPX is quite scary for me :-s

        Kev.D

    • James Love says:

      I probably wouldn’t model that in SharePoint to be fair, but I would use Excel Services to display it.

      Excel does what it does best with massive XL files, you wouldn’t get much benefit trying to recreate it all with SharePoint Lists, as the strength of a sharepoint list is primarily versioning, permission controls and workflows. A big finance XL file probably wouldn’t make much use of those.

      So, store it in a document library and use these features I listed above for that file, and use Excel Services to render it.

      • KevD says:

        That’s more what I’m thinking. There are certain aspects of it that do fit SharePoint, like the lookup of resource to cost. So I’m going off to play with the Excel services me thinks.

        Saying that, XSL is intriging and I’m now following the article by Marc D that you point to … thanks.

  15. Kimberly says:

    I followed your instructions and everything is working perfectly! I wanted to know if/how you could do a double grouping. For example, let’s say your above scenerio was grouped by cars instead of features and you wanted to show what cars had what features and of those features, the details of each feature.

    Car 1
    CD-Player
    3 disk
    6 disk
    Airbags
    2 airbags
    4 airbags
    Car 2
    Airbags
    2 airbags

    Can you help?

  16. PaulB says:

    James,
    Many thanks for this, I implemented it some time ago and it works a treat. One thing though – as I add items to the lookup list and select them in the Features items they appear ata the bottom of the linked table. I guess this is because it’s defaulting to sorting by ID, but I can’t see how to set the sort so they are arranged correctly. Would appreciate any help – many thanks in advance.

    Paul

  17. David says:

    Hi,
    Thanks for the blog.
    I did that accordingly (joining a list with a document library) but I can’t overcome clicking “Joined Subview”:

    After clicking the Joined Subview an empty dialog appears. There is nothing to relate.

    What to do with that, please?

    Thanks
    David

    • Eric says:

      Hi David – I had the same problem several times as well. You have to make sure that you are in an empty cell of the new column. That should fix it. – e

  18. Eric says:

    Hi James,

    This was huge help. Thank you.

    Do you know, is there any way to collapse the groupings, as you could do if it was done through the normail sort/group method?

    Thanks again,
    Eric

Trackbacks

Check out what others are saying about this post...
  1. TechNet Wiki Opens To IT Pros; Microsoft Kills Support for Vista RTM; Windows Mobile 6.5 Virus…

    Top News Stories Evolution Conference Travel Update In light of the recent news regarding the Iceland…

  2. SharePoint Kaffeetasse 178…

    SharePoint 2010 Entwicklung Setting Up the Development Environment for SharePoint 2010 on Windows Vista…




Notify me of comments to this article:


Speak and you will be heard.

We check comments hourly.
If you want a pic to show with your comment, go get a gravatar!