skip to Main Content

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

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 9 Comments
  1. 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!

    1. 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:

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

  4. 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 Reply

Your email address will not be published. Required fields are marked *