Systems Engineering and RDBMS

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:

And then select the row lables and the summation values as shown in the figure below:

And we now have the data summarized by territory and within that territory, by the sales person for their YTD sales.

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.

Resources:

  • Same as mentioned in the previous post for PowerPivot – here.

6 Responses to “PowerPivot for Excel 2010 – II”

  1. […] Posted by decipherinfosys on August 16, 2010 You can download the standalone installer of Report Builder 3.0 for SQL Server 2008 R2 from here.  The download also includes some sample reports to make yourself familiar with the new features.  We had covered some of them in our posts before – Data Visualizations, Cache Refresh and Powerpivot – I and II. […]

  2. David Hager said

    Where is PowerPivot for Excel 2010 – III?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: