Formatting Your SSAS Again

Last time we left our cube with a fusion of fonts and styles more to show how to apply change than to create something that looked good.  But the one thing that eluded our efforts was to format the measure as currency.  Therefore, this week I’m going to show you how to not only display the measure as currency, but how to define your own custom format.  But first need to get rid of all those changes from last time and get back to a basic cube like the one shown below:

Next, look at the tabs across the top of the central work area.  I will not go through the features in all these tabs, at least not now, but I do want to call your attention to two tabs; Perspectives and Translations.

While you can use these tabs for many other things, one of the things you can do is to change some of the properties of the measures. Click on either the Perspectives tab or the Translations tab.  It will not matter which tab you open because the measure properties will be the same in both. Notice on the left side a list of the Measure Groups and the Dimensions.  Within the Measure Groups, this example only has a single table, but there could be more.  Expand the table Fact Sales by clicking on the box to the left of the name if it displays a plus sign.  If the table has already been expanded, find and click on the field Sales Amount to select it.

Open the properties window if it is not already open (Click F4 or open the View menu and select Properties Window) and scroll through the properties until you find FormatString.  Initially this property is empty.

We can enter a format for a positive number and a negative number.  For example the format $#,##0.00 does several things:

–          It displays the number with a leading dollar sign

–          It adds commas as thousands separators

–          It displays the number with two decimal places.

–          It displays number less than 1 with a leading zero before the decimal place.

The ‘0’ is a placeholder and returns the number for that position or a zero.

The ‘#’ returns a number of there are numbers.  When used to the left of the decimal point, all digits are returned even if there is only a single ‘#’ in the format string.  If used to the right of the decimal point, the same number of digits are returned as there are ‘#’ symbols with the number being rounded up or down appropriately.

The ‘;’ is used to separate formats for positive, negative and zero values.  If the semi-colon does not appear, the format is applied to all numbers.  If the format contains a single semi-colon, the string to the left is used to format positive numbers and zero while the string to the right formats negative numbers.  If two semi-colons appear, the expression to the left of the first semi-colon formats positive numbers.  The format between the two semi-colons formats negative numbers, and the format to the right of the second semi-colon formats zero values.

There are additional rules for dates and times that allow you to display different date formats as well as things like month names, day names, and more.

For negative values, the format: ($#,##0.00) does all of the above, but encloses negative values in parentheses.  These two formats are separated with a semi-colon.  For our purposes, zero values can be formatted like positive numbers.

If you click on the Browser tab, you might think that you still did not change the format on the previous page or that you did not click save or some such thing.  Actually, you are close.  You need to rebuild and deploy the cube.  Once you do this, you can open the Browser tab and build your cube if you have not done so before or if you already had a cube you may need to remove the current measure if your format changes apply to the currently displayed measure and add that measure back in. You should now see the measure formatted as currency as shown in this figure.

Well, that’s all for today because as I mentioned earlier this week, it is graduation week.  C’ya next time.