skip to Main Content

 
Power Pivot Scorecard Mockup With Clickable KPI Metrics

Scorecard with Clickable “Key Performance Indicator” Metrics
(Yes, the colors are awful, sorry – I lack that touch)

Not Just for Sheet Navigation, But Also for “Drilldown”

Picking up from Tuesday’s post

When you click that “Customer Behavior” metric in the image above, you are taken to this detail page:

Detail Drilldown Report - Just a Pivot Designed by Me On Another Sheet

The single “19%” Number in the Scorecard “Expands” to this Full Detail on Click!
(Also note the “Back to Scorecard” Link)

That Was a Cube Formula Cell!

 

This “drilldown” effect is pure illusion, but a VERY effective one.  It’s just another link between sheets, just like the ones in Tuesday’s post, but this time, the link is set on a numerical cell:

Power Pivot Scorecard Mockup With Clickable KPI Metrics

Under the Hood:  Turned the Sheet Headers and Gridlines Back On,
Selected the Cell to Display its Cube Formula

Neat huh?

It’s just a big fat cell with a cube formula in it!  And then I set a hyperlink on that cell, with Screentip, just like in Tuesday’s post.

The concept of “sheet navigation” completely falls away in this situation.  The user of the scorecard quite seriously feels like they are “expanding” to more detail, rather than just flipping between sheets.

And that is absolutely ideal.  We want our work to be effortlessly digestible.  This sort of thing is Nirvana.  You might even say it’s On a Plain.  Tell the consumers of this scorecard to Come as They Are.  Hmm.  I apologize folks for this diversion.  You could say I am All Apologies.  Moving on.

Another Scorecard Example

Enough mockups, check out this production example from one of our clients:

Production Power Pivot Enterprise Scorecard for a $10B+ Company

A Production Scorecard With Clickable Headers that Navigate to Detail Views

That’s an Enterprise-wide scorecard, used by the C-level execs down through hundreds of regional offices, for a company with over $10 Billion in annual sales.  All built in Power Pivot in less than two weeks – from data import, to modeling, to reporting – and by a team of less than two (an executive at the company juggling his other responsibilities, plus me in a part-time assistance role).

Absolutely Badass, in other words.  And quite similar to the results we have seen at Kaman.  Mike and I really need to blog that project (the Kaman project) – the audience at the conference last week couldn’t stop asking him questions about their success.  I stood quietly in the shadows, nodding and smiling.

Can We Set Hyperlinks in Pivots?

Sadly, not directly.  PivotTable cells do not allow you to set hyperlinks on them.  Boooooooooo…..

But we can still fake it to an extent.  Check out this example:

Power Pivot Table With Links on Headers

Every Header in this Pivot is a Clickable Link to Another Worksheet

This is nothing more than YADT (yet another dirty trick).  Unhiding a row and a column of the worksheet reveals:

Power Pivot Table With Links on Headers

The Highlighted Row and Column Are Parts of the Pivot That Are Hidden
(The Row and Column With the Links Are Fake Headers I Made OUTSIDE the Pivot)

The HYPERLINK Function

The links on rows (1999, etc.) above are built, for convenience, using HYPERLINK() rather than Insert Hyperlink:

HYPERLINK Function Used to Produce Nav Links

Note How You Must Reference the Filename AND Sheet in Order to Link to Another Sheet
(And by referencing cell C4, we “grab” the label 1999 from the pivot)

For completeness, and for fill-down-ability, you should probably “wrap” that HYPERLINK in an IF, like:

   =IF(C4=””,””,IF(C4=”Grand Total”,”Grand Total”,HYPERLINK(…)))

That way you can deal with pivots that grow and shrink along Rows.  Of course, all of the links go the same place (AnnualReports!A1) in this example, so it doesn’t matter what Year you click, but I suppose even that destination could be constructed dynamically (most likely, all links would still go to the same sheet, but use MATCH() or something to find “1999” in the other report?)

Note that the HYPERLINK function is also an integral part of the “drill across” technique in SharePoint as well, which I blogged here.

Final note:  there is no Screentip argument to the HYPERLINK function, so you still must set those manually – or just suffer without Screentips (which is what I would do if I needed to use HYPERLINK).

Links on Charts!

Power Pivot Mini-Charts Link to Separate Detail Sheets

Each one of these Mini-Charts is a Link to A Separate, Detail Report Sheet

Just select a chart, go to the Insert tab on the ribbon, and click Hyperlink.  Or just do CTRL-K.

image

You can set the screentip too.
(Yeah, unfortunately you ALSO get the “Plot Area” tip on hover as well)

Great tips in last post’s comments!

Two of the comments on the last post were pure gold – definitely things I will use:

A Great Tip for Using Hyperlinks in Reports

A Great Tip for Using Hyperlinks in Reports

Great tips – thanks Matt and ARCHET!

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 8 Comments
  1. So the hyperlink formula has the file name hard coded into it. I have an application where I expect the file name to change occasionally as copies are made, etc. That breaks the link.

    The cleanest way I’ve found around the issue is have the file name in a support cell with a range name of “HyperFileName” and then incorporate it in the hyperlink formula. I might have several dozen or more of these links, or I would just put the file name formula directly in the hyperlink formula.

    I’d love to hear how others solve this problem. It seems way too complex, but I’ve had this working for quite a few years.

    Here’s the file name formula:
    =MID(CELL(“filename”,A2),FIND(“[“,CELL(“filename”,A2)),FIND(“]”,CELL(“filename”,A2))-FIND(“[“,CELL(“filename”,A2))+1)

    Here’s how it looks in the Hyperlink:
    =HYPERLINK(HyperFileName&”‘Tab Name’!a1″,”Tab Name”)

  2. Rob, do you happen to know if hyperlinks within the same workbook work when hosted in SharePoint enterprise and rendered using Excel Services? I don’t have an instance to test it out.

    1. They definitely do in SharePoint 2010 and 2013 using the ‘Insert Hyperlink’ UI.

      The =HYPERLINK() function definitely works in SharePoint 2013. It may work fine in 2010 as well but I never tried it and no longer have access to 2010.

  3. Another way to define links that are clikable averywhere is to made the calculation in another sheet, to apply style and to use snapshot function to get the value and style.
    Then you just have to add hyperlink to the new created picture.
    Moreover it allows to design results without taking care about rows and colums size and formula is not visible. It’s also posiible to rotate picture to created less strict design.
    Here’s an interesting explanation which seems to be too much : http://datapigtechnologies.com/blog/index.php/enhance-excel-2010-dashboards-with-camera-tool-and-picture-effects/

  4. I can’t lay my hands on it at the moment, but a couple of years ago, I put together a small macro for a client that allowed you to add hyperlinks to row labels in a PivotTable. While in generall we detest hidden rows/columns where I work as it is too easy to accidentaly delete or paste over the hidden data, your techniqe of hiding the label row and columns then creatinghte links in adjacent cells is much simpler than the macro solution I used earlier. And since the hidden data is in a PivotTable, you can’t accidentally delete it as you could with normal cells.

    I don’t want to Drain You Rob, but I have a small Sliver of an issue with the ‘And that is absolutely ideal paragraph’. It all Smells Like Teen Spirit. 😉

Leave a Comment or Question