PowerPivot for Excel 2010 – II
Posted by decipherinfosys on June 16, 2010
In one of our previous blog post, we had covered the new feature – PowerPivot that has been introduced in SQL Server 2008 R2. In today’s post, we will look into powerpivot reports. A powerpivot report is essentially an excel spreadsheet that allows us to present our powerpivot data in a summary format by using a pivottable and/or a pivotchart. We can add slicers to it to support selective filtering of the data. Let’s continue with the data output that we had used in the previous post.
Let’s use PivotTable to create a summary from our data set – we can simply use the layout by selecting the PivotTable from the dropdown as shown below:
From this, we can click and remove the enteries or add filters in order to better summarize and present this data for analysis. We can also add slicers – click on “Insert Slicers” on the powerpivot ribbon and you will be presented the field options based on which you can then slice the data set. So, as an example – say we want to do that based on the territory name – we will have the slicer in this presentation:
We have selected the “Northwest” territory from the slicer and hence we see the data set based on it now. There are a lot of options that we can play with – we can select from the slicer settings, change the order, the look and feel etc. – all right from excel. We can add visual representations of the data which works hand in hand with the slicer and the powerpivot options. Here is a 3-D stacked up bar representation of the data from above:
If we want, we can also use cube functions (introduced since Excel 2007) in cell formulas to arrange the powerpivot data in a free form arrangement of cells.
This gives the end user a very effective and an easy way to look at and analyze their data sets since both the data and the visualization piece are kept together and the changes are reflected instantaneously for the end users. In the next post, we are going to take a look at DAX (Data Analysis Expressions). DAX is a new expression language that can be used with PowerPivot for Excel. DAX formulas are similar to the Excel formulas with the difference that instead of working with cells, arrays/ranges, DAX works with the tables/columns and can be used to create measures or calculated columns.
- Same as mentioned in the previous post for PowerPivot – here.