In a previous post I covered the new sorting and filtering capabilities of Excel 12 PivotTables. Those features are available for any PivotTable, regardless of the data source. There are a few additional filtering options available for PivotTables connected to Analysis Services, so I want to review those today. I also wanted to make a short point about some â€œformatting persistenceâ€ work we have done in Excel 12.
Filtering by member properties
A few days ago I wrote about member properties. When a PivotTable is connected to data from Analysis Services, you can filter items in the PivotTable based on the value of that itemâ€™s member properties. Letâ€™s look at an example. In the screenshot below, I have a PivotTable with Product Categories, Products, and Sales Amounts. I might want to filter the Products in the PivotTable by one of their properties. I can do this by applying a Label Filter â€¦ I simply need to right-click on one of the products and choose Filter|Label Filer from the context menu.
This brings up the Label Filter dialog. Since there are member properties defined for the field I selected, the Label Filter dialog lists those for me to select from.
If I pick the field name (â€œProduct Nameâ€ in the example), the filter will be applied to the visible items in the PivotTable. If I pick one of the member properties, however, which are listed under the field name in the drop down, the filter will look at the member-property values instead. If I only want to see the bikes where the color is silver, I can use the Colour member property to do that. Here is a screenshot of the PivotTable filtered by the color member property so only silver bikes are displayed.
Filtering by values not displayed in the PivotTable
Another filter ability specific to PivotTables connected to Analysis Server is the ability to filter items by a value that is not currently displayed in the PivotTable. For example, you might want to filter products in a sales report by the profit margin of each product, even though profit is not showing in the PivotTable. Again, letâ€™s walk through an example. Below is a PivotTable that shows Sales Amount by Product and Product Category. In this case, I only want to see products that have a profit margin which is greater than 40%. To do this Iâ€™ll apply a value filter to my PivotTable.
After I select Value Filter from the context menu, I see the Value Filter dialog. When I drop the first drop-down, all the different value fields available are listed, even though the PivotTable only contains Sales Amount. I simply select Gross Profit Margin, type in 40, and press OK.
And now my report is showing only bikes with a profit margin greater than 40%.
I am personally a very big fan of this feature.
Hiding levels of hierarchies
Excel 12 PivotTables that are connected to Analysis Services allow you to hide any level of a hierarchy as long as at least one level is still visible. As an example, say I want to compare bikes independent of what type of bike they are (I donâ€™t want to see Category or Subcategory information). To do this, I can hide the parent levels of the product name level. Specifically, I just need to select Show/Hide Levels from the PivotTable context menu, and from there I can toggle on or off any levels I like.
After hiding the two levels, Iâ€™ve also sorted the bikes by their individual total sales amounts and, as you can see in the screenshot below, I can now work with the bikes across their groups. Notice that mountain bikes are now mixed with road bikes etc.
This also allows me to produce, for example, a â€œTop 10â€ list of the best-selling bikes regardless of category. This is another example of the very powerful analysis capabilities available in Excel 12 PivotTables.
Here is the final result.
If I now unhide the Subcategory level, the filter will be reevaluated in the new context, and I will get a â€œTop 10â€ list of bikes for each subcategory. I think thatâ€™s pretty neat too.
Better persistence of user applied formatting
Finally, we have improved the persistence of user-applied formatting in OLAP PivotTables. The screenshot below shows a PivotTable where Iâ€™ve manually change the name of a bike to â€œOur Classic Mountain Bikeâ€ by typing the new name into the cell, and where I have also made the text bold + italics and then set the cell background color to red.
Now, if I collapse Mountain Bikes in the PivotTable (which hides the individual mountain bikes), and then expand Mountain Bikes again, the mountain bike I formatted will still be formatted exactly like before I collapsed Mountain Bikes. In current versions of Excel, all the formatting is lost in this scenario. A small item, but one I am sure folks will be glad to see â€œfixed.â€