multiplying columns in a rows then getting sum

Condor76 · May 9th, 2007, 05:06 PM

Hi all,

I have created a dataview and now have to multiply a couple of columns and then get the a sum total.

Can anyone offer an example of how to go about doing this via for-each in xslt.

I'm doing this using Sharepoint Designer. Thank you in advance!

mhkay · May 9th, 2007, 05:50 PM

In XSLT 2.0 you can do

sum(//item/(price*quantity))

It's much harder in 1.0. You can only use sum() over a node-set. 1.0 solutions include:

1. 2-phase transformation, first create a tree with the computed products in the nodes, then sum over the nodes

2. recursive template scanning the items and adding each product to a running total passed as a parameter

3. extension function such as saxon:sum()

4. higher-order functions, Dimitre Novatchev's FXSL library

Michael Kay
http://www.saxonica.com/
Author, XSLT Programmer's Reference and XPath 2.0 Programmer's Reference

Condor76 · May 9th, 2007, 05:58 PM

Thank you mhkay! I was able to get the sum of the column via:

format-number(sum($nodeset/@Apr_Cost)+sum($nodeset/@May_Cost)+sum($nodeset/@Jun_Cost), '#,##0.00;-#,##0.00')

This worked OK. But it seems that I will need some type of recursive function to multiply this against another column and then get the total sum. This is when I thought to use for-each. Does this make sense to you? I'm not so sure how to go about this though. Any suggestions would be greatly appreciated!!

- Andres

mhkay · May 9th, 2007, 06:07 PM

>Any suggestions would be greatly appreciated!!

Buy my book.

Michael Kay
http://www.saxonica.com/
Author, XSLT Programmer's Reference and XPath 2.0 Programmer's Reference

tripecac · May 10th, 2007, 10:55 AM

Which book would be more appropriate for him:

1) XSLT 1.0
2) XSLT 2.0
3) XPath 2.0

mhkay · May 10th, 2007, 11:11 AM

Which book would be more appropriate for him:

1) XSLT 1.0
2) XSLT 2.0
3) XPath 2.0

Rather depends on (a) how long you expect still to be working on 1.0, and (b) whether you're interested in learning about 2.0 even if you're still stuck with 1.0 for a while.

Michael Kay
http://www.saxonica.com/
Author, XSLT Programmer's Reference and XPath 2.0 Programmer's Reference

Condor76 · May 10th, 2007, 11:52 AM

Well, I went bought this morning XSLT 2.0. I'm currently working creating customed dataviews (source MOSS 2007 lists) using Sharepoint Designer. Do you think this book will work for me. Thank you.

mhkay · May 10th, 2007, 12:18 PM

>Do you think this book will work for me.

I tried hard when writing the book to make it clear whenever I was talking about a feature that was new in XSLT 2.0, and also to mention some of the coding techniques needed in XSLT 1.0 even when they have become obsolete in 2.0. So I hope you'll find it useful. But obviously you have to read carefully when using it with a 1.0 processor. (And, of course, it's frustrating to be constantly reminded that your job would be so much easier if you were using 2.0...)

Michael Kay
http://www.saxonica.com/
Author, XSLT Programmer's Reference and XPath 2.0 Programmer's Reference

Condor76 · May 10th, 2007, 05:34 PM

Thank you Michael for all your help and for writing this book. I have started to read it today and so far I have enjoyed it much.

I'm still struggling with my original task. I'm trying to add three columns and then multiply this by a fourth column. I have attached a copy of my code below.

Code:

<xsl:for-each select="$nodeset">
    <xsl:value-of select="@Column1" /><xsl:text></xsl:text>
    <xsl:value-of select="@Column2" /><xsl:text></xsl:text>
    <xsl:value-of select="@Column3" /><xsl:text></xsl:text>
    <xsl:value-of select="@Probability" /><xsl:text></xsl:text>
</xsl:for-each>
<xsl:value-of select="format-number(sum(@Column1+@Column2+@Column3)*@Probability, '#,##0.00;-#,##0.00')" />    
<xsl:text disable-output-escaping="yes" ddwrt:nbsp-preserve="yes" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime">&amp;nbsp;</xsl:text>

However, I'm getting the following error message: "Argument 1 must return a node-set." Could you please direct me in debugging this error.

Thanks in adavance!

mhkay · May 10th, 2007, 06:01 PM

It might be that what you're trying to do is simpler than I thought - but I'm guessing a bit as to your actual requirements.

Perhaps you simply want

<xsl:value-of select="format-number((@Column1+@Column2+@Column3)*@Probability, '#,##0.00;-#,##0.00')" />

The error message is because in XSLT 1.0 the argument of sum() must be a set of nodes, and you have supplied it with a number (the result of an addition is always a number).

It's not a good idea to use disable-output-escaping to output a non-breaking space. It greatly reduces the reusability of your code for no good reason. Just use <xsl:text>#xa0;</xsl:text>. The output may be a real non-breaking-space character rather than a symbolic one, depending how it's serialized, but it's just as good.

Michael Kay
http://www.saxonica.com/
Author, XSLT Programmer's Reference and XPath 2.0 Programmer's Reference