Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®

308



LESSON 33 Creating a PowerPivot rePort

TRY IT

In this Try It, you’re going to build a PowerPivot report of your very own.

Lesson Requirements

To complete this lesson, you will create a report that shows the trend of sales by different product

categories and subcategories both with a PivotTable and pivot chart using PowerPivot data. You will

use the Adventureworks DW 2008 R2 database for this.

Hints

You’re going to need a good data source. We recommend the AdventureWorks2008DW R2



database that we’ve been using in the past lessons.

Make sure to take advantage of the new slicer functionality as well as the sparklines and



conditional formatting.

Step-by-Step

1. Import the following tables and create any needed relationships.

DimDate



DimGeography



DimProduct



DimProductCategory



DimProductSubCategory



DimCustomer



FactInternetSales



2. Choose the data points you want to put in your report. Our recommendations are shown in

Figure 33-7.

3. Now that your data points are in place, add a column for your sparklines. See Figure 33-8

for more information.

As you can see, to create sparklines you select the data range you wish to trend and then the

display range for the actual lines themselves. It is pretty straightforward. Experiment with

the formatting and layout features in the ribbon to see which options you like best.

Lesson 29 talks about working with sparklines in more detail ...