“THAT’s not a PivotTable! Oh wait…”
-Bill Gates
(OK, so I turned it into a movie quote of sorts by using the picture of Anthony Michael Hall playing Bill Gates in “Pirates of Silicon Valley.” Was that a good casting decision? I’m torn between “yeah it was OK I guess” and “well it WAS made for TV.”)
Setting that aside, the real Bill Gates DID in fact say that when we first showed him the new PivotTables in Excel 2007. Prior to that, the appearance of a finished PivotTable was something that the average user found off-putting and geeky. So we were pretty happy with that response to the new look.
PivotTables (and PivotCharts) have come a long way. A few clicks and you’ve got a very professional and customized report. But there are, of course, still cases where you need an even greater level of control than what pivots can give you. The good news is that if you find yourself in one of those cases, you still have other options.
Excel Pros – Do NOT tune out!
Excel pros are probably thinking “yeah, Rob, I know all about those other options. You make a PivotTable, you hide it on another sheet, and then build a report sheet using formulas, etc.”
I have a trick up my sleeve today, though. I bet most Excel pros have NOT seen what I am about to show you 
Here it is – Not Your Average PowerPivot Report!
(You’ll need to crank your volume up – I got a new mic and need to adjust its sensitivity).
Summary of what’s in that report
- PowerPivot-Backed – this report is indeed a PowerPivot report.
- No PivotTables anywhere – that’s right, there isn’t a pivot in the entire workbook!
- Custom formatting – I split the report body with horizontal and vertical dividers, and could place them wherever I wanted.
- Parameterization – users can play “what if” by changing input cells. A numeric column in the report responds to those changes in realtime.
- Sorting – the report is dynamically sorted by that numeric column in #2. Change a parameter, and the report re-calcs AND re-sorts.
- Respects slicers – all columns in the report respect the slicers on the page as well. This also impacts the sort order of the report.
How it was done
I’m going to go through it step-by-step in the next few posts. Yes, I am deliberately teasing you
Next Football Post: Introducing Cube Functions >>
I am guessing that you used “Convert to Formulas” to get the extra blank lines in there. But…how did you get those parameters to factor into the data without a refresh. Intrigue. I love it.
Sneaky. My guess is that he’s using cube functions with the Sandbox connection.
Shhh! You guys wanna ruin the surprise???
Heh heh, glad there’s some mystery here even for you two.
[...] more: Not your average PowerPivot report « PowerPivotPro By pivot | category: pivot, pivot table | tags: another-sheet, bet-most, excel, jorge, [...]
My guess its a “FlatPivotTable” option
You know…I went out and tried using Drawing Tools | Format ribbon to change the fill of the bounding box around the slicers.
I could get it to fill with a color, but as soon as you click away from the pivot table, that bounding box disappears.
So now I am thinking that you got the box around the slicers just by using cell fill and borders.
I REALIZE that the colored box behind the slicers is nowhere near the most amazing thing here, but that’s the one thing I had to go try out…
Not since Baywatch have so many been so teased…
Nice work. Historically I have had to do this with Xcelsius, which has its own buffet of limitations that PowerPivot seems to be dismantling with ease.
“Not since Baywatch…”
I love it.
[...] Kasper, though. The mystery of how the parameterized, sliced, auto-sorting report was built has simply been too much for him to bear, so the other day I sent him an advanced [...]
[...] Not your average PowerPivot report [...]