We’re excited to announce that Marco Russo and Alberto Ferrari’s Microsoft PowerPivot for Excel® 2010: Give Your Data Meaning (ISBN 99780735640580; 416 pages) is now available for purchase!
You can find the book’s Contents at a Glance and an excerpt from the Introduction this previous post.
In today’s post, please take a look at Chapter 2, “PowerPivot at Work.”
We are now going to introduce some of the most interesting features of Microsoft SQL Server PowerPivot for Excel. The goal of this chapter is to show the most frequently used PowerPivot features for transforming a simple Excel workbook into a complex report that helps you perform analysis on data. This is not yet the place for more advanced topics, such as the DAX programming language or complex relationships. Nevertheless, after you read this chapter, you will be able to perform complex analysis on a relational database and—we hope—still feel the need to go forward in your reading to discover the most advanced uses of PowerPivot.
Please note that we sometimes refer to the end user or the user experience as if we think that your PowerPivot workbook might be used by somebody else. To make a good report, you always need to think in this way. Even if you are the only user of a specific report, a user-friendly report is easier to read and update even after some time has passed since its creation.
Let us start with a very simple report, based on the same three tables that you loaded in the previous chapter: Sales Order Header, Sales Order Detail, and Product.
If you create a PivotTable with PowerPivot and put OnlineOrderFlag and SizeUnitMeasureCode on the Report Filter pane, Size on Column Labels, Color on Row Labels and the OrderQty as the value to sum up, you end up with the report shown in Figure 2-1, which you can find in the workbook named CH02-01-FirstSample.xlsx in the companion content.
Figure 2-1 A simple report using PowerPivot.
Before analyzing more advanced features, let us recall briefly what is going on:
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.
Figur e 2-2 Wrong display of numbers if format strings are applied to the PivotTable.
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.
Even if the report shown in Figure 2-1 contains interesting information, it has a problem: it lacks a format for numbers. In Excel worksheets, the formatting of numbers is one of the functions of the worksheet itself. So, to format the numbers properly, you select the data area of the report and choose a proper formatting. If you follow this procedure in a PivotTable, the first result is not very appealing, as you can see in Figure 2-2.
Figure 2-2 Wrong display of numbers if format strings are applied to the PivotTable.
Because you applied the formatting after you created the PivotTable, none of the columns were large enough to accommodate the new representation of numbers, which now contain dots and commas, resulting in larger columns. You can solve this easily by resizing all the columns. Nevertheless, if you decide to change the measure displayed and use a different one (for example, ListPrice), you probably need a different format and different column sizes, and you probably have to resize the entire worksheet.
The correct procedure to follow is to use the PivotTable field settings to define a number format for the OrderQty column. To perform this, you can right-click inside a cell containing the OrderQty value and, from the menu, choose Value Field Settings, as you can see in Figure 2-3.
Figure 2-3 The Value Field Settings menu.
This option opens the Value Field Settings dialog box, shown in Figure 2-4, which contains many options. We are interested, for now, only in the number format, which you can view by clicking the Number Format button.
Figure 2-4 The Value Field Settings dialog box.
The Format Cells dialog box (see Figure 2-5) lets you choose a number format for the column in this PivotTable.
Figure 2-5 The Format Cells dialog box.
When you choose the number format you want (in this case, we have selected a number format with a thousand separator and no decimal places), the PivotTable resizes all the columns automatically, as you can see in Figure 2-6.
Figure 2-6 The PivotTable correctly resized.
This procedure applies number formatting to the current PivotTable only. If the same column is used somewhere else in other PivotTables, your choice in this PivotTable does not affect them. Please note that, if you change the measure shown, you must repeat the procedure to determine the number format of the new column used.
I have bought this book. It's excellent book.
I want to translate it into Russian.