Formatting Numbers in SSRS (and SSAS)

This is a little off of my usual SSIS posts, but I ran across an interesting item. It’s probably well-known to people who work with format strings on a regular basis. However, I’ve used them for a while, and just ran across this the other day, so I thought I’d share it.

I’ve been working with SSRS recently, using an SSAS cube as a data source. The cube had some large numbers in it, in the 10s of millions (10,000,000). It’s not uncommon to translate these large numbers to display as thousands or millions, by moving the decimal place to the left. This saves space on the report, and can make it easier to interpret when dealing with large numbers.

In the past, I’ve usually divided the number by 1,000 to move the decimal place. However, I found out that you can use format strings to accomplish the same thing a little more gracefully. For example, you might have a format string that looks like $#,### to format the number and an expression on the value of the text box that divides the number by 1,000 (=FIelds!MyColumn.Value / 1000). When setting the format string for the textboxes in Reporting Services, you can put two commas together to tell SSRS to divide the by 1,000, instead of using an expression, like this: $#,,

The same technique can be used in format strings for measures in SSAS. Again, this is probably common knowledge for a lot of people, but it was new for me, so I thought I’d share it here.

3 Comments

  1. toddmcdermid says:

    Thanks, John!

    Saved my bacon. I’m just about to get on a conference call with an SVP for a live use of my first cube, and he had just emailed me “can we get rid of the decimal places and show only thousands?”

    Awesome. Searched the intarwebs, and guess where it landed me…

  2. http:// says:

    Thanks John!!!
    It did help

    And if you want to have a number divided by 1000, but with some decimals you can put this : #,#,.00

  3. Diederick says:

    Hi guys,

    That works if your excel or tool you use supports the formating exchange. I’ve just divided /1000 on each measure in the fact on the data source view.

    Right click on fact table and replace by named query. Add /1000 for all the measures and close. Reprocess, voila.

    Note: I’ve you use partitioning by query also adjust this query then.

Leave a Reply