Tuesday, August 8, 2006

Call me a hopeless geek but I really get a kick out of learning something that makes my life easier. Sometimes what I learn also annoys me because I realise that I've been doing things the hard(er) way up until that point.

I've been re-reading MDX Solutions 2nd Edition and found something I should have known about long ago. Format string settings in Excel, SSRS, SSAS are pretty much the same across the board. You know, defining #,#.00; (-#,#.00) and all that kind of stuff to make numbers appear the way users want. (There are lots of sites out there with more info on this, the first one I found with a quick Google was http://www.ozgrid.com/Excel/CustomFormats.htm)

A common requirement in financial reporting is to display larger figures in thousands. For example $101,230,142 would be displayed to the user as $101,230. In order to achieve this most of us calculate the actual and then somewhere in the process explicitly divide the result by 1000. Simple. Thanks to Appendix D in MDX Solutions I learned that I can format my "in thousands" figures by adding an appropriate number commas on the end of my format string. Even simpler.

e.g. 123,456,789

Apply a format of #,#, (note the extra comma at the end) the number is displayed as 123,457 (rounded up)Apply a format of #,#,, (two commas this time) the number is displayed as 123

Each comma on the end of the format string, in essence, divides the number by another thousand. Annoyingly simple.