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.