Developing Business Intelligence Apps for SharePoint

Chapter 22. PivotTable Named Sets

When working with pivot tables, you often want to work with the same set of items from the data over and over again. For example, you might be responsible for help desk tickets from six different productions and want to create a set of reports about your product areas, but the list that describes which products are “your” products isn’t in the data source. Named sets in Excel 2010 give you the ability to create and reuse this logical grouping of items as a single object that you can add to PivotTables, whether it existed in the data source or not.

Beyond creating a reusable group of items for use in PivotTables, named sets in Excel 2010 enable you to:

• Create reusable groupings of common sets of items for reuse in PivotTables

• Combine items from different hierarchies in ways that otherwise wouldn’t be possible

• Dynamically change your PivotTables based on filters by using dynamic sets

• Create PivotTables based on your own custom MDX

To fully explore the power of named sets in Excel PivotTables, let’s walk through a scenario with the dashboard you created earlier in this chapter. Our goal is to look at recent trends in our ticket volume and the average time to closure. To do this, we will look at the last four years of ticket counts and the average time to closure over that time period.

We will use our second PivotTable that currently shows ticket count by creator name to complete this exercise.

Scenario: Last Four Years of Ticket Counts and Total Average Time ...