For Much of Last Week, Our Power Pivot & Power BI Book Topped the Spreadsheet Category on Amazon (Think about it: more people learning about DAX than learning about VLOOKUP or SUMIF is a MAJOR development!) Awareness That’s Been a…
by Matt Allington Today I am sharing a trick that I have used a number of times for clients – it allows you to use simple VBA to jump from one pivot table to another, and when you arrive at the second pivot table it is automatically filtered to show the context for the data you want to see. It is a great user experience.
Here is a sample use case and demo
You are browsing and drilling into a product hierarchy looking at the change in performance vs last year. You drill down to the product level and want to see the weekly sales for that particular product. You simply click on the “show detail” button and you are taken to a detail page that shows you the details for the specific product you were looking at. Let me share the process of how to build this interactive report.
Use the VBA Recorder to do the heavy lifting
After I set up my 2 Pivot Tables, I recorded a couple of steps with the VBA Recorder to help me write the code. Here is the process I followed.
Recently I have been building some interactive charts for one of my clients using techniques that I have learnt from powerpivotpro.com, from searching the Web, as well as some of my own ideas. While some of the techniques I will talk about in this post are not new, I have combined some of these old favourites with some new techniques to solve some of the problems I have come across. I want to illustrate how the combination of these things can deliver a very positive user experience, and just as importantly – anyone with a good set of Excel skills can build an interactive charting tool like this by following the patterns demonstrated. I have created a demo of all of these concepts into a new workbook using Adventure Works so that you can see how these techniques come together for the user. There is a link to this workbook at the end of this post.
The techniques I have used are:
- Disconnected slicers used to create interactive chart series
- Cube formulae and standard Excel to make an interactive chart title
I love these 2 tips I learnt from Rob – so user friendly. However I came across a few problems when I tried to implement these, hence I have developed the following 3 additional techniques to solve these problems.
- Cube formulae and standard Excel to make an interactive legend
- VBA and “link to source” for interactive axis formatting
- Excel VBA to change which Axis the series appears on.
I have created a short video to demonstrate the 5 features built into this workbook, and I then explain each of these in more detail below. I have not hidden the behind the scene workings so you can see these in action – of course you would normally hide these from the user.
Now let me call out the key techniques I have used to make this workbook rock.
OK, you’ve been a good Power Pivot author and given your measures clearly descriptive names.
Your punishment is spending all day looking at pivots like this:
Hey, Where’s the Rest of My Information?
(Hint: It’s in “Scrollsville.”)
MUCH Better: Last Two Measures Completely Visible, With Space to Spare!
(Assuming Vertical Space Isn’t a Problem, Of Course)
A Trick I “Harvested” From a Client
Awhile back I was working with a gentleman named Tom Phelan who repeatedly used a series of click mouse clicks to achieve the sort of layout pictured above. After seeing him do that about ten times I asked him to slow down so I could see what the clicks were.
Guest Post by Dany Hoter
After I published a post about manipulating relationships, Rob suggested that I take a step back and cover the entire scope of what’s possible with the object model.
Can you build a model from scratch? Can you add a new table to an existing table? Can you add calculated columns? What about calculated measures? , Can you change a connection for an existing table in the model?
The short answer to these questions is Yes, Yes, No, No, Yes
The longer version is the rest of this post. Everything in this post is NOT possible in Excel 2010 – this stuff works in 2013 only.
The object model consists of the following elements:
The only property that I found useful in this list is ModelRelationships collection which I used extensively in the previous post.
The ModelTables collection looks promising as it contains ModelTableColumns and could be the way to introduce new tables, new columns or even measures into the model. Unfortunately all these collections are read-only and cannot be used for adding to the model.
So how is still possible to add new tables or even to start a model from scratch?
It all has to do with the method add2 of the Connections collection.
Guest Post From Dany Hoter
Intro from Rob: Ah, the international man of mystery returns! My first instincts when I think of Dany Hoter, other than “one of the most fabulous humans I have ever known,” generally can be summarized as “MDX and Cube Formulas Monster.”
But he’s far from a one-trick pony. Generally speaking, he has a level of tenacity and patience rarely encountered outside of laboratory conditions. Couple that with an insatiable drive for The Right Thing, and you get some crazy results.
Today is one such CRAZY example. Simultaneously, he shows us how to compensate for a drillthrough bug, AND delivers a working example of relationship manipulation via VBA macros.
THIS IS AN ADVANCED TOPIC POST. Feel free to skip this one. This is the deep end of the pool and even I don’t swim in these particular waters yet.
Note of course that this technique is 2013 only, and will not work with Power Pivot in 2010.
Take it away, Dany…
A Drillthrough Bug with Inactive Relationships
I started this VBA project after one of our partners wrote to me about a customer complaint regarding inactive relationships in Power Pivot:
The Sales Table has TWO Relationships to Calendar – One is Based on OrderDate (Active), and the Other is Based on ShipDate (Inactive – Dashed Line)
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!
Note the “Readout” that Displays the User’s Slicer Selections:
Now Do This for Every Pivot in Your Workbook With One Click!
A Common Trick, Now Automated
This is something we do all the time at Pivotstream – we write formulas that capture user slicer selections and then display those selections back to them.
We do this via hidden report filters:
(Yes, we could also do this with cube formulas, but we started out (literally years ago now) using this approach and we’ve just kinda stayed with it. I’m not sure cube formulas would be better, but they might be.)
Why is the Readout Useful?
Why do we do this? Well, for one thing, the Download Snapshot feature on the server does NOT download the slicers – you get a big blank white space where the slicers were, which isn’t terribly helpful. It leaves you wondering what you had selected.
It’s also useful when there are slicers on other sheets impacting your current sheet. And even on a single sheet, it’s often nice to have a compact readout of your selections without having to scroll (or even scan with your eyes) to see what selections you have made.
One of those simple but indispensable tricks
Back to a “real” post now after all the book stuff, but it’s going to be a short one while I get back on my feet.
Let’s say you have multiple worksheets (or workbooks) that all contain the same sort of data:
Multiple Worksheets (or Workbooks), All Contain The Same Type of Data
You Want to Combine ALL of Them Into a Single PowerPivot Table
These worksheets all come to you separately, but really you just want them as one big table.
Naturally, if it’s a small number of sheets, and each sheet isn’t massive, you can just copy paste them all into one table in Excel, then copy/paste into PowerPivot, or link the table into PowerPivot, or export as CSV so you can import it.
And you could also use Paste Append to directly paste into PowerPivot.
But if the combined data set exceeds 1 million rows, you won’t be able to combine the sheets into one – you will exceed the worksheet row limit. And a data set of that size is not something you can paste into PowerPivot directly with Paste Append – pasting large data sets into PowerPivot takes forever, if it completes at all.
Here’s what I do when I find myself in this position:
Our First Post on Excel 2013 Beta!
Guest post by… Kasper de Jonge!
Notes from Rob: yes, THAT Kasper de Jonge. We haven’t seen him around here much, ever since he took over the Rob Collie Chair at Microsoft. (As it happens, “de Jonge” loosely translated from Dutch means “of missing in action from this blog.” Seriously. You can look it up.)
1) Excel 2013 public preview (aka beta) is out, which means that now we’re not only playing around with PowerPivot V2 and Power View V1, but now we have another new set of toys to take for a spin. I am literally running out of computers – I’m now running five in my office. Kasper is here to talk about Excel 2013.
2) I’ve been blessed with a number of great guest posts in a row, and there’s already one more queued up from Colin. This has given me time to seclude myself in the workshop and work up something truly frightening in nature that I will spring on you sometime next week. But in the meantime, I hand the microphone to an old friend.
Back to Kasper…
Inspired by all the great blog posts on doing a Dynamic Top X reports on PowerPivotPro I decided to try solving it using Excel 2013. As you might have heard Excel 2013 Preview has been released this week, check this blog post to read more about it.
The trick that I am going to use is based on my other blog post that I created earlier: Implementing histograms in Excel 2013 using DAX query tables and PowerPivot. The beginning is the same so I reuse parts of that blog post in this blog.