SharePoint: Extending the DVWP – Part 33: Modifying Total and Subtotal Row Layouts in DVWP

Author: Jim Bob Howard

We’re in the home stretch… just a couple more tweaks to our DVWP to make it work a little better and then we’ll put all the pieces together.

Totally Awesome

To really make this worthwhile, we should do some grouping within our filtered views, along with some totals and sub-totals.

First, let’s turn on the grouping and see what we get OOTB.

1. In SharePoint Designer (SPD), hover over your DVWP in the Design pane and click the chevron (right arrow), then click on Sort and Group

Click Sort and Group near the top of Common Data View Tasks

2. Choose the fields you want to use for grouping and/or sorting and click Add > >

Anything you choose in the Available fields and Add > > to the Sort order will be sorted according to the Sort Properties.


All choices are sorted…


…but not all choices are grouped.

3. For our purposes, we’re already filtering the DVWP by Location; grouping by it would be redundant. So we want to go down to the next level and group by Group. And we’ll sort by Worker name, per my requirements. It might instead make sense to sort by Position, depending on how the managers are most comfortable working with the data.

Option 1: Showing column totals per group


Option 2: Showing group footer


It doesn’t really matter which option you choose because we’re going to be changing it in just a bit anyway. That said, I’ll be working from Option 2.

So, here’s our "before" and "after":

For the rest of this article, we’ll take care of the Group headers and footers (1-3).

Modifying Group Headers and Footers

The lowest-hanging fruit on this tree is (1) —the Group header. With only one grouping, it’s pretty redundant (and ugly) to indicate that this section is grouped in a Group, so let’s get rid of that text and just display the name of the Group.

1. In SPD split view, click on any one of the Group headers in the Design pane to bring up the stylesheet in the Code pane.

Click on the Group name in the Design pane to find the corresponding XSLT in the Code pane

2. Delete the first four lines shown in the figure above.

    <b>
        <xsl:value-of select="$fieldtitle" />
    </b>
    <xsl:if test="$fieldtitle">: </xsl:if>

Delete this part

3. Click in the Design pane

Subtotal Count

At first blush, it looks like we might not have to do anything to (2). And that’s true, if you like the way it looks. I want it to stand out a little more.

1. In the SPD split view, click on any of the Group footers


2. I like to have the subtotals a little darker and use the same background colors that are already in the grid. So, for every TD in this TR, I changed the class from ms-vh to ms-gb2 ms-alternating. I also prefer to have the label left-justified with the column title and data:

    <td class="ms-gb2 ms-alternating" nowrap="">
        Count : <xsl:value-of select="count($nodeset)" /></td>
    <td class="ms-gb2 ms-alternating" nowrap="">
        <xsl:text disable-output-escaping="yes" ddwrt:nbsp-preserve="yes">&amp;nbsp;</xsl:text>
    </td>
    <td class="ms-gb2 ms-alternating" nowrap="">
        <xsl:text disable-output-escaping="yes" ddwrt:nbsp-preserve="yes">&amp;nbsp;</xsl:text>
</td>

3. Click in the Design for the reveal.

Adding a Subtotal Sum

On the same line with the count, I also need a calculation (3) of all the FTE hours for that Group.

1. Click on the empty cell beneath the last row in the Group

We’re going to put an FTE subtotal here…

2. In the Code pane, this td tag will be highlighted. Replace…

    <xsl:text disable-output-escaping="yes" ddwrt:nbsp-preserve="yes">&amp;nbsp;</xsl:text>

…with…

    <xsl:value-of select="format-number(sum($nodeset/@FTE),'#0.00')"/>

This will give us a sum() of all the @FTE amounts in this nodeset, which at this point are just the ones in this Group.

3. To give it a meaningful title while keeping the subtotal lined up with the rest of the FTE amounts, we’ll need to put the title in the next cell to the left. Click it in Design to highlight it in Code.

This time, we’ll replace…

    <xsl:text disable-output-escaping="yes" ddwrt:nbsp-preserve="yes">&amp;nbsp;</xsl:text>

…with…

    <nobr>Total <xsl:value-of select="$nodeset/@Group" />  FTE :</nobr>

Here we’re just inserting the Group name for this nodeset.

4. The only problem at this point is that putting the text in that td causes the smaller column of Work Shift to be artificially wide. So, let’s extend the subtitle into the previous td, too:

Simply delete the preceding td and give this one a colspan of 2.

    <td class="ms-gb2 ms-alternating" nowrap="" colspan="2" align="right"></td>

For aesthetic purposes, we’ve also right-aligned the td.

Page Totals

For the page totals (4 and 5), it would be great if those stood out a bit more, even if they’re just bigger. We’ll use the accounting style of a double line to show page totals.

1. Click on the Count cell (4), as before.
2. First, we’ll move the ms-alternating class to the tr so we don’t have to keep repeating it.

    <tr valign="top" class="ms-alternating">
</tr>

3. We’ll remove the class attribute for all the td’s on this row. But, we’ll also add in a border-top to create the double-line accounting style:

    <td nowrap="" style="border-top:medium black double">
	</td>

4. SharePoint adds an xsl:text tag to put a space before the Count title. I didn’t like it, so I removed it. In the rest of the TD’s—if they’re empty ones, the space is required to make the border-top appear.
5. This time, our sum() will come from $Rows, rather than $nodeset:

    <xsl:value-of select="format-number(sum($Rows/@FTE),'#0.00')"/>

6. The title will need to extend into the previous cell again. This time, it will be a total for the Location, rather than the Group:

    <td style="border-top:medium black double" nowrap="" colspan="2" align="right"> Total <xsl:value-of select="$Rows/@LocDept" /> FTE :</td>

Since we’re going to use this same DVWP template for all of our Locations, it’s probably easier to "look up" the name of the Location. If you’ve used abbreviations like I have, you may want to type out the long name of the Location for this DVWP, since there’s only one per webpart.

Almost there

It’s looking better already.

Next time: One last tweak we’ll do next time: Icons for the Form action links.

Author: Jim Bob Howard

Jim Bob Howard is a web designer / webmaster in the healthcare industry. He has been working with SharePoint since March 2009 and enjoys sharing what he has learned. He is a moderator and frequent contributor to Stump the Panel, and answers SharePoint questions on Twitter (@jbhoward) and via email ([email protected]).