Sparklines are new to Microsoft Excel 2010 and Reporting Services 2008 R2.  In this post I will outline the steps required to create sparklines in an existing PowerPivot workbook.  Sparklines provide you with the ability to create a trending graph inline with your data.  So how do create these Sparklines.

Let’s assume that you already have a PowerPivot workbook that contains data.  In this example I will be using the AdventureWorks2008 database.  I have imported the following tables:  DimDate, DimSalesTerritory and FactInternetSales.   I am also assuming that you have already created a Pivot Table that resembles the following:

My columns in the Pivot Table are the CalendarYear from the DimDate table, the rows are the SalesTerritoryCountry from the DimSalesTerritoryTable and the SalesAmount from the FactInternetSales table is the value. 

Now that I have the Pivot Table created, I decided that I would like to see a graphical depiction of the sales for each country over each year inline with the data.  To accomplish this you can use sparklines.  To create the sparkline, click the tab labeled Insert and click the icon labeled Line in the sparkline section of the ribbon.  See the following:

When you click the icon the following dialogue box will appear:

In the textbox labeled Date Range you must specify the values that you want to trend.  In my sheet I selected all the data for every country for every year, excluding the column labeled Grand Total.  Similar to the following image:

In the above diagram note that all the data within the dashed lines are selected as the Data Range for my sparklines.  Next you must specify where you want to place the sparklines or the Location Range.  Select the empty columns to the left of the Grand Total column.  Ensure that you select the same number of rows that selected for the Data Range.  See the following image:

Click on the button labeled OK on the Create Sparklines dialogue box and now you have the sparklines.  Your speadsheet should resemble the following:

Now you not only have data, but you can quickly see how the data is trending for each country.  As always if you have any questions or comments please feel free to email me at pleblanc@pragmaticworks.com.

Talk to you soon,

Patrick LeBlanc, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.