Calculated Column Formula Tips – Aggregating Calculations Over 1000
Contributing Author: Jim Bob Howard
Congratulations, Mark, on the posting of the 1000th article on EndUserSharePoint.com! Thanks for letting me be a part of it. In honor of passing that milestone, maybe this will come in handy for you as you continuing calculating the number of articles here. —Jim Bob
Aggregating data with a DVWP and a Linked Data Source can really save you lots of time and make your SharePoint lists do some great online reporting.
I was recently working on a Mileage Reimbursement application and was able to use some tricks I learned from Laura Rogers in her screen cast, 4 Minute Screencast: Join View of SharePoint Lists, to link data from different lists. Her techniques gave me the direction I needed to display all of the mileage records for a given employee for a given month, as well as show aggregated totals based on the data brought in through the linked data source.
Figure 1 – Data Form Web Part, plus a Data View Web Part
In Figure 1, two of the fields in the Data Form Web Part come from a Linked Data source, as shown in Figure 2.
Figure 2 – Data Form Web Part – XSLT aggregation
The DVWP also has some aggregated fields that sum up Calculated Columns, as show in Figure 3.
Figure 3 – Data View Web Part – XSLT aggregation
But, if you are aggregating Calculated Columns—using XSLT calculations on columns that are SharePoint Calculated Columns—you may have noticed, like in Figure 4, that your data disappears from your form when the calculated value goes over 1,000.
Figure 4 – Calculated column data disappears
Notice, though—in Figure 5, that the data is actually calculated correctly. It’s just not displayed correctly in the DVWP row, nor is XSLT able to perform the summation of that column.
Figure 5 – Calculated Column functioning and displaying properly
Why 1,000?
The answer is in the answer. That’s right, it’s the comma.
The Calculated Column is stored as a string (Single Line of Text). SharePoint formulas will automatically cast a string that "looks like a number" into a number, when necessary, for a given formula that requires numbers. Let me explain.
Consider the following situation
X = 100
Y = 6X
X is a string with a value of "100" and Y is a number with a value of 6 times X. The formula (6*[X]) will convert the string "100" to the number 100, multiply it by 6 and return 600, storing it either as a string or a number depending on how Y is defined.
But a Calculated Column which results in a value of "1,000" no longer "looks like a number" because of the comma. So, if X has a value of "1,000", Y will not equal 6000. Further XSLT cannot cast a non-number-looking string to a number, either.
To make matters worse, the Calculated Column doesn’t give you the option of turning off the comma.
Figure 6 – Typical Calculated Column definition
So, what do you do?
Formula Tip – FIXED
Since you are already working with a Calculated Column, simply add another formula to make sure the number stays a number. Here’s the description for just thing you need.
Figure 7 – The FIXED function
That last parameter—no_commas—is what is going to save us. The default for this parameter is FALSE, meaning: "No, don’t show no commas." Nice, huh? TRUE, on the other hand, says: "Yes, we want no commas."
Remember, that even though this function is used to "convert" our number to text, that’s OK, because the Calculated Column is going to be text anyway. What we want to ensure is that the text "looks like a number," meaning, no commas.
Let’s try this out on our Calculated Column, Trip Miles, by wrapping our calculation in this new formula:
=FIXED([Ending Odometer]-[Beginning Odometer],1,TRUE)
Since we want to have only one decimal place, the decimals parameter is set to 1. But, since we don’t want commas, we’ll set no_commas to TRUE.
Is that all? Yes, that’s all.
The Result
Now that our text field looks like a number, the DVWP can treat it like one when doing calculations or formatting. See Figure 8 to see how it looks now.
Figure 8 – DFWP and DVWP both now properly display Calculated Columns with values 1000 or higher
Contributing Author: Jim Bob Howard
Jim Bob Howard is a web designer / web master in the healthcare industry. He has been working with SharePoint only since March 2009 and enjoys sharing what he has learned. You can email him at [email protected].
on
Great tip. Needed this a while back for a project but ended up doing a custom solution for it. Will definately add this to my memorybank for the future.
Jim,
This is a definite gem. How did you:
- create the aggregated values
- create the ‘Submit Report’ button.
I am assuming this run a submit for approval workflow?
Greg,
Aggregated valued, see Laura’s screencast, then check out the XSLT I display for each of the aggregated fields. Also, see my answer to this STP question: http://www.endusersharepoint.com/STP/topic/sp-designer-average-on-column-not-working
I’ll be writing an article on the Submit Report button, too. I’ve not finished the functionality of it, yet. ;) But, I’m building it on a post from Nataly Voskresenskaya at http://spforsquirrels.blogspot.com. (Can’t remember the exact article off the top of my head, but I mentioned it on Twitter yesterday – http://www.twitter.com/jbhoward.)
And yes, this will be using a two-step submit for approval workflow.
Glad you liked the article. I’m planning more in the series on Calculated Column Formula Tips.
Blessings,
Jim Bob
Here’s the link to Nataly’s post: http://bit.ly/15hEz2
Hi Jim,
Thanks a lot for the pointers.
Will definitely investigate Nataly’s blog!
Also really impatient to see your post introduce the end users to buttons.
The out of the box workflow tab is always a training issue.
Greg
Be sure, also, to join in to the SharePoint Shop Talk (open Q&A) 8/13 @ 12:30PM EDT http://bit.ly/oZLv4
Laura Rogers, Paul Galvin, Nataly Voskresenskaya, Harry Jones, and I will be there. Mark Miller joined in last time, but I don’t know if he will this week from his fishing trip. ;)
And I’ll get to that button post just as soon as I can. ;)
Blessings,
Jim Bob
Hi Bob,
You totally opened my eyes on the XSLT customisation possibilities. Your post above and Laura’s screencast http://www.endusersharepoint.com/2009/09/01/get-it-together-groups-and-totals-in-the-data-view-web-part-screencast/
definitely triggered my interest.
As far as the button to trigger workflows, did you have a chance or time to work on it?
I checked the following post http://nextconnect.blogspot.com/2009/06/using-jquery-to-update-item-without.html but it is trigerring a javascript – not a workflow…
Greg
Dear Greg,
Yes, I have several button workflows going on a project I’m finishing up. I’ll work up some screenshots/screencasts in the near future.
I’ll also be covering button-initiated workflows in my EUSP workshop, Business Solution: Manage Milage Reimbursements, scheduled for December 3.
Blessings,
Jim Bob
TIP: Keep an eye out at EventBrite for the EUSP workshops as they’re scheduled.
Excellent!!!! ..Been searching for the answer to the XSLT display issue with calculated fields in the thousands. I thought it had something to do with the comma, had no idea about the FIXED function – a definite help!
Glad to hear it, Darren!
More nuggets coming soon…
Blessings,
Jim Bob
Great post – thanks for documenting the FIXED function.
We used a calculated column formula to transform a number entered as mm.ss to a decimal number. So as an example, the user has a time of 6 min. 59 seconds. They enter this in a number column as 6.59. Our calculated column converts it to a decimal 6.96. The benefit is that on a graph – 6.96 looks much closer to 7 mins, than 6.59.
The problem is that due to the calculated column being a string – our graphing webpart will not render the 6.96 as a number. Any thoughts on how to accomplish this?
Thanks,
Mike
Life Savor! Couldn’t figure out why my data wasn’t being displayed. Same thing was happening with my calculated graphs. Now all I have to do is go through 65 calculated columns of data and correct the formula!
Is there a way to have the formula keep commas? I know i cant filter the data in SP designer in the webpart but now the SP list doesnt contain the comma in a datasheetview
Nick,
Hmm… No, I don’t think you can eat your cake and have it, too. ;)
The DataSheetView will display it as it’s stored. We’re storing it as a string without commas. Then, we’re relying on the DVWP XSLT to format it as a number with a comma on display.
Blessings,
Jim Bob
Figured that!
thanks for quick reply
Fantastic tip, especialy for SPD newbies
Thanks alot for posting, its helped me out alot
but it won’t work in sort the column in list.
Would you mind restating that HP? I don’t quite understand what you’re getting at.
Hi,
I have a similar requirement. I have claculated the sum of a column but instead of placing the Total value like u did..can I put that value in the column of a list.
Hi,
Can you explain me a bit details how did u get the value 89 into a form field and if i want to get the value into another column how do i get it?
Hi Kirthi,
The 89.0 isn’t actually in a form field. It’s in a DVWP and is a sum of the retrieved list items. It’s simply displayed on the screen, not stored anywhere.
See Figure 2, above, for the XSL/XPath I used to calculate it on the page.
Blessings,
Jim Bob
Thanks for the reply,,,
If want to displaythe same in another column then how can I do it.