http://blog.hoegaerden.be/2013/06/12/formatting-numbers-ssrs/
On the forums I frequently encounter questions related to formatting certain things for display in a Reporting Services report.
Formatting questions can be quite broad. Here are some examples to give you an idea:
How do I convert a datetime to a specific string format?
How do I display an amount with a currency symbol and thousands separator?
How can I display a percentage with 4 positions following the decimal separator?
The internet does have some information available on formatting. However, this info is spread over several different pages and to be able to find it you need to be aware of a couple of things. Which is why I decided to write an article that’s dedicated 100% to the topic of formatting stuff in SSRS reports so I can refer to it when applicable.
Introduction
Basically you’ve got two options when formatting. The first one is using the Format property, which is one of the properties of the Textbox.
Or you can decide to not use the Format property at all and write a custom expression that uses some formatting functions to create the value string. I’ll explain both in combination with all applicable data types.
You should also be aware that the formatting functionality is not specific for SSRS only. If you’ve got some development experience you’ll probably recognize some syntax. SSRS is actually just a layer on top of the .NET framework, as far as formatting is concerned. So in many cases you’ll find interesting info online by searching in the .NET documentation. I’ll be referring to several .NET-related pages in the text below.
The Query
As with any good report, before we can display anything we first need to write a SQL query. My data source is the AdventureWorksDW2012 sample database, available at CodePlex.
And here’s the query:
SELECT dd.FullDateAlternateKey as OrderDate
, f.SalesOrderNumber
, f.SalesOrderLineNumber
, f.OrderQuantity * 42000 as OrderQuantity
, f.UnitPriceDiscountPct
, f.SalesAmount
, dc.CurrencyAlternateKey as CurrencyISO
FROM dbo.FactResellerSales f
inner join dbo.DimCurrency dc on dc.CurrencyKey = f.CurrencyKey
inner join dbo.DimDate dd on dd.DateKey = f.OrderDateKey
where UnitPriceDiscountPct > 0
Little sidenote: I’m multiplying OrderQuantity with 42000 to get larger numbers to be able to demonstrate certain things, such as the thousands separator. Don’t do that in the production version!
Formatting Numbers
REGULAR NUMBERS
Let’s start with the easiest of them all: the regular numbers. Without any formatting specified, numbers are rendered without any “make up”, similar to how the Management Studio shows them in the query Results window. For a report that’s usually not what we prefer.
I mentioned the Format property earlier. Now the question of the day is: what can you put in there to format a number differently? There are some standard format strings that can be used. Here’s one: type an N in the Format property of a Textbox that displays the OrderQuantity. When rendering the report you’ll get a number with a thousands separator and two decimal positions:
What if we don’t want any decimals? Or what if we actually want to display four instead of two? No problem, precision can be specified by just adding a trailing zero or four to the N specifier: N0 or N4.
The standard format strings are useful but not very flexible. If you need more flexibility then there are somecustom format strings to be used. In that case the equivalent of N0 would be #,###. Here are some more:
The double comma that you see in the #,#,,.# example is called the number scaling specifier. As you can see, for each instance of that specifier the number gets divided by 1000. This is a very useful method when you want to use minimal space such as in charts.
