#SmartPivot for Excel–Now, for the new OLAP Features

In my last post I covered a brand new feature in this release of SmartPivot-pivot viewer visualization-, actually not olap related, as in you don’t need a cube, just a regular Excel table.

image_thumb40 image_thumb36 image_thumb39 image_thumb7image_thumb5[1]

But SmartPivot still is, and will always be, a cube exploration accelerator for Excel. It makes things easier with features and quick-wins that are not available in Excel out-of-the-box (although they should honestly… and we actually have other things to do beside “fixing” Excel Olap browser usability issues… :) )

Anyway, we have a really hard time saying no to our users :)… when they ask, “well, you know… this pivot field list is always expanding, we have a hard time seeing the cube “big picture”… could you…well collapse it? toggle it?”… And well, so we have…

 

1) a Collapse/Expand Button for the pivot list fields! (this will work only on Excel 2010, sorry)

image

Select the Toggle Fields and toggle between expanded or collapsed fields:

image      image

 

2) you can search fields (measures, attributes, hierarchies), searching as you type – Excel 2007 & Excel 2010

image

image

it will do multi term search, just use a space to split the terms you’re looking

image

3) Filter by list of items, ex: Order Numbers – Excel 2007 & Excel 2010

image

Paste your items and apply the filter:

image

image

4) Detail a cell value in a new pivot with the Detail by feature-Excel 2007 & 2010

image

Select a cell value on the pivot you want to explore further, then select Detail value by:

image

That value will be isolated in a new pivot, you can immediately select another hierarchy to drill down/detail it. (The original pivot remains intact)

image

The new pivot is drill by the selected field. If appropriate a pie chart is automatically generated:

image

Off course you can start from this values into new Detail by actions, ex: detail Calgary.

image

5) Create a quick chart dashboard almost instantaneouslyExcel 2007 & 2010

image

Just select Create Dashboard, if no pivot table is selected, you’ll be asked for an analysis services connection:

 

image

Select the fields you want on the filters zone (you can always change them later):

image

And the new dashboard is created for you, all pivot charts, pivot tables and filters properly connected. Just edit each chart for the view you want.

image

Something like:

image

6) Quickly jump to a pivot chart data values (base pivot table), Just select a pivot chart and then Show Chart Data:

image 

We can then explore the values, using the Detail By feature for example.

image

That’s it for today. Grab a copy and tell us what you think. Happy cube browsing!

 

Rui

Advertisements

About Rui Quintino
Developer & Software Architect @ DevScope

2 Responses to #SmartPivot for Excel–Now, for the new OLAP Features

  1. Pingback: Shattering old myths (again): analysis services cubes can’t handle tabular reports with #excel, adhoc tabular reports won’t work with cubes & excel, -definitely busted with #smartpivot « Rui Quintino Blog

  2. Pingback: Browsing & exploring a “big” #kindle library, part II, now with #Excel2013 #PowerView « Rui Quintino Blog

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: