Group by for more than two columns in SharePoint list

Posted: March 15, 2010 in List View Web part, Sharepoint Designer

There are some rare scenarios where you want group-by for more than 2 columns in list view of SharePoint list. But by default SharePoint doesn’t provide this function. OOB you can do a group-by for at-most 2 columns.So how we can go for it??

Here’s a simple way using SharePoint Designer along with some changes & additions. No customization / no code  required. Here we go.

1) Create a view for your list without any filtering or sorting.

2) Now open SharePoint designer and open the view you have created.

3) Right-click on the default ListViewWebpart that displays the content of list & and select Convert to XSLT Data View.

4) At this point the data will be displayed using a DataViewWebpart and from now on

  • You cannot edit any of the details of the view from the browser except its name.
  • Can be edited only from designer.
  • You can also use Site Actions, Edit Page, Modify Shared Web Part to hand edit the XSLT from the browser.
  • New columns added to the list hereafter will not appear in the view.

5) Now its time to make group by for the required columns. (In my case the three columns that has to be grouped ion order are IDU, Location & Title respectively).

6) Right-click on the DataViewWebpart and select Show Common Control Tasks and then select Sort & Group option that appear on the Common Data View Tasks pop-up menu.




7) On the Sort & Group settings do the following

  • Remove any sorted fields already available (ID column will be the one).
  • Now select & add all the three Group By columns in order to the Sort Order box.
  • Click “Show group header”.
  • Click “Show group footer” (This displays the count with totals).

8 ) Now click OK and if you save the page and check the page in browser you will be having group by but the columns will have wrong count.

9) This is due to the sort expression for the columns we have added and hence a small change is required.

10) So do the step 6 again, Click on the Edit Sort Expression button for the first column you have added (In my case its IDU).

11) In the Edit the XPath Expression text box make sure it shows as @IDU.

12) Now at a minimum you will need to concatenate the current grouping column with the previous grouping columns.

13) So for the second column I am changing the sort expression to concat(@IDU,’-‘,@Location) & for the third column to concat(@IDU,’-‘,@Location,’-‘,@Title.




14) Now verify once the sort columns are in the same order (Most probably it will change).

15) That’s it. Save the page and view the page in browser.

16) Now the view will be with default gray back ground.

17) You can change the color by selecting the td’s in the code view and choosing appropriate color.




Its done 🙂

Advertisements
Comments
  1. Ravi says:

    Hi Chankya,
    Thanks for the post.
    I am trying to do, group by for 4 columns and facing issue.
    Group by for 4 columns shows properly (as expected) in Share point designer but it is messed up on 4th column group by when viewed on web page.
    Any Thoughts?

    Thanks,
    Ravi

    • chanakya01 says:

      Check step 13. In my case if you see the sort expression for the third column it is concat(@IDU,’-‘,@Location,’-‘,@Title). In your case for the 4th column it should be same with one more column added. Please make sure its proper.

      The problem you are facing should be because of the Sort Expression only..

  2. Santosh says:

    Hi Chanakya,

    Thanks for your post and it is very useful for my scenario where i do need the Group by 3 fields.

    Thank You,

    Santosh

  3. 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

    Thanks.
    Harish.

    • kranthi says:

      hi harish can u plz mail me the information how you did for the task….
      i have same task tooooo

    • kranthi says:

      can u mail me ur number plz me to have same task as you i need ur number so that i will callu my id kranthi5111@gmail.com

    • chanakya01 says:

      Harish,

      Group by can be made only for columns & not column values. You need a custom development to achieve this..

      One round way is having another list with columns “Microsoft,Lotus etc” & whenever a value is entered in your main list you will add entries in this list.. Don’t know how far this is possible in your case!!!

      Regards,

      Chanakya

  4. Ahmed Bhamjee says:

    Hi,

    Did anyone manage to get this working on SharePoint 2010?

    Thanks
    Ahmed

    • Kiran Kumar Reddy says:

      Hi
      Did you find the way to group by 3 columns in SharePoint 2010? If so kindly help me in the same.

      Regards,
      Kiran Kumar Reddy

  5. subha says:

    Hi All,

    I am facing an error in displaying the sharepoint list values .Please help me in solving and put me out from this problem. my error is ” I have a sharepoint list where values are grouped by created date.When i expand the data of any particular date, the list is displaying all the values of the list.i.e, it is showing the values of all days . and all the row values are available for each date.
    the grop by function is nt working properly.” did anybody came across this type of error…please help me in solving this.

  6. subha says:

    Hi,

    I am having only one level grouping.
    It is just grouped by the created date in the list settings, i am not using any OOB functions.

  7. subha says:

    Hi,
    ill try doing it and let you know.
    Please help in clarifying the meaning of OOB group by function, as i am not aware.it will be gud if you share any link on that.

  8. subha says:

    Thanks a lot Chanakya.

  9. subha says:

    hi ,

    I tried removing the group by , then the list is nt displaying duplicate reocrds, but if i select any other column for group by function then the list is again displaying all the records, i.e the error is again following…

    Please give me some solution for this…i cant delete the columns as the site is live and running..

  10. lucky says:

    if muti group you can user javascript.

  11. Niyaz says:

    HI Chanakya

    Nice blog, do you have any article for sp 2010 lists?

    Reagards
    Niyaz

  12. Adarsha says:

    Hi Sir,

    In sort and group could not able to see [] show group footer and Advanced grouping…please let me know ; how can i solve this

    With Regards,

    Adarsha.K.R

  13. Sankar says:

    I have done this. But the collapse and expand functionality is not properly working.

  14. Fawad Munir says:

    Hello,

    I have created simple view of a list but when I open it in the sharepoint Designer 2010 I can’t find that option to “Convert to XSLT Data View”.
    Can you help me in this regard?

  15. nit says:

    Hi will this work for SP2013? I tried But i have a problem with collapsable content.Actually If the contents are collapsed i couldnt expand it back again

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s