Microsoft Office Tutorials and References
In Depth Information
Even if this condition might seem obvious, sometimes you need to define a set that changes
dynamically, based on your selection, without knowing in advance what it contains. Think, for
example, if you could define a set containing the 10 top-selling products. You do not know
in advance which these products are. Moreover, the set changes, depending on your filtering
because you expect that the top-selling products in 2003 are different from the top-selling
products in 2004.
These kinds of sets are called dynamic sets because they can change their content based on
user selection. To create them, you need take a deep dive into the internals of PowerPivot and
discover the OLAP cube that lives well hidden in PowerPivot. Because a PowerPivot database
is essentially an OLAP cube, you need to use MDX to create dynamic sets. In this section, we
demonstrate what can be done by using MDX to query PowerPivot and create dynamic sets.
Note The topic of MDX requires a book of its own, so we are not showing all the details of
what you can do using this exciting query language. Our goal is just to bring its possibilities
to the attention of the interested reader by letting you see what happens when you query
PowerPivot with MDX.
To create a dynamic set, you need to select the Manage Set option of the Fields, Items & Sets
menu, which opens the Set Manager dialog box shown in Figure 8-36.
FIguRE 8-36 The Set Manager window.