Microsoft Office Tutorials and References
In Depth Information
Before analyzing more advanced features, let us recall briefly what is going on:
■ The OnLineOrderFlag, coming from the SalesOrderHeader table, is a TRUE/FALSE value.
PowerPivot found only two possible values for it, so it has been able to ill the combo
box of the filter with the values True and False. By choosing True, you selected only the
orders placed online.
■ The same process happened for the SizeUnitMeasureCode, this time coming from the
Product table. It can contain only two distinct values (empty and CM). You have selected
CM as the measure unit for the size.
■ Placing Color on the rows, Size on the rows, and finally OrderQty as the value,
PowerPivot analyzed all the rows containing the value (which is OrderQty, contained
in the SalesOrderDetail table). Then it followed the relationship between Order Detail
and Products and filtered out all the rows from the detail that do not satisfy the filter
condition. In the meantime, it removed all the rows that do not satisfy the condition
on the OrderHeader table, which contains the OnlineOrderFlag.
■ Having detected the set of rows that you want to analyze, PowerPivot followed the
relationship between SalesOrderDetail and Product to find out the color and the size of each
product sold, summarized up all the quantities, and displayed the final PivotTable.
Do not worry if the process described here is not perfectly clear; it will become easier to
understand as you continue reading, thanks to the many examples we are going to provide.
But remember this important point: the presence of relationships is essential for PowerPivot
to detect the set of rows it must take into account from the source tables.
Important You can easily understand why relationships are an important concept in PowerPivot
when you remember this significant difference between PowerPivot and the classic Excel PivotTable:
the older tool analyzes only one table and so it does not need to relate it with anything else—its
analysis is carried on a single object. On the other hand, PowerPivot can analyze more than one
table at a time but to do that, it needs to relate the tables to produce useful results.
In the next chapters, we spend several pages in the analysis of different kinds of relationships
and how to master them. Nevertheless, before diving into complex analysis, let us solve some
minor problems in this sample report to make it more appealing and a smoother introduction
to all of the PowerPivot features.