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='[email protected]' 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='[email protected]' 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='), '"') =
substring-before(substring-after(current()/@Nominee, '?ID='), '"')]/@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='), '"')), 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='), '"') =
substring-before(substring-after(current()/@Nominee, '?ID='), '"')]/@Amount)"/>
</td>
</tr>
</xsl:if>
</xsl:template>
Yippy!!! That did man. You are amazing. I need to start learning some XSLT stuff.
Thank You.
Ultra
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
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
Ron:
I’m not sure I understand your question. What’s happening with the paging?
M.
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.
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.
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
YA MARC ITS WORKING FINE BUT THE PAGING NAVIGATION IN THE FOOTER IS NOT DISPLAYING CORRECTLY.
THATS ONLY ISSUE
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
What’s happening? Have you tested to see what the select statement is returning?
M.
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
Well, if it’s returning the name in the rowview, it ought to be returning the name in the sort as well. Can you discern what impact the xsl:sort is having on the items? Are the items being sorted, but just in the wrong way? Or are they not sorting at all?
M.
HI Marc,
Some of Items are sorted and some of them not.
Ron, I think it’s just debugging time for you again. It’s really hard to just “give you the answer” on this stuff.
M.
I Know Marc,
Anyway thanks for Help.
Can you advise me how can i debug in XSLT.
Ron
Output values to the page, etc. Same as anything else!
M.
How do you use all this to filter on a Person field?
Ignore last question. Figured it out.