Thursday, December 27, 2012

Count/sum rows grouped by in XSLT Dataview WebPart

Scenario: Converted a list view to XSLT View using SharePoint Designer and grouped the list by 2 columns "Created By"(built in people and column group) and "WeekEnding" (date field).

Requirement: To display the count of each item that is grouped by "Week Ending" and "Created By" column. Also sum of "% of Week" column with the same condition.

Grouping in XSLT Dataview WebPart


1. Declare param <xsl:param name="usernamegroup"/> after the following lines <xsl:template name="dvt_1.groupheader0"> and <xsl:template name="dvt_1.groupheader1">

2. Search for the following line and add xsl:with-param after it
<xsl:call-template name="dvt_1.groupheader1"> 

<xsl:with-param name="usernamegroup" select="substring-after(substring-before(substring-after(@Author, 'ID='), '&lt;'), '&gt;')"/>
I've added it in 2 locations.

3. Added the following line of code to display the count in header row(refer screenshot)
<xsl:value-of select="count(/dsQueryResponse/Rows/Row[(@WeekEnding=$fieldvalue) and (substring-after(substring-before(substring-after(@Author, 'ID='), '&lt;'), '&gt;')=$usernamegroup)]/@Week_Ending)">

4. Added the following line of code to display the sum in the header row(refer screenshot)
<xsl:value-of select="format-number(string(sum(/dsQueryResponse/Rows/Row[(@WeekEnding=$fieldvalue) and (substring-after(substring-before(substring-after(@Author, 'ID='), '&lt;'), '&gt;')=$usernamegroup)]/@PercentageOfWeek)),'#,##0%;-#')" />