Unlocking the Mysteries of Data View Web Part XSL Tags – Part 18 – Miscellaneous – Some Math / Number Functions
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 1: Overview
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 2 – <xsl:template>
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 3 – <xsl:call-template>
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 4 – <xsl:with-param>
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 5 – <xsl:param>
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 6 – <xsl:variable>
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 7 – <xsl:for-each>
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 8 – <xsl:sort>
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 9 – <xsl:if>
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 10 – <xsl:choose>
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 11 – <xsl:value-of>
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 12 – Miscellaneous: Person or Group Columns
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 13 – Miscellaneous: String Functions
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 14 – Miscellaneous: ddwrt Namespace Functions
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 15 – Miscellaneous: Field / Node Functions
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 16 – <xsl:attribute>
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 17 – <xsl:comment> and <xsl:text>
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 18 – Miscellaneous – Some Math / Number Functions
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 19 – Miscellaneous – More Math / Number Functions
- Unlocking the Mysteries of Data View Web Part XSL Tags – Part 20 – <xsl:import>
- Unlocking the Mysteries of the SharePoint Data View Web Part XSL Tags eBook Now Available
Cross-posted from EndUserSharePoint.com…
I’m going to go back to the XPath Math / Number functions for this article to cover a few of them. Most of these functions are fairly intuitive, but I’m a math guy. (Yup, I even majored in it in college back during the Renaissance.) However, I find that the “documentation” in the XPath Expression Builder is a bit obtuse for these functions so I’m just going to run through some of them “in my own words”. If I state the obvious or repeat the descriptions in the XPath Expression Builder, please forgive me. Your refund is in the mail.
average()
average() computes the average of a set of numbers. You’ll generally use this function in the body template because you need a nodeset (multiple items) for an average to make sense. This is analogous to the way you’ll typically use sum. So, you go into the XPath Expression Builder, and you select average, and you see:
sum() div count()
What the heck? In fact, there is no average function. Tricked you, didn’t we? (SharePoint Designer and I like to pal around on stuff like this.) You need to insert the column you want the average of in both the sum() and the count() functions, like this:
<xsl:value-of select="sum(/dsQueryResponse/Rows/Row/@Potential_x0020_Value) div count(/dsQueryResponse/Rows/Row/@Potential_x0020_Value)"/>
This computes the average just like we all learned in grade school, but you’ve got to do a little more work. Because I’m putting this into the dvt_1.body function, I need to specify the full XPath to the column. Here’s where this fits into the dvt_1.body template to make it useful:
<xsl:template name="dvt_1.body">
<xsl:param name="Rows"/>
<xsl:for-each select="$Rows" >
<xsl:call-template name="dvt_1.rowview"/>
</xsl:for-each>
<tr>
<td>
</td>
<td class="ms-vb" align="right" colspan="2">
<b><xsl:value-of select="format-number(sum(/dsQueryResponse/Rows/Row/@Potential_x0020_Value) div count(/dsQueryResponse/Rows/Row/@Potential_x0020_Value), "$#,##0.00;-$#,##0.00")"/></b>
</td>
</tr>
</xsl:template>
<xsl:template name="dvt_1.rowview">
<tr>
<xsl:if test="position() mod 2 = 1">
<xsl:attribute name="class">ms-alternating</xsl:attribute>
</xsl:if>
<td class="ms-vb">
<xsl:value-of select="@Title"/>
</td>
<td class="ms-vb" align="right">
<xsl:value-of select="format-number(@Potential_x0020_Value, "$#,##0.00;-$#,##0.00")"/>
</td>
</tr>
</xsl:template>
After I’ve called the dvt_1.rowview template “for-each” item, I am writing out another table row which contains the average. Note that I’ve added the format-number function around the individual values and the average so that they look more presentable.
format-number()
Here’s the description of format-number() directly from the XPath Expression Builder:
format-number() simply takes a textual representation of a number and formats it using the format-pattern which you provide.
String bean man again! When you open the XPath Expression Builder you can never quite read the descriptions of the functions very well. The trick is to manually drag the window larger:
Min and Max
These twins are actually members of the good old ddwrt family. You need to provide them with a nodeset, just like sum and count:
<xsl:value-of select="format-number(ddwrt:Max(/dsQueryResponse/Rows/Row/@Potential_x0020_Value), "$#,##0.00;-$#,##0.00")"/>
Replacing my average calculation with ddwrt:Max:
Operators
The math operators aren’t functions, but they are worth listing out:
· + – the plus sign, which adds two numbers together
· – – the minus sign, which subtracts one number from another
· * – the asterisk, which is used to multiply two numbers together
· div – div, which divides the first number by the second
That last one threw you, didn’t it? You’re probably used to using the slash character (/) for division. However, in XSL and XPath, the slash means something else entirely. The slash is how you sort of “parse” the nodeset, as you can see in my use of the full XPath for Potential Value above: /dsQueryResponse/Rows/Row/@Potential_x0020_Value
These are a few of the quirky Math / Number functions which are available in XPath. I’ll run through the rest of the Math / Number functions in the next article.
Great post!
I’m trying to divide the sum of two colums. And i’m just stuck. I need to sum all the values of column1 and column2 and then i need to make something like (column1.sum) div (column2.sum).
Do you have a suggestion on how to do this?
I’ve tried:
No luck. I get a “NaN” as the result.
/Ulrich
Ulrich:
It’ll be something like this:
Sum needs a nodeset upon which to operate.
M.
I am trying to take the average of a column just like you demonstrated above “sum() div count()” but I dont want to count rows with fields that are kept empty. How can I achieve this?
Ammar:
If you look at the first example I provide, both the sum() and count() functions take a nodeset as their arguments:
If you want to only include items with a non-empty or non-zero value, you can filter your nodesets, something like this:
M.
I am trying to sum a column and I am using
This actually displays the total in sharepoint designer but in the broser it is 0
Any ideas?
John:
It looks like the code you posted was stripped out of your comment, so I can’t tell what you’re doing. It may be that the context for your nodeset isn’t appropriate.
M.
Sorry, let’s try that again. I have taken the openning & closing tags off so hopeully the code should come through in the post.
xsl:value-of select=”format-number(sum(/dsQueryResponse/Rows/Row/@RequiredPoints),’#,##0′)”
As I said it actually totals the column in sharepoint designer, but in the browser it is 0.
John:
That should work, and I just validated it in a DVWP, albeit with a different column. What do you get if you remove the format-number?
Also, where in the XSL do you have this? Since you are summing across a nodeset, it would generally be in the dvt_1.body template.
M.
I tested without the format-number same result.
There is no dvt_1.body template.
I am using sharepoint 2010, when I inserted the data view web part of my list I inserted a row at the bottom of the list items and added my code within the td tag of the row.
I seem to remember using something similar in sharepoint 2007 and it worked no problems, could it be that the data view web part is different in 2010?
The DVWP is pretty much trhe same in 2010, though there are a few differences in the XSL which SPD2010 generates.
My first test was in 2007 (I already had a VM open). I just tried it in a 2010 VM and it worked exactly the same. Different list, different column, but the same syntax works.
M.
Thanks Mark for testing.
I don’t get why it works in designer but not the browser. Is it possible the problem is where I am putting the code?
Where did you add the code in your test?
I tried a couple of different locations. If it’s in the dvt_1.rowview template, then the value should be displayed on every row; if in the dvt_1.body template, then just once above or below the rows. Want to send me your XSL? marc dot anderson [at] sympraxisconsulting dot com.
M.
HI
I am trying to show serail number in data view. Can you pleae guide me to get this.
FYI – Enabled paging.
Thnaks
Sai
Sai:
If you use position(), you can display the item’s position in the rowset. If you’ve enabled paging, that gets far more tticky. I believe that you will need to do the math based on the page number and position within the page.
M.
Hay Hi,
Thanks for the quick response.
Can you please help me to define this logic. Actually I am totally new to sahrepoint designer.
Thanks
Sai
Sai:
I can’t write the code for you, if that’s what you are asking. You’ll need to come up with it based on the specifics of your particular business requirements and setup.
M.
Hi
Its possible to sum or count how many visible groups do i have in a list?