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:
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:
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:
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:
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:
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:
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!
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.
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:
Great tips – thanks Matt and ARCHET!
