Microsoft Office Tutorials and References
In Depth Information
Now the set represents a selection of combinations of sales territories and product categories,
as its name suggests. You can save it and look at what happens in the PivotTable Field List.
After you define a set, it appears in the special folder Sets at the bottom of the table list. In
Figure 8-35, you can see the PivotTable with the set placed on the rows.
FIguRE 8-35 The set represents our selection.
When you put a set on rows or columns, you filter the data, which imposes a filter context
that makes only the data in the set visible.
If you look carefully at the PivotTable, you might notice that something is missing: you have
no total for the territory group, nor any grand total. As strange as that might seem, this is
perfectly correct. You have removed from the set the items containing the member All for
the ProductCategory. This means that the set does not contain a filter context for Europe,
any category . So the cells that represent this selection are empty. The same obviously
applies to the grand total.
Sets are commonly used with dates. If, for example, you define a set that contains data for the
last two years, it is easy to quickly select the last two years in a report. Moreover, when you
want to update all reports to show, for example, the last three years, it is enough to update the
set, and all the reports are automatically updated to reflect the change in the underlying set.
Creating Dynamic Sets with MDX
In the previous pages, you learned the basics of set definition. Now we take a step further and
look at some advanced uses of sets. The sets that can be defined by means of the standard user
interface are statically defined. This means that you enumerate a list of filter contexts and then
create a set based on them. The items contained in the set do not change, whatever selection
you perform in the PivotTable. Moreover, to create the set, you need to know in advance which
items to put in.