“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?”



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:


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.


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:


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.


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


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:


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:


Add Page Filters and Named Ranges to “Destination” Workbook


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.


With Page in Edit Mode, Add This Web Part


Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 8 Comments

  1. ColinBanfield


    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!

    1. powerpivotpro

      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. Carly Kaufman

    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?

    1. powerpivotpro

      Yes that is indeed one of the limitations.

  3. binh cao

    Where is the sample excel workbook for this blog article?

    thank you

  4. kmtaha

    Great Post! Thank you for sharing! So helpful, can you please attach the sample workbook? Thank you.

  5. Suneil

    Great post indeed! I got a solution up an running based on this. Opened up a whole bunch of interesting and useful reporting options. Thanks for sharing.

  6. Diptesh

    Hi Rob,
    You have a excellent post for hyperlink.. We have some problem if you could assist us…As we have a design a SSAS Tabular model in VS Studio and used Power view sharepoint Dashboard to Analyze the data. also we have created the WebURL Drillthrough link in tabular model to show the SSRS report. we have to use the CTRL and CLICK option on all the Windows machine it’s working fine BUT problem in MAC and Linux machine is not working the link… So do you have any idea how can we resolve this issue..

Leave a Comment or Question