Summing Calculated Columns in DVWPs
Every blog post I’ve read says it can’t be done, but now I beg to differ! Today I came up with a way to sum calculated columns in SharePoint Data View Web Parts (DVWPs). It has been bugging me for a long time that I couldn’t do this, and it especially bugged me that everyone said that you just can’t do it. I don’t like the word “can’t” when it comes to programming. Usually it just means “I can’t figure it out”.
My gut told me that I just wasn’t thinking about the XSL the right way, and that it had nothing to do with SharePoint, and that hunch turned out to be right. Instead of trying to find the answer in a SharePoint context, I decided to just look in an XSL context. Sure enough, I turned up an old post with the answer. (It’s the SECOND EXAMPLE. I fiddled with the first one and couldn’t figure it out.)
Here’s the dealie-bob. What you need to do is create a new recursive template in your DVWP. In my example, I wanted to do a pretty standard sales pipeline calculation. For each potential Project in our Pipeline list we might sell, there is a Value (USD we could earn for the project). Each Project is in a particular Stage in the sales process. Each Stage has an associated Likelihood. We maintain the Likelihood for each Stage in a second list called Opportunity Stages so that we can change those values separately if need be.
So, for each Project, the Adjusted Value = Value * Likelihood, and we want to sum that calculated amount at the bottom of the list. In my footer, I have a table detail element like so:
<td align="right"> <xsl:variable name="TotalAdjustedValue"> <xsl:call-template name="TotalAdjustedValue"> <xsl:with-param name="Rows" select="$Rows"/> </xsl:call-template> </xsl:variable> <xsl:value-of select="format-number($TotalAdjustedValue, "$#,##0.;-$#,##0.")"/> </td>
The TotalAdjustedValue template looks like this:
<xsl:template name="TotalAdjustedValue">
<xsl:param name="Rows"/>
<xsl:choose>
<xsl:when test="$Rows">
<xsl:variable name="FirstRow" select="$Rows[1]"/>
<xsl:variable name="TotalAdjustedValue">
<xsl:call-template name="TotalAdjustedValue">
<xsl:with-param name="Rows" select="$Rows[position()!=1]"/>
</xsl:call-template>
</xsl:variable>
<xsl:variable name="Likelihood" select="/dsQueryResponse/Opportunity_Stages/Rows/Row[@Title = $FirstRow/@Stage]/@Opportunity_x0020_Likelihood"/>
<xsl:value-of select="($FirstRow/@Value * $Likelihood) + $TotalAdjustedValue"/>
</xsl:when>
<xsl:otherwise>0</xsl:otherwise>
</xsl:choose>
</xsl:template>
You could pass any nodeset into the TotalAdjustedValue template, so you could use it to calculate subtotals for groups as well. For instance, if you use the ddwrt:NameChanged function to show subtotals for each Client, you could call it like this:
<xsl:variable name="TotalAdjustedValue">
<xsl:call-template name="TotalAdjustedValue">
<xsl:with-param name="Rows" select="$Rows[@Client_x0020_Name = current()/@Client_x0020_Name]"/>
</xsl:call-template>
</xsl:variable>
UPDATE 2009-06-09: As requested in the first comment below, here’s a screenshot of how this might end up looking (client names redacted). Note that the totals don’t equal the sum of the rows above, as I snipped out only a few rows.
Hi Marc,
Great job here… and would be nice if there is an screenshot..
I’m stock with my project right now hope that you could help me with this
I have two data lists
1.) MyMoney List
-Name “Sample Name”
-Amt $100
2.) MyCart List
Item Name Qty Price Amt
Shirt 2 5.00 $10.00
Pen 1 1.00 $1.00
Paper 10 1.00 $10.00
TOTAL $21.00
My questions are:
1.) What I want is when I click the button/hyperlink it will subtract to the amount from other list showed from other webpart?.
2.) Is it passible that I can grab the TOTAL VALUE as parameter for the webpart connection?
Any idea how to do that? THANKS in advance..
SPbb:
There’s nothing out of the box which will work for what you’re describing. I would probably look at some JavaScript to do the math on the total.
M.
Hello Marc,
I use this xsl code below to get the total amount and its working..
Total :
What I’m working now is how to grab the calculated Total Amount as a parameter or whatever to subtract the amount balance of the mymoney list amount. Any idea?
Your reply is greatly appreciated.. Much thanks Marc..
SPbb :)
I don’t think you can do this with a Web Part Connection. After looking at your code, I think I’d still suggest writing some JavaScript to fire on the onclick event for your buttons which would recalculate the total.
M.
Hey Marc,
Thanks for the screenshot. Actually I’m still a newbie with sharepoint and javascript could you give me a sample javascript and steps how to put/fire javascript button? I’m using WSS and sharepoint designer.
Thanks in advance Marc…
SPbb :)
SPbb:
I don’t think that I have enough to go on to solve your problem directly. You’re going to have to build up a reasonably complex page between a custom form and some JavaScript.
M.
this works fine except the calculated column is not coming back as a number so I get a NAN, All i am doing is multiply two number columns to get a total and said the value reutrn is a number.
so this,
returns a nan
but, if i do this
I get a value of 2 since I have two items in my table, So I know its working
why would it the list return something that it thinks is not a number,
sorry the xsl didn’t show up
this provides a Nan “@Remaining_x0020_Impact + $TotalAdjustedValue”
where this provides a valur of 2 “1 + $TotalAdjustedValue”
Michael:
I looked over my post and saw that it had gotten munged somehow. WordPress did some upgrades a while back to the way they display source code and it inserted some hyperlinks into my post’s code.
I fixed the code so that it is what I originally meant. See if it works better for you now.
M.
Hello,
I am creating a Custom survey list in SharePoint , about 100 questions(columns) and need the total for each question. I came across your example and was wondering if the total value can be grabbed in SSRS report. Any help is very much appreciated. Thanks
Radha:
I haven’t done much with reporting services. In my experience, though, it’s easier to do survey analysis in Excel since you end up wanting to do a lot of crosstabs and multivariate analysis.
M.
Marc,
Another great post…I have a requirement I am trying to meet and I think that this post holds the answer, but I am having trouble applying the concept. Maybe you can help me….here is my scenario:
I have an xml list that I am manipulating in a DVWP that I am initially filtering to remove redundant records, and then remaining dataset then is grouped twice on two different columnss. The first group is by year and the second group is by category. I have two columns (unit cost and # of units) in the dataset that when multiplied will give me the total cost. I have been able to pull the calculated column of total cost into the rowview with no problems, but what I need to do now is calculate sums of the total cost per grouping (year and category) and place it in the group header. The nodeset of each grouping gives me the right set of row data, but I cannot produce the dynamic totals as I did with the rowview and then sum them for a total to sit right next to the group $fieldvalue.
I think your post is pointing me in the right direction, but having trouble applying it.
Any thoughts are appreciated.
Thank you for all of your hard work!
Jeff
Marc,
After working with your code, I was able to get it figured out by playing around with the actual template and the filtering / selection of the actual nodeset provided to the template.
Works great!.
Thank you again for your posts…..I know that when I have a hurdle to overcome in working with DVWPs, I can turn to your blog first to see if their may be an answer.
Jeff
Jeff:
Glad you got it, and you’re welcome!
M.
Marc,
If I use the approach you describe above and call the template multiple times in a single DVWP, the performance just chokes. I have a feeling that by using the template multiple times and recursing the nodeset, multiple times, that it is just too big a load since it is running through an xml file of about 3000 records. I am trying to call the template five different times to achieve sum totals.
I guess my question is if you see any issues with calling this template, or any template for that matter, multiple times and the impact on performance? Any recommendations for improving performance?
What I am trying to achieve is a DVWP with a matrix table showing each year of a project and the total budget for each year, with a total project budget at the end. I am using the template approach above to calculate the totals for each year by passing the year in as the parameter, and then performing calculations on the nodeset. I may have the option of altering the initial datasource to generate a data column which would eliminate alot of the calculating I am doing through the sum template, but I would like to see if I can do all of this through the DVWP prior to exploring other options.
I can post my template code if it helps…
Thank you for all of your help….
Jeff
I’ve used this approach multiple times and not had a big performance problem. Obviously, the more data you have the more work there is to do. At 3000 items, you are beyond the mythical 2000 item limit (which is sort of a horse puckey limit, but I mention it because others are bound to). Depending on what you are actually trying to do, you may be able to reduce the sizes of the nodesets you are recursing on with judicious use of filters, perhaps even dynamic filters.
Honestly, I think DVWPs are fantastic, but that doesn’t mean that they are the right solution for everything. When you say that the performance chokes, what metric do you have? Time for page load? Memory consumed on the server?
M.
Hi Marc,
Thanks very much for the great info. I’ve got the total total working, but when I try to use your second call to get subtotals, I get an error: A reference to variable or parameter ‘Rows’ cannot be resolved. The variable or parameter may not be defined, or it may not be in scope.
My only guess is that it’s got something to do with the fact that I may not be using “ddwrt:NameChanged function to show subtotals.” I’m using grouping. Can you straighten me out? Thanks again!!
Pete
Hi Marc,
Need your help on (DataView in sharepoint).
I have a choice field in my list and it as a validation like,if some enters values under fields and check the option of choice field ,then those values should be add to the separate column else it should not be added.
power time:Text type
Energy time:Text type
Saved power:Choice(Yes/No)
Total savings:(Add value fields if saved power field checked else not to add).
Please help me how can i complete my task.
poornachandra:
Based on your description, it’s difficult to understand exactly what you’re trying to do, but it sounds like something you could enable with script in the form. You may not need to convert to a DVWP to make it work.
M.
Thanks for your prompt response….
How can i get list items from a list based on a filter like choice field status is equals to yes.Once i get a data then i will calculate one by one and stores in my data view webpart.
Please help me else suggest me how can i do the same.
Poorna:
Certainly. Filtering your DVWP is something you can do from the ribbon options without writing any code yourself.
M.
Great post, thank you. I have a question though, i have columns that i have created in SPD, how can i get the xslt to pull the values from them? they aren’t true columns in the list so they aren’t pulling right on the dsQueryResponse/NewDataSet/Row[@ColumnTitle]; when i use a column from the database itself, it works, but not with one i added through designer.
thank you.
Jen:
That doesn’t sound right. If the column is in the list, the column is in the list. Where you created it shouldn’t matter.
M.
Marc,
I created a dvwp. I was able to sum the total for the number columns. However, when I tried to create a sum for a calculated column I get a “NaN” value.
I have tried the following codes:
**xsl:value-of select=”sum($nodeset/@Row) ** (This returned ‘NaN’)
**xsl:value-of select=”format-number(sum($nodeset/@Row), ‘#,#0;-#,#0′)”** (This returned a blank/empty cell)
**xsl:value-of select=”format-number(substring-after(sum($nodeset/@Row),’;#’), ‘#,#0;-#,#0’)”** (This also returned a blank)
I believe this may have to do with the fact that the values in the calculated column show up as “float;#12345.000000” in dvwp. I have changed to substring-after ‘;#’. But I believe this is what may be causing the problem.
Is there a way I can fix this issue?
Thanks in advance.
Is the calculated column called Row?
General, I do the calculations right in my DVWP rather than using calculated columns at all. Because calculated columns are only recalculated when the item changes, sometimes the value may not be what we want, especially if there’s any date logic included. I’d suggest simply doing the calculation in the XSL instead.
M.