Supporters of End User
Web

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

Item is currently unrated. Press SHIFT+ENTER to rate this item.1 star selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+ESCAPE to leave rating submit mode.2 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.3 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.4 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.5 stars selected. Press SHIFT+ENTER to submit. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.
Categories:Extending the DVWP; Site Manager/Power User; SharePoint Designer; Data View Web Part; Content Editor Web Part; Javascript and jQuery

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
  2. 2010-08-30-ExtendingTheDVWPLayoutEnhancement-Part33-01.png
    Click Sort and Group near the top of Common Data View Tasks
  3. Choose the fields you want to use for grouping and/or sorting and click Add > >
  4. 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.
  5. 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.
  6. 2010-08-30-ExtendingTheDVWPLayoutEnhancement-Part33-04.png
    Option 1: Showing column totals per group
    2010-08-30-ExtendingTheDVWPLayoutEnhancement-Part33-05.png
    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":

 2010-08-30-ExtendingTheDVWPLayoutEnhancement-Part33-06.png

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.
  2. 2010-08-30-ExtendingTheDVWPLayoutEnhancement-Part33-07.png
    Click on the Group name in the Design pane to find the corresponding XSLT in the Code pane
  3. Delete the first four lines shown in the figure above.
  4.     <b>
            <xsl:value-of select="$fieldtitle" />
        </b>
        <xsl:if test="$fieldtitle">: </xsl:if>
    

    Delete this part

  5. Click in the Design pane
  6.  2010-08-30-ExtendingTheDVWPLayoutEnhancement-Part33-08.png

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.  2010-08-30-ExtendingTheDVWPLayoutEnhancement-Part33-09.png
  3. 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:
  4.     <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>
    
  5. 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
  2. 2010-08-30-ExtendingTheDVWPLayoutEnhancement-Part33-11.png
    We're going to put an FTE subtotal here...
  3. In the Code pane, this td tag will be highlighted. Replace:
  4.     <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.

  5. 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.
  6. 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.

  7. 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:
  8. 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.
  3.     <tr valign="top" class="ms-alternating">
    </tr>
    
  4. 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:
  5.     <td nowrap="" style="border-top:medium black double">
    	</td>
    
  6. 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.
  7. This time, our sum() will come from $Rows, rather than $nodeset:
  8.     <xsl:value-of select="format-number(sum($Rows/@FTE),'#0.00')"/>
    
  9. 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:
  10.     <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.

 2010-08-30-ExtendingTheDVWPLayoutEnhancement-Part33-12.png

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

Comments

SharePoint Consultant

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

A lot of nice detail! Excellent post.

Stacy
SharePoint Developer

Posted 02-Sep-2010 by SharePoint Consultant
SharePoint Consultant

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

A lot of nice detail! Excellent post.

Stacy
SharePoint Developer

Posted 02-Sep-2010 by SharePoint Consultant
Jim Bob Howard

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

Thanks, Stacy!

Blessings,
Jim Bob

Posted 03-Sep-2010 by Jim Bob Howard
Jim Bob Howard

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

Thanks, Stacy!

Blessings,
Jim Bob

Posted 03-Sep-2010 by Jim Bob Howard
SharePoint: Extending the DVWP – Part 35: Putting it All Together | EndUserSharePoint.com

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

[...] to make it look even nicer, we’ll massage the way total and subtotal rows look and replace some of our form action links with [...]

Posted 14-Sep-2010 by SharePoint: Extending the DVWP – Part 35: Putting it All Together | EndUserSharePoint.com
SharePoint: Extending the DVWP – Part 35: Putting it All Together | EndUserSharePoint.com

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

[...] to make it look even nicer, we’ll massage the way total and subtotal rows look and replace some of our form action links with [...]

Posted 14-Sep-2010 by SharePoint: Extending the DVWP – Part 35: Putting it All Together | EndUserSharePoint.com
ssvv

Brilliant

Absolutely brilliant. I have been searching for two weeks...this is the best explanation I have seen.

Posted 07-Mar-2012 by ssvv
ssvv

DVWP Group Count Error

I followed the steps mentioned above to group and generate the sum columns per group.

I have come across a small problem. For example; I have total of 5 entries in the my DVWP in the following order:

• 1 entry for 1/15/2012
• 2 entries for 1/31/2012
• 2 entries for 2/2/2012

The count and the sum is correct for the group 2/2/2012.
However the web part is displaying ‘0’ for the count and sum for groups 1/15/2012 and 1/31/2012.
 
The count for the total groups shows correct (5).
 
I don’t understand why. I would really appreciate your help.

Posted 07-Mar-2012 by ssvv

Notify me of comments to this article

E-mail:
   

Add Comment

Title:

 
Comment:
Email:

   


Name:

 
Url: