Office 2007 Professional Key PivotTables 12 Filte

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.
(Click to enlarge)
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.
(Click to enlarge)
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,Windows 7 Home Premium Key, 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.
(Click to enlarge)
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,Office 2007 Professional Key, 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.
(Click to enlarge)
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.
(Click to enlarge)
And now my report is showing only bikes with a profit margin greater than 40%.
(Click to enlarge)
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,Windows 7 Home Basic, I can hide the parent levels of the product name level. Specifically,Office 2007 Ultimate, 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.
(Click to enlarge)
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.
(Click to enlarge)
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.
(Click to enlarge)
Here is the final result.
(Click to enlarge)
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.
(Click to enlarge)
Now, if I collapse Mountain Bikes in the PivotTable (which hides the individual mountain bikes), and then expand Mountain Bikes again,Office 2010 Professional Key, 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.”
<div