11/23/2008

SharePoint: Group By on more than 2 columns in a view (Updated!)

 

An expanded version of this article, along with many other customization examples and how-tos can be found in my book, SharePoint 2007 and 2010 Customization for the Site Owner.

 

 

12/19/09   Updated!  More pictures and some more detail in the steps.

12/19/09   Expand Collapse bug found and fixed! (not my bug, SharePoint’s bug!)  See here…

 

1/30/11   SharePoint 2010 version of this article. See here…

 

Group By on more than 2 columns in a view

In my SharePoint classes I have often been of asked how to group on more than two levels in a view. I have always given the quick answer of "use SharePoint Designer"!

But, some of you have asked "How?". You may be sorry you asked… but here goes…


Goals:

  • Create a view with nested groups deeper than just 2 levels
  • Put the counts and totals at the bottom of each section.
  • Do the above with no custom web parts, custom XSLT or anything that has to be deployed to the server.

Sample:
       Sample list with three levels of grouping

Secrets and Tricks needed:

  • It can't be done "out of the box" in the browser
  • SharePoint Designer is needed to make the edits
  • You need to convert the view web part to a XSLT Data View (SharePoint Designer's Data View web part)
  • You need a Sort and Group secret
  • You need to fix the footer rows
  • You need to manually add your totals, averages, counts etc.

My Example:

  • I have a simple list (actually imported from Excel) with these columns: ID1, Bike, BikeType, BikeSize, BikeColor and Retail (price).
  • I want to group this on BikeType, BikeSize, BikeColor and count the items and sum or average the price in each group.

 

Steps: (and yes, a lot of them!)

  • Tip: There are many, many options available in the Data View Web Part used here. For the first time through this process don't change any options not listed below. You can always come back and experiment with the rest of the options later!

  • Create a new Standard View from the list's view menu.
    Generally don't bother with any options other than selecting the desired columns.
    • Don't set any grouping options, they will mess up the expand / collapse functionality
    • Don't set up any Totals options, they will be ignored
    • Do select the columns needed in your view, both display and grouping
    • Optionally set the filter options (this can also be done in SharePoint Designer)

      The “before” – just a standard view with selected columns:
            image_thumb[1]
  • Open your site with SharePoint Designer and expand the Lists node in the Folder List pane.
  • Expand your list's node and double-click on your new view.
                                                       image_thumb[2]
  • If your view page is not being displayed in the Design view, click the Design tab at the bottom of the page.
  • Note: the view is now being displayed using a ListViewWebPart and can only be customized from the browser ("Modify View")

  • Right-click in the middle of the view and select Convert to XSLT Data View.

       image_thumb[4]
  • Note: the view is now being displayed using a DataFormWebPart and

    • is no longer a “View” and can not longer be modified from the “Customize this view”
    • can be customized from SharePoint Designer,
    • from the browser you can only rename the view,
    • from the browser you can also use Site Actions, Edit Page, Modify Shared Web Part to hand edit the XSLT,
    • going forward, columns added to the list will not be added to the DataFormWebPart.
  • Your data should now be displayed.
    • You could also fix up the display of the data (right align some columns, rename column headings, select color, bold, etc)

  • Save! At this point you may want to click Save and review the results so far in a browser. Review the formatting and note the things you need to change. This will then be an unghosted, or customized, page.
                    image_thumb[28]


  • Now add the Sorting and Grouping options…
    • For my example I am first grouping on BikeType, then by BikeSize and BikeColor
  • Right-click the web part and select Show Common Control Tasks (or click the pop-out arrow at the top right corner of the control The pop-out is named “Common Data View Tasks”).
    image_thumb[8]             image_thumb[7]
  • Click “Sort and Group”
  • Remove any sort fields listed (usually one)

    image_thumb[10]

  • Add your top most group:
    • Click the field (BikeType) and
    • Click Add
    • Click “Show group header”
    • Click Collapse group (optional)
    • Click “Show group footer” (if you want counts and totals)

        image_thumb[16]

  • Add your second group by clicking the field (Example: Size) and clicking Add.

    • If you stop here you will have groups, but all of your counts and totals will be wrong! We need to create both the text to display for the group and a hierarchy for the grouping. At the second level of our grouping we need to group on the combination of both columns, “Bike Type” plus “Size” 
    • Click “Edit Sort Expression”
       
      • At a minimum you will need to concatenate the current grouping column with the previous grouping columns, and while you are at it you can add some formatting:
      • Enter: concat(@Bike_x0020_Type, " - ", @Size)  (Watch the capitalization!  Use the exact name the Intelisense offers – spaces are “coded” so “Bike Type” is “Bike_x0020_Type”.)
      • Note the preview at the bottom of the dialog box…
        image_thumb[24]
      • Click OK
      • Click Show group header
      • Click Collapse group (optional)
      • Click Show group footer (if you want counts and totals)

  • For the third and following groups repeat the step above with Sort Expressions similar to:
    • concat(@Bike_x0020_Type, " - ", @Size, " - ", @Bike_x0020_Color)
    • In each new group include all of the fields from the previous groups along with any separators you like:
      concat(@group1field, @group2field, @group3field, @group4field, @group5field, etc)
      or concat(@group1field, " - ", @group2field, " - ", @group3field, " - ", @group4field, " - ", @group5field, etc)

        • Before clicking OK to leave this dialog box, check the order of the groups. They have probably gotten changed.  (bug?)
          • Make sure they are in order something like:
            @BikeType
            concat(@Bike_x0020_Type, " - ", @Size)
            concat(@Bike_x0020_Type, " - ", @Size, " - ", @Bike_x0020_Color)
          • Recheck each of the sort levels to see if these are still checked:
            • Click Show group header
            • Click Collapse group (optional)
            • Click Show group footer (if you want counts and totals)
        • Optionally add one more column just to sort the data within the last group. Do not turn on the group header or group footer options for this sort-only field.
        • Click OK to close the Sort and Group dialog box.
        • You will probably want to change the Paging options as they default to 100 items.
          • Right-click the new data and select Show Common Control Tasks (or click the pop-out arrow at the top right corner of the control).
          • Click Paging and pick your options
            image_thumb[26]
        • Tip: If you have a lot of data in the list SharePoint Designer can get really slow. There are two things you could do here, set paging to a small number (not so good for testing multi-level grouping) or from the Common Control Tasks select Show With Sample Data.
                         image_thumb[27]

        • If you want grand totals you will also need to click Data View Properties and check Show View Footer.

        • Save! At this point you may want to click Save and review the results so far in a browser. You should now have all of your groups.
                                   image_thumb[30]
          Click the + to expand a section.

        • Now is a good time to clean up the formatting such as the gray background and odd row height in the group header and footer rows. (Right-click in the row in Design view and select Cell Properties.

                            (click for bigger view)
                            image_thumb[31]
        • Your grouping work is now done. The following steps are only needed if you want to add totals, counts, averages, etc.

        • Now for totals!
          • These steps describe adding totals to the footer rows, but also apply to modifying header (top of each group) rows.
          • The Data View sets up the header and footer rows as a single cell with a column span of 99! To display totals in the same columns and the data you will need to fix these rows.
          • You will need to fix each of the group footer rows plus the view footer row.
        • In the Code View do a search for "colspan="99"". In my example there will be seven of these, one for each grouping footer, one for each grouping header and one for the view footer.
        • Now you need to do a little planning. In my example I'd like to put the "Count=" in a two column spanned cell under the ID and Bike columns. Then I would like to have one cell under each additional column. So something like: <td colspan='2'>count stuff</td><td></td><td></td><td></td><td>total goes here</td>

          Total boxes

          Steps:
          • Find "colspan="99"" for the first group footer row. (Tip: Did you find a header or footer row? Look up two lines and you should see "<xsl:if test="$showheader"" or "<xsl:if test="$showfooter""!)
          • Change the 99 to 2
          • Find the end tag for the cell (</td>) (down about 16 lines of html)
          • After the end tag add the other cells (TD tag pairs), one for each additional column (I need four more)
            <td></td> <td></td><td></td><td></td>
        • Now to add the total…

          • Switch to Design view and you should see the new cells in one of the footer rows.
          • Click in the cell where you want the total
          • Right-click and select Insert Formula
          • Build your formula. For a total double click "sum" in the function list then select your field from the dropdown list. My example needed: sum(@Retail)
          • The formula editor does not know one extra piece of information needed here. The @Retail field needs to come from the current group only. To get this, update the formula like this: sum($nodeset/@Retail) ("nodeset" is all lower case)
            Tip: How would you have discovered this? Go look at how they calculated the Count: count($nodeset).
          • Notice that the formatting is wrong. The total is left aligned and the wrong size. To match the font and size find the style used for the data in the row above. In my example it looked like this: <TD Class="{$IDADW3HE}">
          • Copy the class info (Class="{$IDADW3HE}") into your total cell (the TD tag). The result will look something like this:
            <td Class="{$IDADW3HE}"><xsl:value-of select="sum(@Retail)" /></td>
          • The last step is to right align the cell (you can use the toolbar button) and to format the total. To format the number, click in the cell with the total, right-click and select Format Item As… and pick your formatting options.
          • Note: SharePoint Designer may get creative and merge your formatting in new styles with great names like style1, style2, style3 etc.. If you are curious, do a search for .style1 (dot style1) and you will find they have placed the style class definition in the PlaceHolderBodyAreaClass master page placeholder.
        • Fix up each footer row, including the View Footer, the same way. Add the extra TDs, add the totals, counts, etc, and format the results.

        • Now about the View Footer… and some things to make your head hurt…

                       Grand total row

          • The View Footer is built in its own HTML table, so the column widths are not going to line up with the rest of the list. (I'll have to come back with another article to make these line up correctly).
          • The grand totals need a special calculation to sum all of the rows. The @Retail field (or @yourfield) needs to come from the entire set of rows. To get this, update the formula like this: sum(/dsQueryResponse/Rows/Row/@yourfield) (make sure the capitalization is correct!)
            Tip: How would you have discovered this? Go look at how they calculated the Count for the View Footer: count(/dsQueryResponse/Rows/Row) .


         

        Finally all done!

         

        A lot of steps, but it's a fairly routine and mechanical process. Do it a few times and you to will be able to amaze your fellow site owners!

        Hopefully the next version of SharePoint will support more than two levels of grouping. Then we can all brag: "Back in the old days I had to … "    (But sad to say… 2010 Beta 2 does not!)

        73 comments:

        Anonymous said...

        This is great. However, for some reason the 2nd level in SPD is ALWAYS expanded. I want to scream. I checked the collapse options on both the primary & secondary groupings and also ensured I did the concate you showed on the second field. Any suggestions?

        Bob Eldredge said...

        WOW! Great post - thanks for the detailed explanation!!!

        Mike Smith said...

        DaveK,

        Most likely you set a grouping in the view before customizing it in SharePoint Designer.

        Here is the note from the steps above:
        "Don't set any grouping options, they will mess up the expand / collapse functionality"

        Mike

        Anonymous said...

        This is exactly what I was looking for. Thanks for posting.

        Pr0x1 said...

        I see this is for a single list/lib, any chance on having this go across multiple libraries?!

        Anonymous said...

        I would like to add two more list items under same group by. Is this possible?
        Example:
        Created By:XYZ
        XYZ 10 30 40
        XYZ 20 50 70
        i would like to convert this as
        Created By:XYZ
        XYZ: 30 80 110

        Thanks...

        Mike Smith said...

        Kamal,

        Do you want to add more totals in footer? Or hide the detail lines?

        The article above should have enough info to add more totals in the footer. Something like "sum($nodeset/@Retail)".

        If you want to hide the details, then in SharePoint Designer just select one of the detail rows in the web part and press delete.

        Mike

        Mike Smith said...

        > Pr0x1 said...
        > I see this is for a single
        > list/lib, any chance on having
        > this go across multiple libraries?!

        Yes. You need to start with a linked data source in SharePoint Designer. Here's some info: http://office.microsoft.com/en-us/sharepointdesigner/HA101154041033.aspx

        In the steps above... find "Right-click in the middle of the view and select Convert to XSLT Data View" and change that do "Delete the existing web part and insert a new Data View web part based on a Linked Datasource". The rest of the steps should follow just fine.

        Mike

        Unknown said...

        Thanks for your article...this is exactly what I have been looking for. So after I convert the WebPart to the XSLT I get the following error:

        ********
        Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Windows SharePoint Services-compatible HTML editor such as Microsoft Office SharePoint Designer. If the problem persists, contact your Web server administrator.
        **********

        Now to fix this I changed the ViewFlag from 0 to 1. However I am now getting ctx errors...how were you able to convert over from the normal webpart view over to the XSLT without running into any errors.

        A little history:
        I am running a Wiki site with a custom column listing multiple choice values. I am not able to group by my custom category column and the built in category column only allows a text only field.

        Your article allows me to change it to the format that I wish - however when changing the flag to "0" - I produce these ctx errors.

        There isnt any customization besides that - so any assistance would be appreciated. Thanks!!

        Mike

        Mike Smith said...

        Michael,

        Did you get the error just after the conversion to XSLT? Or were there other edits before you saved and did a test view in a browser?

        I have not tried grouping on Wiki lists, so there may be a problem there.

        What is the ViewFlag? I have never seen it with a value other than 0, and Microsoft has little documentation on it.

        The "Unable to display this Web Part" message implies an error in your web part HTML or XML/XSLT. I would go back and repeat the experiment one step at a time, saving frequently and testing in a browser.

        Mike

        Anonymous said...

        Mike,
        i have tried group by using 'Created By' field. i could not get count/sum of grouped items. But i tried with 'single line of text' field and got succeeded.
        'Created By' field might be causing issues.
        Could you please look into this.

        Thanks...

        Mike Smith said...

        Kamal,

        I just did a group-by on Created By and it worked. What type of list are you working with?

        Mike

        Panda said...

        Do you know if the XSLT Data View for SharePoint Designer supports a Thumbnails column?

        I've got a user wanting to group by more than 2 columns in a Picture Library

        Mike Smith said...

        Panda,

        Yes, just create the XSLT to display the thumbnail.

        See here: http://techtrainingnotes.blogspot.com/2009/04/sharepoint-displaying-pictures-and.html

        Mike

        Anonymous said...

        Hi Mike,

        Is there a way to add the total to the header?

        e.g.

        GroupHeaderOne (3)
        item 1
        item 2
        item 3
        Group HeaderTwo (2)
        item 4
        item 5

        Mike Smith said...

        > Is there a way to add the total to the header?

        Yes, just use the exact same steps above as for the total in the footer. The XML processing of the data allows totals before and after each section.

        Mike

        Anonymous said...

        You just made my week! Only grouping by 2 columns has been killing me, and now I won't have to bug my developers, since I know they don't have time for this... THANK YOU!

        K.D.Kadyan said...

        Wow!!! you the man. Save my life.

        Kuldeep Kadyan

        David Matons said...

        Simply incredible!

        David Matons

        Zala said...

        When I click on show common control tasks I only get a 1 item in the list saying "Default to Master Page Content". Why would this be?

        Mike Smith said...

        Zala,

        > When I click on show common control tasks I only get a 1 item in the list saying "Default to Master Page Content".

        You have clicked on the popout of an asp:ContentPlaceholder, not the Data View web part. Sometimes it can be a bit of a challenge in SharePoint Designer to find these popout menus. This should also work: click any where on the page outside of the Data View, then right-click on the Data View and select "Common Control Tasks".

        Mike

        nadee said...

        thanks...I Use this for row sum it was very useful to me

        JOSHIPS said...

        I was looking to use GroupBy on Year of StartDate & EndDate in a calendar. Please let me know if it is possible.

        ---------
        I have a custom view from a calendar list (out-of-box) and it has GroupBy feature. Later this was customized using SPD.

        Calendar has various records each with Start Date & End Date. I want to show each record grouped by 'year'.

        I though of using year(start-date)?. will it work?

        Next question is with records such as 12/26/2009 to 01/06/2010 is it possible to group it in 2009 as well as 2010? This means I just can not take year(start-date) directly and will have to use some IIF conditions?

        Please suggest how can I acheive this. Typically the above record should appear in both groups (2009 and 2010).
        ------------
        Thanks in advance.

        Mike Smith said...

        JOSHIPS,

        > GroupBy on Year of StartDate & EndDate in a calendar. Please let me know if it is possible.

        Yes, it would be possible. I don't know what was done in your webpart in SPD so I can't answer how to change that. If starting from scratch I would add a calculated column to the calenader using the YEAR function (calculated column fuctions are typically the same as those used in Excel), OR do the same using XSLT in the coverted web part.

        > Next question is with records such as 12/26/2009 to 01/06/2010 is it possible to group it in 2009 as well as 2010? This means I just can not take year(start-date) directly and will have to use some IIF conditions?

        Yes to using IIF to determine which year to group it in, but I don't know of any way to show the same record twice in the XSLT output. So it will be grouped in 09 or 10, but not both.

        Mike

        Anonymous said...

        Hi,
        thank you for your post, looks like something that I want to do...
        For some reason, when I am trying to open the view in the designer, I am getting the following error message:
        "You do not have permission to do this operation."
        I checked, and I have "Design" permission to this particular site.
        Do you know what kind of permission should I ask for?

        Thanks in advance,
        Maxim.

        Mike Smith said...

        Maxim,

        Check with your system administrators or support group to see if SharePoint Designer has been locked down.

        In SharePoint Designer click the Site menu and then Contributor Settings and see what's available for Web Designers. The default is "unrestricted use of SharePoint Designer", but can be changed by your administrators.

        Mike

        Christian said...

        Does anybody else get an error when trying to edit the document properties after doing the custom view?

        You cannot edit the properties of this document whilst it is checked out or being used for editing by another user.

        once i switch back to a non custom view this warning does not appear.

        Anonymous said...

        Thanks Mike.
        Grouping on 4th column is not working for me, It works perfectly till 3rd column. Grouping shows properly on Share point designer but when viewing on the web page, the fourth column grouping is messed up.
        Any thoughts?

        Mike Smith said...

        Anon,

        > "the fourth column grouping is messed up. Any thoughts?"

        What are the data types? Anything special like date/time? Also, define "messed up". Strangely formatted? Random order?

        The Team said...

        I have issues with grouping past 3 as well. I'm grouping by 4 columns. It will by columns 1-3 correctly, but for the fourth column, it will put everything with the same column through under a single column four.
        For example, Column 3 = Fruit type and Column 4 = container. Apples and oranges will be grouped separately, but under Apple there will only be one more group Container: Bushel under which will be two items. The first one's container is Bushel, but the second is Basket.
        It seems like Sharepoint will only create 1 group heading for the fourth grouping.

        Mike Smith said...

        The most common problem I have seen it that the group fields are not set up correctly. Do not just group on "field 3". You must group on "field 1" plus "field 2" plus "field 3". Example:

        concat(@Bike_x0020_Type, " - ", @Size, " - ", @Bike_x0020_Color)

        The above groups not on just "Bike Color", but on the combination of all three fields, and using the internal names of those fields.

        Mike

        The Team said...

        Ya, that's what I thought it was at first, but it's not. I just re-did all the sort expressions that way again and it made no difference. Any other ideas? Thanks for the quick response to my first question.

        Mike Smith said...

        The Team,

        Other than that tired phrase "It works for me :-)" the only other thing to consider is the data type. The groups work on strings (text), so some data types like dates will not sort as expected when concatenated to another string. One other possibility would be Lookup field that internally stores items as list ID plus text (#2;Ohio).

        If I get some time this weekend I try to duplicate the issue.

        Mike

        The Team said...

        Hmm, now you may be on to something. The fields aren't exactly Text fields, but they are returning text. They're a custom column type that is similar to the Lookup column type. It allows us to pull from an external list on any site and filter by a column on the current site.

        The information it is looking up is a Text column though. Would Lookup columns break the sorting if they were pointed to a Text column?

        Unknown said...

        Great post with a ton of good detail! Thanks so much.

        A quick question, and please forgive if I missed this... I'm using your process to group list items by a multiple choice field. Is it possible for the list items to appear in multiple sections when grouped, versus being grouped by the common multiple choice selections applied?

        Meaning, if the item "shoe" is assigned to multiple choice categories of "dress", "black" and "leather", can shoe show up in 3 seperate category groups or just in one "Dress, black, leather" group?

        Mike Smith said...

        Dustin,

        --Is it possible for the list items to appear in multiple sections when grouped

        I don't think so, as each item is in the XML only once. But... with some creative XSLT beyond my skills, it might be possible.

        Mike

        Codename "Santosh" said...

        Thanks mate

        L01$Lan3 said...

        this is awesome - thanks so much.

        2 questions:

        -just a clarification - Do I add this everywhere I find the colspan 99 & the footer?

        -And I am trying to apply this to the absences template that comes with the fab 40 templates. I think I'm running into formatting issues with the columns that have links to other lists or people's profiles. How can I get around that? (Seems rather silly to me that the template doesn't come with subtotalling functionality, don't you? But I digress...)

        Many thanks though! I was told this couldn't be done without tons of code.

        Mike Smith said...

        Stefanie,

        -just a clarification - Do I add this everywhere I find the colspan 99 & the footer?

        Probably... any place you have a footer.

        -And I am trying to apply this to the absences template that comes with the fab 40 templates.

        Are you having problems grouping on these fields? or about the formatting of the footer? Grouping on "special" field types like Lookup and People can be a problem as they are compound fields. They have both an ID and the displayed text, and without some XSLT work may group on the ID and not the displayed text.

        Mike

        Anonymous said...

        Has anyone had difficulties with summing columns? For some reason the sum formula is taking the actual column data from the column to the right.

        e.g. I have two fields:

        Budget_x0020_Year
        Out_x0020_Year_x0020_1

        Out Year 1 is immediately to the right of Budget Year in the dataformwebpart.

        When I use formula 'sum($nodeset/@Budget_x0020_Year)' the actual result is the sum of all 'Out_x0020_Year_x0020_1' values within that group.

        I must be doing something wrong but it isn't jumping out at me after a couple of hours.

        Any assistance would be much appreciated.

        David

        Anonymous said...

        In regards to the 'column to the right post' above (by anonymous)

        I figured out what happened. One of the developers 're-named' the columns after they were initially created.

        As we know the actual column name doesn't actually change, just the label.

        Thus the problem was confusion from our end. Sorry about that.

        Great post by the way!

        David

        Anonymous said...

        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.

        Angelfish42 said...

        This has a been a very helpful post, and the first one out of all the ones I could find from grouping to totals in the footer!

        Sadly my sum function does not work still - as it is picking up the first row at the moment and not calculating everything - but I think this may be because it is a calculated column?

        Thanks!!!!!

        L01$Lan3 said...

        Anyone have any issues with Paging? If you have a lot of items in your list the 'maintain groups when paging' option is greyed out and the totals are bogus.

        HELP!!!!!!

        Unknown said...

        Hi,

        Is there anyway I can read the total value of the columns into SSRS.
        Little background: I have a custom list for survey in SP and would like to Total the column value and do further math based of the value.

        Thanks in Advance for any help.

        Mike Smith said...

        > Is there anyway I can read the total value of the columns into SSRS.

        Not that I know of. The result is just HTML in the page. I would guess that you read the data directly from the list into SSRS using the Lists web service.

        Unknown said...

        Mike,

        Great post. I used it to set up a document library.

        However, now I can't access the Version menu.

        Any suggestions?

        Thanks,

        John Samuels

        Mike Smith said...

        John,

        > now I can't access the Version menu

        Cam you tell me more? You can't see the Version History option in the item's dropdown menu? Or do you mean the View menu?

        Mike

        Unknown said...

        Mike,
        I can't see the "Version History" option in the drop-down menu.

        But...

        I created a new standard view called versioning. It makes sense. If a user wants to check a previous version, then they can go to a view that shows modified, modified by, and the version number.

        Still, if you have a technical answer, then I'm always happy to learn.

        Thanks,

        John

        Unknown said...

        Hi
        Please let me know how to make this work in SharePoint 2010 designer. I am not able to find the options you have mentioned in this article.

        Regards
        Kiran

        Mike Smith said...

        Kiran,

        On my to-do list. I'll try to add in the next week or so...

        Mike

        Mike Smith said...

        Kiran,

        Check the top of this page for a link to a new 2010 version of this article.

        Mike

        Unknown said...

        Hello,

        I'm having an odd problem, using WSS 3.0 with SharePoint Designer 2007 SP2, whenever i click on "Convert to XSLT Data view" it crashes SharePoint Designer. I've tried it on multiple PC's and multiple sites and lists and it happens every time, any idea what could be causing this?

        Problem signature:
        Problem Event Name: APPCRASH
        Application Name: SPDESIGN.EXE
        Application Version: 12.0.6423.1000
        Application Timestamp: 49b09947
        Fault Module Name: FPEDITAX.DLL
        Fault Module Version: 12.0.6423.1000
        Fault Module Timestamp: 49b098d3
        Exception Code: c0000005
        Exception Offset: 001bba31
        OS Version: 6.1.7600.2.0.0.256.48
        Locale ID: 1033

        Additional information about the problem:
        LCID: 1033
        Brand: Office12Crash
        skulcid: 1033

        Mike Smith said...

        ZGerman,

        I have not run into that problem, but here's a discussion that may help:
        http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/37736c91-0b6e-440e-87c0-cff24db07fac/

        Mike

        Matt said...

        Great post. There is just one issue which I hope you can help with. When I click on the button to expand it takes about 30-40 seconds to respond. There is about 600 items in the list. Do you have any suggestions for speeding this up? Thanks for your help either way.

        Mike Smith said...

        Matt,

        Are there 600 items in the list, or in each group? How long does it take to load the entire list in a normal list page?

        600 items in a list is fairly small, 600 per group could be quite large.

        Mike

        fernando said...

        Mike Thank you for your posting.. Its great.. I have implemented and it works as expected. However, I was wondering if you can show us how to sort the sub-groupings and if we can limit the items showing per sub-grouping... ?
        example:
        +Status
        +Green
        1
        2
        3
        +Red
        3
        2
        1
        So I have sub-grouping going diff sorting and limit the number of items in the sub-group. I hope to hear from you soon. And, Thank you again for all your help & time.

        Mike Smith said...

        fernando,

        The only way I can think of is to add an extra column that contains the sort order.

        Display Sort
        3 1
        2 2
        1 3

        Mike

        Anonymous said...

        Thank you for the post, Mike.

        I use this or a similar method, but I show only 100 items per page due to performance issues (few to several thousand items in my libraries). This causes counts and totals to apply only to the current 100-item nodeset. Mike mentions this potential problem as well, above. My question - has anyone used Mike's solution and still managed to work around this problem?
        - KP

        Anonymous said...

        Hi Mike, thank you for this post. My own requirements were simpler, I just wanted grouping by more than one level. Using ideas from your post, the solution was easy

        Thanks again, Tony (Melbourne, Australia)

        Anonymous said...

        Mike Hello, I have a lil problem and I hope you can help me.
        I have a list with a field "Status" which have the following options:
        Completed
        In Progress
        Waiting
        I need to keep track of how many records in my list are Completed, In Progress, or Waiting.
        I been working with few shapoint functions but I can't hit the nail yet and I am running out of time. Thank you so much for your prompt respond

        Mike Smith said...

        F.Mathews,

        Free consulting?! ;-)

        Just create a view on the list and group on the status field. You would then see something like:

        Status : Completed ‎(2)
        Status : Deferred ‎(1)
        Status : In Progress ‎(1)
        Status : Not Started ‎(2)

        Is this what you are looking for?

        Mike

        Anonymous said...

        Hi Mike

        My problem is when I move accross the columns none of them are "linked" to the document they all appear as text fileds only, am I doing something wrong?, I have been through all the columns available but only Doc id actually seems the link to the document, but I want the Name - linked to document column

        Niyaz said...

        HI Mike,

        Thanks for this great post. I have created 4 level stucture(4 columns grouped) , all ar collapsed by default. When I expand it will collpse to 4th level. I want it should collaps level by level. Is this be possible?

        Elena said...

        Hi Mike,
        This is really good and instructions are very easy to follow! I made it work for my list. The only problem: I can not add this view to a web part. The error shows:

        "Cannot save the property settings for this Web Part. Cannot complete this action. Please try again."

        Am I missing something?

        Mike Smith said...

        Nlyaz,

        At the top of the article is the link to the fix for this problem.

        http://techtrainingnotes.blogspot.com/2009/12/sharepoint-dataview-group-by-expand.html

        Mike Smith said...

        Elena,

        You will need to repeat the steps in the web part page using SharePoint Designer and a Data View Web Part.

        One thing you might also try is put the view page in edit mode (see trick 2 here)and then export the web part and then import the web part into the new page.

        Mike

        Eskerod said...

        Fantastic.
        Just what I needed.
        Note to my self. Always ask Google first. Do not think you are clever than Google...

        Anonymous said...

        Hello, everythink works fine but i have little problem with comma.
        If i have Totals with "," (for example 30,99)the result will be "NaN".
        The solution ist a "translate" in the formula.
        For example:


        But how i combine this formular with:



        to get the right formated total?
        Thanks a lot for your answer.

        Mike Smith said...

        Anonymous,

        No formulas were displayed in your post. Can you repost your question? If you included HTML tags change all < to &lt; and > to &gt

        Anonymous said...

        Great guide. I don't know if it worked yet because it's still rendering in SPD. Maybe place that tip about using sample data a little higher in the tutorial? :)

        Anonymous said...

        Hi there Mike,

        Thanks a lot for this guide I'm doing a lot of stuff but I found a problem. I did group the columns and got average of all of them, but now I want to get the lowest average of a column. Let me try to draw the situation:

        (A) (Here lowest average)
        (A-B1) (average)
        (A-B1-C1)
        (row1)
        (row2)
        (row3)
        (A-B2) (average)
        (A-B2-C1)
        (row1)
        (row2)
        (row3)
        (A-B2-C2)
        (row1)
        (row2)

        So, I have so far the average of all columns but I want to show the lowest average of the descedants (or children) in this case of and . I have tried different codes in xpath but didnt work...lack of experience...
        Thanks in advance.

        Mike Smith said...

        > lowest average

        Sorry, but I don't think I have an answer for that one! Maybe post the question in the MSDN forums:
        http://social.technet.microsoft.com/Forums/en-US/sharepoint2010customization/threads/

        Include what you have done so far with the XSLT.

        Mike

        Note to spammers!

        Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.