Office Professional Plus 2010 PivotTable Named Set

Thanks to Diego Oppenheimer for putting together this post.
When working with PivotTables, you often come across scenarios where you want to work with the same set of items from the data over and over again. For example, you might be a regional manager and you want to create a set of reports about the branches in your particular region, but the list of which are 鈥測our鈥� branches isn鈥檛 in the data source, so you always have to filter to the same eight branches, like so:


Example of two PivotTable reports created using a named set of store branches
Of course the examples are endless: you might be a product manager who wants to show the set of products that you are responsible for in many different PivotTables to give different perspectives on the performance of your product line, you might want to have a set of your Top 10 partners, you might have a set with your best suppliers, you might have a set of your 鈥済old鈥� salespeople, etc. The point is that the Named Sets feature in Excel 2010 provides you the ability to easily create and reuse this logical grouping of items as a single object that you can add to OLAP PivotTables, regardless of whether this existed in the data source. Why Named Sets in Excel 2010?
Named sets are a very flexible tool that allows users to do a lot more than just create a re-usable group of items for use in PivotTables. With Named Sets in Excel 2010 you can: Create simple, re-usable, groupings of common sets of items for re-use in PivotTables 鈥� even when those sets are not present in the data. Create PivotTables that combine items from different hierarchies in ways that otherwise wouldn鈥檛 be possible (what is sometimes called 鈥渁symmetric reporting鈥�). Create PivotTables based on your own custom MDX. Create PivotTables that dynamically change based on filters by using dynamic sets.
Let鈥檚 see how easy it is to create one of these asymmetric reports with Excel 2010 that wasn鈥檛 previously possible in Excel PivotTables. Creating an Asymmetric Report with the New Set UI
A very common report layout needed by our customers is to show actual sales for the year 2008 while showing forecasted sales for the year 2009. Here is a PivotTable showing actual, budgeted and forecasted sales quotas for the last two fiscal years (click to see larger image).

As you can observe there is a lot of irrelevant information in our report such as Forecast for 2008 (since we have actual data for 2008) and Actual for 2009 (since this only includes 9 months of data). If I tried to hide Actual it would actually get hidden for both 2008 and 2009, but since I have Excel 2010 I can go ahead and create a set out my information on columns by simply going to PivotTable Tools -> Options in the ribbon, and selecting 鈥淐reate set based on column items鈥�.
Now the Named Set creation UI will pop up (click to see larger image):

The UI contains all the tuples that currently define the column labels of my Pivot report. Now I can easily remove the tuples I no longer want. In this case I am going to go ahead and remove: FiscalYear 2008 Forecast FiscalYear 2008 All (This is my subtotal for FiscalYear 2008 that doesn鈥檛 really make sense here) FiscalYear2009 Actual FiscalYear 2009 All
Note: Subtotals and Grand totals can be easily identified because they contain an 鈥淎ll鈥� member.
I鈥檒l also go ahead and change my set鈥檚 name to something easier to recognize in the future like 鈥�08-09 Actual vs. Forecast鈥�. Let鈥檚 hit OK and see what my report looks like now.

The new set is placed on the corresponding axis

If a display folder was not specified it will be stored under the Sets folder by default.
So now I have a reusable named set that can be used in new PivotTable reports or as part of cube functions. Of course this same feature can be used to create any 鈥渟et鈥� of items (like product bundles offered for Christmas or locations belonging to a particular regional manager),Office Professional Plus 2010, and I can use these across multiple reports. A set could also be used to build a report layout that would otherwise not be possible with PivotTables. Advanced Named Sets
I have covered how to easily create these sets through the UI, but this is just some of the power and flexibility that named sets add.
For users who are familiar with MDX, you are now able to create just about any PivotTable you鈥檇 like to by creating sets based on your own custom MDX definition. The set manager allows you to create a new set using an MDX editor, and also allows you to set advanced options on your set, including making the set recalculate its items based on its context (a 鈥渄ynamic set鈥�). For example, you can make a set that shows combinations of products and salespeople when you鈥檙e filtering by one manager, but shows products and sales channels when you鈥檙e filtering by a second manager. A couple notes about Named Sets
You may be saying to yourself 鈥渉aven鈥檛 I used named sets in Excel before?鈥� Well, Excel has allowed you to access some named sets that exist on an OLAP data source for a number of versions. In fact, you could even create named sets using the Excel object model in previous versions of Excel, so long as you could figure out how to do so, write MDX for the set, and so long as your set was one that previous versions of Excel could support (the named set consisted only of members from a single hierarchy and was not dynamic). More detail on this can be found here.
Lastly, in Excel 2010, only OLAP PivotTables support named sets. We hope to add named sets to all PivotTables in a future release of Excel. Conclusion
So there you go; a 鈥渟hort鈥� introduction to the world of Named sets in Excel. We think they鈥檙e an addition to Excel 2010 that will make PivotTables a whole lot more flexible and powerful for users. I would love to hear back about how you are putting this feature to use. <div