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.
Hi Patrick,
ReplyDeleteI don't see what this post has to do with PowerPivot per se. The post is about using a range in a PivotTable to generate sparklines, and there have been a few posts about this already.
Actually, it's not even about PivotTables at all - since this range could be any arbitrary worksheet range.
I like to use sparklines from PivotTable data to add historical context to measures in a dashboard. However, I find that showing sparklines next to a PivotTable is pretty useless. A user interacts with a PivotTable to analyze data in some way. Try drilling down on your countries and see what happens. Filter out a year and/ or a country and see what happens. The results in either case are not what you'd want.
To some extent, you can use VBA to resolve the issues, but the real solution would be native support for sparklines in PivotTables.