Formatting Numbers [SSRS]

The Matrix NumbersOn 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.

The Format property

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:

Formatting a number using standard format string

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.

Formatting numbers while specifying number of decimal positions

The standard format strings are useful but not very flexible.  If you need more flexibility then there are some custom format strings to be used.  In that case the equivalent of N0 would be #,###.  Here are some more:

Formatting numbers using the custom format string

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.

Using the number scaling specifier to minimize space usage

With that knowledge we can even take it one step further!  (Get a coffee if you haven’t already.)

Let’s first create a calculated field called NumberFormat using the following expression:

=Switch(
    Fields!SalesAmount.Value < 1000, "0.#",
    Fields!SalesAmount.Value < 1000000, "#,.#K",
    true, "#,,M")

Now that we’ve got that field we can use an expression like this:

=Format(Fields!SalesAmount.Value, Fields!NumberFormat.Value)

Note: the Format function in use here is explained a bit further down.

Here’s what that would look like in a table:

Using the number scaling specifier

If you want to specify a different format for negative numbers, don’t worry, can be done as well.  Just enter two custom format strings separated with a semi-colon: #,###;(#,###).  And the same for zero values, add another semi-colon and format string at the end: #,###;(#,###);’-’:

Specifying a different format for negative or zero values

Can you achieve the same without using the Format property?  Sure, as I mentioned in the intro you can also make use of some functions when displaying the value.  One of the functions that can be used is called FormatNumber:

Function FormatNumber(

ByVal Expression As Object,

Optional ByVal NumDigitsAfterDecimal As Integer = -1,

Optional ByVal IncludeLeadingDigit As TriState = TriState.UseDefault,

Optional ByVal UseParensForNegativeNumbers As TriState = TriState.UseDefault,

Optional ByVal GroupDigits As TriState = TriState.UseDefault

) As String

NumDigitsAfterDecimal is used to specify precision.

IncludeLeadingDigit has an effect on numbers smaller than one. When set to True 0.6 will render as 0.6 while that will become .6 when set to False.

UseParensForNegativeNumbers will render parentheses around negative values when set to True, otherwise you’ll get a leading dash: (5) or -5.

GroupDigits indicates if the thousands separator should be rendered.

To give that a try, replace the =Fields!OrderQuantity.Value with this expression:

=FormatNumber(Fields!OrderQuantity.Value, 3, True, True, True)

Here’s what that looks like when rendered:

Using the FormatNumber function

There’s something you do need to take into consideration though.  Take a good look at the above screenshot and compare it with some of the other ones shown earlier.  Do you see the horizontal alignment difference? The FormatNumber function actually returns a string while the formatting options through the Format property keep the value numeric and thus aligned right.  That has an effect on the export to Excel, so you may want to experiment a little before delving into using custom expressions.

Another really useful function is called Format. Here’s what it looks like:

Public Shared Function Format( _

ByVal Expression As Object, _

Optional ByVal Style As String = “” _

) As String

It works very similar to the Format property: any valid format string in the Format property will also be a valid string to be passed into the Style parameter of the Format function.

Sidenote: if you’re looking for the MSDN page on a certain function but can’t find it, try this.  First locate the function in the Expression builder:

Locating the Format function in the Expression builder

Then enter the function name followed by the description (or part of it) surrounded with double quotes into the search box of your favorite engine:

How to find a function description

Reference page found!

Leading Zeroes

What if you want your number to display leading zeroes?  That can be achieved by using another standard numeric string formatter: D.  Try using D10 on the SalesOrderLineNumber:

Formatting numbers with leading zeroes

There you go, leading zeroes!

Currency Amounts

With regular numbers covered, let’s switch to currency values.  The standard format string for currency amounts is C.  When specified, the currency amount will be rendered with a leading currency symbol, a space, thousands separator and two decimal positions.

Using the standard format string for currency amounts

The symbol that’s used depends on the report’s Language property unless that property is not set.  I’m not going into localization issues now so have a look at following page for more info if needed: Set the Locale for a Report or Text Box (Reporting Services)

Can you use a custom format string for currency amounts?  Sure, here’s what it looks like: ‘€ ‘#,#.  You actually hard-code the currency symbol into the format string.

Using a custom format string for currency amounts

BTW: those single quotes are optional, you’d get the same result if you leave them out.

Formatting currency values through expressions is possible as well.  The function specifically created for this is called FormatCurrency:

Function FormatCurrency(

ByVal Expression As Object,

Optional ByVal NumDigitsAfterDecimal As Integer = -1,

Optional ByVal IncludeLeadingDigit As TriState = TriState.UseDefault,

Optional ByVal UseParensForNegativeNumbers As TriState = TriState.UseDefault,

Optional ByVal GroupDigits As TriState = TriState.UseDefault

) As String

As you can see it is very similar to the FormatNumber function.  The only difference is that it will generate a number with a leading currency symbol.

So what if your data consists of mixed currencies?  In that case the FormatCurrency function is not very useful.  But it can be done!  Let’s demonstrate this using the SalesAmount and the CurrencyISO fields.  In our sample data the CurrencyISO field contains the three-letter ISO code of the currency.

The reference data, a list of currencies accompanied by their symbol, should be available in a database somewhere.  For demonstration purposes I’ll create a new dataset called Currencies using the query below.  So just imagine it comes from a table. :)

select 'USD' CurrencyISOCode, '$' Symbol
union all select 'EUR', '€'
union all select 'CAD', '$'
union all select 'GBP', '£'

We can now use the Lookup function to retrieve the symbol that belongs to the currency ISO code.  Here’s what that would look like:

=Lookup(Fields!CurrencyISO.Value, Fields!CurrencyISOCode.Value, Fields!Symbol.Value, "Currencies")
    & " " & Format(Fields!SalesAmount.Value, "#,0.00")

The expression above would give us following output:

Mixed currencies using correct symbol through lookup on different dataset

Percentages

Let’s conclude this article by displaying some percentages.  We’ll create a new dataset for that:

select 0.33 Pct
union all select 1.5
union all select 0.0045
union all select 42

Once more we’ve got a standard format string that can be used.  Here’s what it looks like if we put this Pct field in a table.  The left column is not formatted while the right has got the standard P formatter in the Format property:

Formatting percentages with the standard P string

Without any precision specified the P formatter uses two positions following the decimal separator, just like we’ve seen earlier.  And we get a space followed by the percentage symbol added to the end of the number.

But what’s really important to note and differs from regular numbers is that the P formatter will actually multiply the value by 100!  That means percentages from 0 to 100 need to be stored in the 0 to 1 range.  Don’t forget to take that into account or your reports won’t make much sense.

Another option to get this percentage formatted is through using FormatPercent function:

public static string FormatPercent(

Object Expression,

int NumDigitsAfterDecimal,

TriState IncludeLeadingDigit,

TriState UseParensForNegativeNumbers,

TriState GroupDigits

)

As it’s very similar to other functions mentioned above I won’t go into much detail here.  Try the following expression:

=FormatPercent(Fields!Pct.Value, 1)

And here’s its result:

Formatting with the FormatPercent function

If you need more flexibility have a look at the % symbol in following format string: #,0.00%

Result:

Formatting percentages through custom string

If you need per mille then then you’ll need to use the per mille sign: #,0.00‰

The per mille formatter

Notice how the per mille symbol multiplies the value by 1000, don’t forget to take it into account!

Tip: if you don’t know how to type that symbol, simply copy it from my expression above.

Conclusion

In this article I’ve explained practically all different options to get your numbers formatted the way you’d like to.

For additional tips have a read through the next chapter in which I handle even more formatting, such as for dates and times.

Happy formatting!

Valentino.

Share

Tags: , ,

  1. puneet’s avatar

    HI I need help in formating.

    We have 3 Rows. 1st row shows sales and 2nd row shows Gross margin and 3rd row shows Gross Margin %.
    I need 1st two rows with Comma and nothing to effect 3rd row. Can you help me in writing expression for it.

    Reply

  2. Faisal’s avatar

    Very nice article.

    Reply

  3. Steve T’s avatar

    Ah, FormatPercent! Thank you for that tidbit.

    I agree this is a nice complete article on number formatting. Thank you for taking the time to put it up!

    Reply

© 2008-2014 BI: Beer Intelligence? All Rights Reserved