THE PROBLEM
You've written a SQL Reporting Services report (SRS) that displays large numbers (4 digits or higher). The problem is, end-users don't want to see numbers that glob together like this:
Instead, they wish to see the thousands separator so they can see the scale of the numbers better like this:
(and for you non-US locales, it would show the cultural-specific separator that you use, like a period instead of the comma as the thousands-separator, or whatever you use)
THE SOLUTION
NOTE: I'm using SQL Server 2008, SQL Reporting Services 2008 and SQL Report Builder 2.0. You mileage may vary on other versions, but I'm sure the solution will work for most, if not all versions.
The Short Answer
Use "N0" (without the quotes) in the "Format" property on a textbox or data region:
The Full Answer
In SRS, there's a property called "Format" that controls how numbers and dates are formatted. You can use this property on textboxes and data regions. It would appear that since this is all built on .NET, it uses the same formatting schemes as the .NET framework so they point to the developer's guide to figure out the format string. For what we're looking for, we'll need to know the Standard Numeric Format Strings.
I don't know, maybe it's just me, but it took me a few times of looking at that crap to figure out just what it all means. I mean, since we're not all .NET developers, the format strings you need to produce a certain output seem to get lost in a sea of irrelevant code that we won't be using. I'm writing another article on those format strings shortly so you don't have to try to figure out how to convert that all to something you can use in SRS.
But for our example, since all we need is something to add the thousands-separator, and we don't want any decimal points since we're just displaying a count of something, let's use "N0" (without the quotes). This is the one I use in almost every report I've written. The N means NUMBER and the 0 indicates the number of decimals of precision. I'm pretty sure I could also use F0 (for fixed-point) or maybe G-something to get the same format, but I use N0 cuz it worked for me first.
SUMMARY
So I guess what I'm saying is, use N0 for the "Format" property on an SRS report in order to add the culturally appropriate thousands-separator to a number in a textbox, data region, (axis label, maybe) and perhaps others. I haven't actually looked to see all of the objects in a report that have the FORMAT property, but everywhere it's there, this format string will work to convert something like 123456 into 123,456 (if you're en-US) or 123.456 (if you're fr-FR).
Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog
