PivotTable Named Sets in Excel 2010

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 “your” branches isn’t 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 “gold” 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’t be possible (what is sometimes called “asymmetric reporting”).
• Create PivotTables based on your own custom MDX.
• Create PivotTables that dynamically change based on filters by using dynamic sets.

Let’s see how easy it is to create one of these asymmetric reports with Excel 2010 that wasn’t 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 “Create 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’t really make sense here)
• FiscalYear2009 Actual
• FiscalYear 2009 All

Note: Subtotals and Grand totals can be easily identified because they contain an “All” member.

I’ll also go ahead and change my set’s name to something easier to recognize in the future like “2008-09 Actual vs. Forecast”. Let’s 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 “set” of items (like product bundles offered for Christmas or locations belonging to a particular regional manager), 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’d 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 “dynamic set”). For example, you can make a set that shows combinations of products and salespeople when you’re filtering by one manager, but shows products and sales channels when you’re filtering by a second manager.

A couple notes about Named Sets

You may be saying to yourself “haven’t 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 “short” introduction to the world of Named sets in Excel. We think they’re 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.