Showing Subtotals in a DVWP, Sorted by the Subtotals – Part 2

In a post the other day, I showed some code to show subtotals for a given column’s values, with the results sorted by the subtotal, descending.  This worked great for most types of columns as the groups, but Person/Group columns can be a bit problematic.  This is due to the fact that there is a part of the HTML which makes up the contents of those column values which is unique per display of the value.  This is why grouping by Person/Group columns never works the way anyone wants.

The two bits of HTML below both represent the same person, but they have different IDs for the presence indicator image (imn_2085 and imn_2076), so they are not actually the same:

<nobr><span><A HREF="/Intranet/TestSite/_layouts/userdisp.aspx?ID=7">Peter J Sterpe</A><img border="0" height="1" width="3" src="/_layouts/images/blank.gif"/><a href='javascript:' onclick='IMNImageOnClick();return false;' class='ms-imnlink'><img name='imnmark' title='' border='0' height='12' width='12' src='/_layouts/images/blank.gif' alt='No presence information' sip='peter.sterpe@sympraxisconsulting.com' id='<strong>imn_2085</strong>,type=smtp'/></a></span></nobr>
<nobr><span><A HREF="/Intranet/TestSite/_layouts/userdisp.aspx?ID=7">Peter J Sterpe</A><img border="0" height="1" width="3" src="/_layouts/images/blank.gif"/><a href='javascript:' onclick='IMNImageOnClick();return false;' class='ms-imnlink'><img name='imnmark' title='' border='0' height='12' width='12' src='/_layouts/images/blank.gif' alt='No presence information' sip='peter.sterpe@sympraxisconsulting.com' id='<strong>imn_2076</strong>,type=smtp'/></a></span></nobr>

But all is not lost!  What you need to do is simply get at the unique identifier for each person.  You can either use the person’s name or, probably more safely, the ID of the user item in the User Information List (the ID=7 above).  The trick is to substring the ID out correctly in all of your formulas:

<xsl:template name="dvt_1.body">
    <xsl:param name="Rows"/>
    <xsl:for-each select="$Rows">
        <xsl:sort select="sum($Rows[substring-before(substring-after(@Nominee, '?ID='), '&quot;') =
                    substring-before(substring-after(current()/@Nominee, '?ID='), '&quot;')]/@Amount)" data-type="number" order="descending" />
        <xsl:call-template name="dvt_1.rowview">
                        <xsl:with-param name="Rows" select="$Rows"/>
        </xsl:call-template>
    </xsl:for-each>
</xsl:template>
<xsl:template name="dvt_1.rowview">
    <xsl:param name="Rows"/>
    <xsl:variable name="NewUserID" select="ddwrt:NameChanged(string(substring-before(substring-after(@Nominee, '?ID='), '&quot;')), 0)"/> 
    <xsl:if test="string-length($NewUserID) > 0">
      <tr>
        <td class="ms-vb">
          <xsl:value-of select="@Nominee" disable-output-escaping="yes"/>
        </td>
        <td class="ms-vb">
          <xsl:value-of select="sum($Rows[substring-before(substring-after(@Nominee, '?ID='), '&quot;') =
            substring-before(substring-after(current()/@Nominee, '?ID='), '&quot;')]/@Amount)"/>
        </td>
      </tr>
    </xsl:if>
</xsl:template>

19 Comments

  1. This has been bugging me for the past few weeks, go figure when I am not looking for a solution one presents itself. This may have saved my sanity.

    Thanks

    Reply
  2. HI Marc
    One Question here did u try this with Paging in DVWP.I mean eliminating duplicate in DVWP.
    I was doing exactly same thing from one of post its works fine but when i do paging its not working they way it should.

    Ron

    Reply
      • OK Let me give Example that i am working on.
        I have Two list with following structure

        Employee Role Project
        Ron Dev XYZ
        Ron Tester ABC
        Ron Lead SDF
        Ron Dev TRF

        Role Course
        Dev ABC
        Dev xyz
        Dev PQR and also for other role.

        Result List
        Employee Role Course

        AS you can see Ron is Dev on Two different project so i want to display only once.

        so i have modified body template to short as per Role and then call rowview template.
        its works fine if i dont cross item limit in DVWP but once its then i still showing 4 records.

        Reply
        • Looks like this is the same thing you’ve been writing about on several forums? Trust me, this can work the way you want it to, but you have to have reliable data and the right XSL to get it to work. The number of items will not make a difference, either. Keep trying: like I said, it *can* work.

          M.

          Reply
  3. HEY MARC,
    I WAS PLAYING WITH THIS THING AND I FOUND IF I LIMIT ITEM TO 3 THEN SORTING IS NOT WORKING.
    I HAVE TOTAL 5 ITEMS.

    ONCE I DO ITEM LIMIT MORE THEN 5 ITS WORKING FINE

    Reply
  4. HI Marc,
    I have Employee Name in DVWP now i would like to sort it By Employee Name but as you know there is problem with that as Employee Name value is different and we have to get ID or Employee Name from string.

    I was doing this <xsl:sort select=”$Rows[substring-before(substring-after(@Employee, ‘?ID=’), ‘"’)]” data-type=”number” order=”ascending” > in dvt_1.body body template inside foreach loop but its not working.

    can you please advise me on this

    Ron

    Reply
  5. Yes Marc I did
    Its Returning Number and i also try to by Name instead of Number like this
    <xsl:sort select=”substring-after(substring-before($Rows/@Employee,'</A>’),’">’)” order=”ascending”>

    Above statement Return Employee Name i Used same Statement in Rowview Template to Display Employee Name
    like this

    <td class=”ms-vb”>
    <!– –>
    <xsl:value-of select=”substring-after(substring-before(@Employee,'</A>’),’">’)”>

    Its working Here but when i try for sorting its not doing sorting

    Reply

Have a thought or opinion?