Microsoft PowerPivot is a self-service business intelligence tool that lets you import SQL Server data into Excel. After you’ve imported that data, you might wonder what to do with it -- how to create a chart in PowerPivot, for example. Fortunately, it’s relatively easy to use PowerPivot to make a table or chart in an Excel worksheet.
Suppose you want to create a PivotChart that shows the annual sales each of your salespeople has generated over a certain number of years. On the Home tab of the PowerPivot window, click PivotTable and select PivotChart. You’ll be prompted to choose whether to create the chart in a new worksheet or an existing one. The worksheet will be displayed with the basic chart elements in place, as shown in Figure 1.
Figure 1. Creating a PivotChart with PowerPivot data
Notice that the area for the chart is at the center of the worksheet and the field list (tables and their columns) is to the right. To prepare the data for the chart, I created a relationship between the Person and Sales tables, and I created a calculated column that concatenates the first and last names of each salesperson.
To populate the chart, drag the columns from their respective tables to their intended position on the graph. Let’s begin with the FullName column in the Person table. Expand the table in the field list, and then drag the FullName column to the Axis Fields (Categories) box. Drag the SubTotal column in the Sales table to the Values box. Your chart should now show a graph with the salespeople’s names at the bottom and graph lines indicating each salesperson’s total sales, as shown in Figure 2.
Figure 2. Finalizing the PivotChart in Excel
Refine your chart by adding a slicer. A slicer acts as a filter that breaks the data into smaller categories; in this case, years. To add the slicer, drag the OrderYear column in the Sales table to the Slicers Vertical box. You can now view the sales amounts by year. If you want to view the totals for more than one year, press the Ctrl key while clicking from one year to the next.
This is a simple chart, but it provides a glimpse into a much larger range of data, an important element in any effective business intelligence report.
ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. Find more information at http://rhsheldon.com.
This was first published in May 2011