Weekly Newsletter Signup



• Upcoming
  Live Online Workshops

  • SharePoint Charts and Graphs: Part 01 (Google and Yahoo APIs)
  • SharePoint Charts and Graphs: Part 02 (Sparklines)
  • SharePoint Charts and Graphs: Part 03 (Gantt Charts)
  • Create a Master Calendar in SharePoint
  • jQuery Animations in SharePoint
  • SharePoint WSS Dashboards: No Code Required
  • Become Your Company's SharePoint SuperStar!

• Ongoing Series

  • Article Archives (1000+)
    All Authors
  • This Week in SharePoint with Joel
    Joel Oleson
  • jQuery for Everyone
    Paul Grenier
  • The Calculated Column
    Dessie Lunsford
  • The Data View Web Part
    Laura Rogers
  • Stump the Panel: Q&A
  • Subscribe to the Weekly Newsletter
  • SPC09 - Live Media Streams

• Authors

  • Dessie Lunsford
  • Laura Rogers
  • Lee Reed
  • Mark Miller
  • Jim Bob Howard
  • Paul Galvin
  • Paul Grenier
  • Toni Frankola
  • Woody Windischman

• SharePoint Resources

  • Bits of SharePoint
  • Codeless Solutions for SharePoint
  • Get the Point
  • Innovation Matters
  • Laura Rogers
  • Path to SharePoint
  • Paul Galvin
  • SharePoint 911
  • SharePoint Blank
  • SharePoint Blogs at SharePointU
  • SharePoint Forums at SharePointU
  • SharePoint Screencasts
  • SharePoint Use Cases
  • Social Media Talk Tweets
  • The Sanity Point
  • The Weekly Newsletter  

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].

---

Notify me of comments to this article:
E-Mail:

---

Comments