Before I begin, I’d like to say ‘Thanks’ for any of you who came to SQL Saturday BI Edition in Tampa yesterday. It was a very successful event. This could be the beginning of a new branch of SQL Saturday events dedicated specifically to BI topics.
Because I was out of town yesterday and did not get a chance to create a post, I’m going to at least give you all a quick tip when working with Pivot tables (or PowerPivot) in Excel. I’ve been doing a PowerPivot presentation using the Contoso data set now for a few SQL Saturdays and one of the problems that someone asked me about was the sorting of months. I had created a simple pivot table with product categories as columns and years as rows. That part looked fine as shown in the figure below.
Then I added the CalendarMonthLabel field. It did add the calendar month names as shown in the next figure, but the problem was that the months sorted alphabetically. Honestly, I had not noticed during the presentation. So now that someone called me on it I started to look for a solution. I could use the CalendarMonth field which represented the data as a numeric string such as 200706 for June. That is great for sorting purposes, but it is also unacceptable for presenting data to the average person.
So the problem was how to display the months in chronological order rather than alphabetical order. It took awhile, but then I remembered an Excel solution in which you can define custom lists that can be used when filling in cells. For example, when you enter January in a cell, you can then select the handle on the bottom right corner and drag horizontally or vertically to add sequential month names in each cell that you drag through. So I started thinking whether I could somehow use that ordered list in my pivot table as well. After clicking around a bit, I round a solution.
First, click the arrow for the dropdown menu to the right of Row Labels. This displays the menu shown in the figure below that I had used many times before for sorting and filtering the field values in my pivot table rows and column.
Warning: Before doing anything, make sure you select CalendarMonthLabel from the dropdown at the top of the dialog. This dialog only appears when you have more than one field in the row hierarchy.
This rest of the menu includes options that let you change the sort direction (ascending or descending), and it also allows you to define a filter by select which row values you want to display. Sort of hidden between these two is an option that says: More sort options.
Click this option displays a Sort dialog for the currently expanded field, in this case, the CalendarMonthLabel. By default, the sort order is Data source order. Obviously, this option is not going to give us what we want. The second option in the list allows you to manually set the order by dragging the items to rearrange them. Whenever you can do something automatically rather than manually, you want to go with automatic. So is there an automatic way to get the months sorted? Actually, you want to select the third option, Ascending (yes, you could also use Descending if you want to show the most recent months first) and then click the More Options button in the lower left of the dialog as shown in the following image.
This brings up another dialog in which the default: Sort automatically every time the report is updated is selected. Deselect this option to activate the next option. This option, First key sort order, displays a dropdown which when opened displays various ways to ‘sort’ day-of-the-week and month names. For this example, I’m going to select the full month names and click OK.
You will need to click OK to exit the sort dialog as well.
Now the months in each year are sorted chronologically rather than by date which will make the consumers of this pivot table a lot happier.
That’s it for today. Hopefully this week I’ll get back to my normal schedule. See you next time.