This is going to be the last PivotTable post, at least for a while. Unlike the last several posts, the subject matter that follows applies to any PivotTable, not just those connected to SQL Server Analysis Services.
In current versions of Excel, one of the capabilities that exist in PivotTables is the ability to adjust the position of the labels that describe the values in the Values region of the PivotTable (i.e. â€œSum of Salesâ€). Excel PivotTables offer significant flexibility in this area â€“ the labels can be on rows, on columns, and anywhere in the hierarchy on either of those areas. When we visit customers to talk to them about how they use PivotTables, though, we see a couple of things. First, the majority of users arenâ€™t fans of our initial placement of the labels. Second, most people have never figured out that the labels can be repositioned. We have tried to address both of these items in Excel 12.
This area is probably best explained by walking through an example, so here goes. To start with, imagine you were building the following PivotTable. It has some items on rows and columns, and Sales Amount summarized in the Values area.
If you add a second field to the Values area â€“ say Product Cost â€“ then Excel adds some captions (â€œSum of Sales Amountâ€, â€œSum of Product Costâ€) below the years (â€œ2003â€, â€œ2004â€) to help the user distinguish which numbers are Sales and which numbers are Product Cost.
Those of you familiar with PivotTables have probably already spotted one change from current versions of Excel. In current versions of Excel, the captions are placed in the Row area, not the Column area. Here is a visual of what that looks like.
This one change â€“ putting the labels on columns and not rows when a second field is added to the Values area â€“ makes PivotTables with multiple items in the Values area more readable, and was the default positioning that most users wanted. So far, feedback on this one small change has been very positive.
As I said above, PivotTables are flexible enough to show the labels at any point in the hierarchy on either the Row or Column areas . To move the labels around in current versions of Excel, you can drag and drop a â€œDataâ€ field in the Excel grid. This is not terribly obvious, though, and those folks that did spot this capability often had trouble putting the labels at the point in the hierarchy that they wanted. In Excel 12, we have tried to make this a more straightforward task by putting a field for the labels in the Drop Zone area of the field list that people can move around exactly like any other field. So, when you add more than one field to the Values area, we add a field labeled â€œâˆ‘ Valuesâ€ to the field list, initially in the Column Label area.
We donâ€™t show this field until you add a second field to the Values area because we donâ€™t put captions in the PivotTable until there are multiple items in the Values area.
If you want to re-position the captions elsewhere, you can simply drag-and-drop the â€œâˆ‘ Valuesâ€ field to another drop zone or another position in the drop zone that currently contains the â€œâˆ‘ Valuesâ€ field. Say I wanted to see the labels above the â€œYearâ€ field in the Column area. All I need to do is start dragging the â€œâˆ‘ Valuesâ€ field, and I get feedback as to where the field will end up when I am done (blue bar, new cursor).
If I drag the â€œâˆ‘ Valuesâ€ field above the â€œYearâ€ field and let go, the PivotTable will refresh, and the captions will now be outside the year information, showing me a different view of my data. Here is what that looks like.
I can also move the captions to the Row area. Here is what the PivotTable looks like when I have moved the â€œâˆ‘ Valuesâ€ field to the bottom of the hierarchy on the Row area.
Further, I can move the captions anywhere else in the hierarchy in the Row area. Here is what the PivotTable looks like when I have moved the â€œâˆ‘ Valuesâ€ field in between the Product Name and Product Category fields.
By now you are probably getting the idea â€“ the placement of the labels is infinitely flexible, allowing you to see the data pretty much any way you want. Hopefully folks see this as a useful feature. As always, I am interested to hear your feedback.
Next up, OLAP functions.