Two Level Grouping in XSLT 1.0 in SharePoint Content Query Webpart

Category: XSLT1.0 & SharePoint's Content Query WebPart

Problem/ Scenario: 

We have to display data in a Content Query WebPart from a SharePoint list & apply two level grouping to it. Below is an example of two level grouping.

We have three columns in the list namely FoundationName, CauseName & FundName. We need to group the data first by FoundationName & then by Causename. Below is an example of how the data should look like in the end.

Foundation1

            Cause1

                        Fund1

                        Fund2

                        Fund3

            Cause2

                        Fund4

Foundation2

            Cause1

                        Fund5

            Cause3

                        Fund6

Solution:

Step 1:

In order to understand how SharePoint renders the Content Query Web Part, let's look at the Raw Xml

Refer to: http://www.sharepointblogs.com/radi/archive/2009/03/17/content-query-web-part-getting-a-full-dump-of-the-raw-xml.aspx

Below is the raw Xml I got when i connected to my list:

<dsQueryResponse>

    <Rows>

      <Row ListId="40EB7A9E-6F40-479B-B510-D321E60A47E7" WebId="72FAC64E-A65A-4F66-81CA-0240777D7FB2" ID="2" _x007B_fa564e0f_="Brain" FileRef="Site1/Lists/CustomList/2_.000" _x002D_9fedbadb6ce1_x007D_="2" Modified="2009-06-22 16:58:20" Author="System Account" Editor="System Account" Created="2009-06-17 18:01:40" PublishingRollupImage="" _Level="1" Comments="" Title="Brain" Description="<div>this is leader text for Brain</div>" NewFeatured="Featured" SiteUrl="http://Server/site1" ContextScriptID="ctxg_588a882e_6730_4693_b3f0_2bbf668aa8021" ItemIndex="2" ItemContentType="" ItemContentTypeID="" ItemApplicationName="" ItemOpenMethod="" ItemCheckedOut="0" ItemCheckedOutUserID="" ItemIconUrl="blank.GIF" ItemExtension="" DocumentFolder="Site1/Lists/CustomList" ItemUrl="/Site1/Lists/CustomList/2_.000" DocType="" CopySource="" ModerationStatus="0" DocEditCommand="" LinkUrl="http://Server/site1/Lists/CustomList /2_.000" PubDate="Mon, 22 Jun 2009 16:58:20 GMT" ImageUrl="" ImageUrlAltText="" Style="CustomStyle1 " GroupStyle="DefaultHeader" __begincolumn="True" __begingroup="False" />

  </dsQueryResponse>

This will give you an idea on how SharePoint renders XML of Content Query Web part

Step 2:

I am assuming here that you know about ContentQuerymain.xsl, Header.xsl & ItemStyles.xsl. Refer to http://msdn.microsoft.com/en-us/library/bb447557.aspx

Since we have to group our data, we will be coding in ContentQueryMain.xsl. (Note: It is a good practice to create your own custom ContentQueryMain file & make your CQWP reference to this file.)

In ContentQueryMain.xsl, replace

<xsl:template match="/">

    <xsl:call-template name="OuterTemplate" />

  </xsl:template>

with

<xsl:template match="/">

    <xsl:call-template name="OurCustomTemplate" />

  </xsl:template>

Step 3:

We will be using Keys for creating two level grouping. Two keys in our case:

  <xsl:key name="foundationsKey" match="Row" use="@AssociatedFoundation" />

  <xsl:key name="associatedCause" match="Row" use="@AssociatedCause" />

Put these keys just before the code mentioned in Step 2:  in ContentQueryMain.xsl

Here @AssociatedFoundation & @AssociatedCause are internal column names which will be used for grouping

Step 4:

Creating the custom template:
  <xsl:template name=" OurCustomTemplate ">

<xsl:for-each select="/dsQueryResponse/Rows/Row[generate-id(.)=generate-id(key('foundationsKey', @AssociatedFoundation))]">

/*Going upto the <Row> node in the xml & getting Foundation Name*/

<div>

<xsl:value-of select="@AssociatedFoundation" />

</div>

/*Display the Foundation name*/

        <xsl:variable name="thisFoundation" select="@AssociatedFoundation"/>

/*Storing Foundation name in a variable 'thisFoundation'  so that we can use it in our inner loop*/

        <xsl:for-each select="../Row[generate-id() = generate-id(key('associatedCause', @AssociatedCause)[@AssociatedFoundation = $thisFoundation][1])]">

/*For @AssociatedFoundation = the current foundation name, get the associated causes & store in @AssociatedCause */

          <xsl:sort select="@AssociatedCause" /> /*Sort based on AssociatedCause*/

                                                <xsl:for-each select="key('associatedCause', @AssociatedCause)[@AssociatedFoundation = $thisFoundation]">

                                            <xsl:if test="position() = 1">

                                                                                    <xsl:value-of select="@AssociatedCause" />

/*Display the cause when position()=1 else it will display CauseName for every Fund Name*/                       

                    </xsl:if>

                                                        <xsl:value-of select="@FundName" />

/*For the particular foundation name & cause name, display the corresponding Fund Name*/

                                                                                                                    

                </xsl:for-each>

                                    </xsl:for-each>

            </xsl:for-each>

  </xsl:template>

Final Notes: Similar way multi level grouping can be done