When you first build your cube in SSAS, you may not like the way it looks. Typically, the measures are not formatted. If you have large numbers, there are no commas separating the thousands. Each number could have a different number of decimal places. The numbers are left justified, not right justified or even decimal justified. Finally, currency values do not display any currency symbols. Perhaps this does not both you, especially if you plan to create a report with Reporting Services for your end users. You can certainly format your data in the report instead and honestly, this is what I would usually do. However, if want to capture an image of the pivot table directly from the SSAS browser screen to be placed in a Word document for example, you may want to dress up your table a little first.
Last time, we left our cube looking like the following figure. Not too pretty, and certainly not easy to read or interpret.
In fact, it is hard to tell at a glance, which numbers are large and which are small because the numbers are left-aligned. Perhaps your first thought is to look for a properties option when you right click on a measure much as we did back in the Excel pivot tables. After all, since this is just another Microsoft product, you would expect it work the same way. Right? (You in the back, stop snickering!) Instead, when you right-click on a measure, you will find an option named Commands and Options at the bottom of the menu which displays the following dialog.
First note that the Select option is a dropdown that defines the portion of the pivot table you last clicked on. Changes to formatting in this dialog typically apply to the selected portion of the table. However, you can also use this dropdown to change the portion of the table to which you want to apply formatting. It should also be interesting to note that this dialog can be left open while you click on different parts of the pivot table to format different elements. Finally depending on what part of the pivot table you click on or select from the dropdown, the options that appear and are enabled including the tabs that appear in this dialog will change accordingly.
Before we look at formatting our numbers, let’s look at some of the other formatting options beginning with sorting. Suppose we had the following pivot table that displays sales by year and channel just to keep things simple. If we right-click in any column and then display the Commands and Options dialog (if it is not already open) and click the ascending sort button , the rows in the table shuffle to display the data in the selected column in ascending order. Similarly, the descending sort button reorders the rows so the values in the selected column appear in descending order.
Likewise, if we click in the row headers, we can sort the rows by the header labels in ascending or descending order. You can even click in the column headers and sort the order of the columns in ascending or descending order using the column header text.
Formatting of the column and row header text to use a different font, color, font size, style or even the background color is also easy. The following figure shows a couple of format changes to show what is possible, not to show what good design is. Notice that when you select any one of the column or row headers, the format applies to all of them except for Sub-Total and Grand Total columns or rows.
You can format Grand Total headers separately by clicking on the header Grand Total. The interesting twist here (and it is more than a bit confusing) is that changes to the font style (bold, italic, underline) get applied to the header, but if you change the font itself, the font color or the font size, these properties only affect the data in the column or row, not the header text.
In a somewhat similarly twisted way, if you click on a measure in the table and change the font, font size, font color, or background color, you only affect the detail measure values, not sub-total or grand totals. These can be selected and formatted separately.
(Perhaps to eliminate confusion as to what formatting to apply, formatting changes to rows such as the Grand Total row at the bottom of the table will have priority over formatting changes applied to Grand Total columns.)
So now, it is time to look at changing our numbers to currency. If you select any of the measure cells in the table and attempt to use the seemingly obvious Number dropdown to select Currency thinking that dollar signs and nicely formatted numbers will appear, you would be wrong. The Currency option does not appear to change the formatting of the numbers in the table at all.
So how do you display the measures as nicely formatted currency values? For that, you will have to come back next week.
By the way, my daughter graduates from college this week with a Doctorate in Pharmacy. Therefore, while next Saturday’s post will be short, you will not want to miss how to format the measures in your SSAS cube as currency. C’ya later.