“Drill Across” in PowerPivot – Live Demo

Hyperlinks in a Pivot

“I’m telling you there are monkey-fighting hyperlinks in this Monday-to-Friday pivot!”

(Seriously this is how they cleaned up his line for TV, with “monkey-fighting” and “Monday to Friday”)

***UPDATE:  I am no longer working at Pivotstream and do not endorse their services.  All links are removed from this article but feel free to look them up if you are interested.

 

 

 

 

Retailer Competitive Overlap Application – New and Improved Live Demo

PowerPivot Retailer Competitive Overlap Application With Drill Across

Revamped/Simplified “Retailer Competitive Overlap” Application
(Note that the Row Labels Area of the Pivot Contains Hyperlinks!)

Clicking an Item to Get More Detail

The retailer overlap application is one that I’ve covered before, in my post announcing our live PowerPivot demo site, but I’ve recently spent some time improving it based on customer feedback and requests.

Specifically, our retail customers have asked the following:  “It’s great that I can see that Retailer X competes with me for our senior citizen customers much more aggressively than we thought, but can I get a list of the actual stores that overlap, with addresses?”

 

image

But WHICH Stores?  I Want to See the Addresses!

Hyperlinks in a Pivot!?

Let’s zoom in on the row area of the pivot pictured above:

image

Where does that link take me?

It opens a new browser tab, and a completely different PowerPivot workbook, parameterized to the selections you made above.

image

Destination Report Shows Me the List of Stores, With Addresses,
Based on the Selections Made in the Original Report

And the link at the top of that report takes me to the opposite list:  Target stores that compete with “my” CVS stores:

image

Second Page of the Destination Report Shows
the Corresponding List of the Other Retailer’s Locations

Other Improvements Made

Since the last revision, the application now includes Wal-Mart, Whole Foods, Costco, and Trader Joe’s locations.

How is this done?

This post is already getting too long for me to explain every detail, but here are the highlights.

First off, there’s no custom code here, no programming.  It’s all done using built-in features of Excel combined with built-in features of SharePoint.

image

Glimpse at the Innards of the Source Report, and How the Hyperlink is Constructed

image

Same Field From the Slicer is Duplicated on Report Filter
of the Pivot.  HYPERLINK Formula Then References Cell D9.
(And then Rows 8-10 Are Hidden)

This results in a hyperlink that looks like this:

https://insights.hostedpowerpivot.com/sites/demo/pages/Retailer-Locations-Drilldown.aspx?
Retailer1=[Retailers1].[RETAILER].%26[CVS%20PHARMACY]&
Retailer2=[Retailers2].[RETAILER].%26[TARGET]

The first line of that hyperlink is the same no matter what I select on the slicer (and which link I click), but the next two lines are constructed by referencing the row labels area of the pivot and the page filter of the pivot.

The %26 is CRUCIAL.  It represents the “&” character which is required in front of the “[TARGET]” for instance otherwise it’s not a valid PowerPivot selection id.  And since “&” is also used to separate between different parameters in the URL, browsers get confused if you use “&” in front of “[TARGET]” – so you “trick” it by sending the %26 code equivalent instead.

Took me awhile to figure THAT one out.

Parameters in Excel

Keeping it brief:  your destination pivot has to have page filters for every parameter it needs to receive:

image

Add Page Filters and Named Ranges to “Destination” Workbook

image

Now Add a Parameter for Each of the Named Ranges, Then Save Workbook

Last Step:  Filter Web Parts

This is a built-in feature of SharePoint.  I can add a “web part” to the page that “harvests” parameters from the URL and sends them to things like the destination Excel workbook.

image

With Page in Edit Mode, Add This Web Part

 

5 Responses to “Drill Across” in PowerPivot – Live Demo

  1. ColinBanfield says:

    Rob,

    I’ve reviewed the demo and must say that it is a superb drill-down…er, drill-across example. On this site, the one thing you’ve done better than anyone else on the planet is demonstrating how to *fully* use the current UI capabilities of Excel to build front-ends that users could interact with to make sense of their data, and provide some of the intelligence they need to make informed decisions. Keep up this great work!

    • powerpivotpro says:

      Hey thanks Colin! There really are a million little features of Excel, some of which like this parameters thing are pretty obscure (for now). My time spent at MS pays off a lot in that regard – I was around when a lot of these features were conjured.

      Once you move your consumers from the desktop Excel “frame” to the web, all of these little tricks and techniques become MUCH more impactful because they remove a lot of the fear people have of Excel and raise the ceiling on usability and friendliness quite a bit. I covered that before here: http://www.powerpivotpro.com/2012/01/in-the-browser-aesthetics-yield-a-greater-return/

      So necessity (or is it opportunity?) is the mother of invention, and my colleagues contribute a lot to these refinements over time.

  2. Rob – Great post! I’ve successfully implemented this solution. My only issue is that my URL is going over 255 characters which Excel does not accept. Have you experienced this issue yet?

  3. binh cao says:

    Where is the sample excel workbook for this blog article?

    thank you

Leave a Reply