Custom Toooltips in Dashboards!

 
Power Pivot Dashboard Tooltips/Comments

Custom “On Hover” Tooltips on Each Cell in the Dashboard!
(The yellow dot and distortion around mouse pointer are GIF side effects and do NOT appear in Excel)

Question from PowerPivotPro School!

Got a great question the other day from Oscar, a student in PowerPivotPro School

[OSCAR]:  “Is it possible to have a tooltip in powerpivot which shows additional information based on the cells selected (or mouse roll over). the info to be displayed comes in from a table created with cube fuctions on the same data source. So the coordinates of the highlighted cells would be inputs for the cube formula and result displayed in a tool tip dynamically.”

My first thought was “no, not possible.”  Then ten seconds later, a guerilla-style hack came to mind.  And then, my reply:

[ROB]:  “Oscar you are a very, VERY bad man. I am now obsessed with this problem. There goes my Sunday.”

The Trick:  Hyperlinks to Nowhere!


 

Select a cell and choose Insert Hyperlink…

image

Your Hyperlink in Cell D5 Goes to Cell D5, Making the Hyperlink a “Do Nothing” Link

Now click on ScreenTip…

image

ScreenTip Button Lets You Enter a Tooltip

And that’s it!

You now have an “on hover” tooltip for cell D5!

End of blog post, right?

“Rob you SUCK!  That’s incredibly tedious!”

Yeah, who wants to go through that process for every cell.  Blech.

So, we create a “mirror” sheet, that has the same shape as our dashboard:

image

“Mirror” Sheet Has Same Shape and Location as Our Dashboard.
You Enter Desired Tooltips Here.

Then you MACRO That Sucker!

Go back to your dashboard sheet, make sure your mirror sheet is named “ToolTIps,” then run this macro called RunTheToolTipHack.  It calls two other macros, also included below.

This macro “grabs” the tooltips from your mirror sheet and assigns them all to the cells on your main sheet.  It’s magic.

(Anyone who makes fun of my macro code will be beaten with a nerf bat.  Or worse, asked to improve it.)

Sub RunTheToolTipHack()
    CubeFormulasToolTips ActiveSheet.Name, “ToolTips”
End Sub

Sub CubeFormulasToolTips(sSheet As String, sToolTipsSheet As String)
    Dim oMainSheet As Worksheet
    Dim oTipsSheet As Worksheet
    Dim oRange As Range
    Dim c As Range
    Dim sFormula As String
    Dim sAddress As String
    Dim sToolTip As String
   
    Set oMainSheet = Sheets(sSheet)
    Set oTipsSheet = Sheets(sToolTipsSheet)
   
    Set oRange = oMainSheet.UsedRange
   
    For Each c In oRange.Cells
        sFormula = c.FormulaR1C1
        sAddress = c.Address
        sToolTip = oTipsSheet.Range(sAddress).Value
        If Left(sFormula, 5) = “=CUBE” Then
            SetHyperlink sSheet, sAddress, “‘” & sSheet & “‘!” & sAddress, sToolTip, True, False
        End If
    Next
End Sub

Sub SetHyperlink(sSheet As String, sCell As String, sDestAddress As String, sToolTip As String, bFormula As Boolean, bLookLikeLink As Boolean)
    Dim oSheet As Worksheet
    Dim sFormat As String
    Dim c As Range
    Dim iColor As Integer
   
    Set oSheet = ActiveWorkbook.Sheets(sSheet)
    Set c = oSheet.Range(sCell)
    sFormat = c.NumberFormat
    iColor = c.Font.ColorIndex
   
    If bFormula = True Then
        oSheet.Hyperlinks.Add Anchor:=c, Address:=””, SubAddress:=sDestAddress, ScreenTip:=sToolTip
    Else
        oSheet.Hyperlinks.Add Anchor:=c, Address:=””, SubAddress:=sDestAddress, TextToDisplay:=c.Value, ScreenTip:=sToolTip
    End If
   
    c.NumberFormat = sFormat
   
    If bLookLikeLink = False Then
        c.Font.Underline = xlUnderlineStyleNone
        c.Font.Color = iColor
    End If
   
End Sub

Questions

  1. Does this work with pivots?  No, sadly, pivots don’t let you set hyperlinks on the cells.  In Power Pivot land, this only works with cube formula reports.
  2. Can the mirror sheet be constructed using formulas?  Yes, absolutely.  No need to manually enter tooltips.  In fact you could use cube formulas and text measures to FETCH COMMENTS FROM THE POWER PIVOT MODEL ITSELF!  Cue maniacal laughter!!!!
  3. Will these tooltips work in the web version of Excel?  Yes, they will.  I have tested it.
  4. Wait, I didn’t think macros ran on the web version!  That’s right, they don’t.  But your macros don’t need to RUN on the server.  You are merely using the macros to SET the tooltips.  The macros don’t even need to live in your report workbook, they can live in your Personal Macro workbook.
  5. What happens when I want to change the comments?  Well, you need to run the macros again.
  6. Wait, even if I am using formulas to fetch the comments???  Yeah.  You refresh your Power Pivot model, refresh the dashboards, and even refresh the mirror sheet.  Everything is updated.  But your tooltips will still be the “stale” tips until you run the macro again.
  7. So this won’t work with Power Pivot autorefresh?  Bingo.  It won’t.  If you have a Power Pivot server and have scheduled the reports to autorefresh, the tooltips will still be stale.  You will need to download the file and re-run the macros.
  8. That’s a tragedy.  Can you fix that with the HYPERLINK function?  These are good questions, it’s like you were watching me work on this over my shoulder or something.  No, the HYPERLINK function lacks an input for tooltip, otherwise we COULD do this all automatically.
  9. Shouldn’t we point this out to Microsoft and ask them to address this tragic shortfall?  Yup, damn straight.  On it.
  10. Why not just have a TOOLTIP() function and dispense with all of this fake hyperlink crap?  Doubly damn straight, and doubly on it.

image

You Have to be a Real Nerd to Be Disappointed About This.  Which, I am.

Am I Missing Something?

I have this spider sense feeling that a longtime Excel pro is going to now say “but Rob why didn’t you do X instead?”  I am actually hoping that there IS a better approach here, and this post will bring that to the surface.  So bring it on.

18 Responses to Custom Toooltips in Dashboards!

  1. About 3-4 years ago I was trying to replace a custom C# coded dashboard with Excel and Excel Services in SharePoint, and the inability to mimic what the code was doing for tooltips was pretty much the deal breaker. :( Not only content but formatting would be nice too

    • powerpivotpro says:

      Hi Steve! I’m curious – how “good” of a solution would the above have been for you, in that situation 3-4 years back? Would you have needed server-side auto-update or were the tooltips relatively static?

  2. Yuriy says:

    You can add cell comments instead of hyperlinks – they can be added to pivot tables too (via VBA macros)
    And “Show contextual tooltips” on pivot table should be turned off to see comments

    • powerpivotpro says:

      One big problem with comments, historically, has been that they don’t show up in the web version. Maybe that’s changed in 2013, I will check.

      • powerpivotpro says:

        Yes, OK, comments DO now show up in the web version. So the same basic macro approach can be used there.

        Also, Bill Jelen emailed me about using Data Validation’s “Input Message” feature. Which ALSO works on the web.

        Both of those are great. Probably better than my hyperlink hack.

        But so far, none of these allows a formula to set the comment/tooltip. All of them require VBA to set the tooltip. We need one that autofreshes the tooltips when the “mirror” sheet gets updated, WITHOUT the use of macros.

        • powerpivotpro says:

          Note that for 2010 server, I’m pretty sure the hyperlink hack is still the only way, as comments and data validation are “new” on the server in 2013.

  3. Yuriy says:

    Comments is just only way to get tooltips inside pivot tables (at least I don’t know any other)
    But outside there is a choice…

  4. powerpivotpro says:

    I don’t think either of those techniques is “server friendly.” Which is fine for many people but in my world, and increasingly for others, the server is THE way to share reports.

  5. Dave says:

    So, in the pivot table, we’re stuck with three dimensions (page, row & column). Tableau has a “Tooltip” dimension. That is, you drag a dimension onto it and upon hover (chart or cell value), that dimension’s detail will show as a Tooltip. Very useful feature

  6. m-b says:

    How about overlaying each column in the dashboard with a separate transparent 100% stacked bar chart referring to a ‘mirror’ sheet (each part of the bar being the same size as the cell). Then use the legend labels to enter comments and make sure the values are hidden using the cell properties in the ‘mirror’ sheet. That way things could be formula driven and server proof? Hopefully that makes some sense :-)

  7. Doug Driver says:

    This is great! However, is there a way to enter multi-line tooltips without using the macro approach? I’m using Excel & Sharepoint 2010. My dashboard is more free form and hence the macro approach would appear to not be applicable.

    BTW: first post/question, been lurking and learning here for 3 years. Power Pivot+SharePoint+PowerPivotPro.com = awesomeness..

    • powerpivotpro says:

      Hi Doug! I tried Alt-Enter and Ctrl-enter in the Screen tip dialog, and neither worked. Not sure what else to try.

      (I’m assuming you meant a single tooltip that had multiple lines in it)

      I think the macro approach still works though, no matter what shape the dashboard is. As long as the “ghost” sheet is the same shape right? And it’s easy to make it the same shape – just copy the entire sheet.

Leave a Comment or Question