Worked on a new PowerPivot dashboard this week – this time connecting straight to our TFS_Warehouse database since if anything is screaming for a customizable front-end dashboard – its got to be our Microsoft Team Foundation Server (TFS). But I wanted to jot down some of my PowerPivot dashboard building experiences for future reference of mine (and any others who come across this).
I had been working on queries into the TFS Warehouse for some time now for my own teams project management as I preferred gathering the data directly from TFS rather than building TFS queries in the Team Explorer window and creating multiple queries to gather everything I was interested in. So when I saw a fellow project manager looking to export into PowerPivot some TFS results in Excel which had been exported from TFS queries – that’s when I suggested we skip the middleman and that I would help her with a PowerPivot dashboard going directly against the TFS warehouse. Which has worked out great as long as everyone understands the few hours of lag time represented in the TFS warehouse as TFS changes get flushed to the Warehouse on some periodic schedule but not in real-time.
But as far as working with dashboards, have picked up a few tidbits along the way, either by checking out MSDN forums, Channel 9 videos, or just brute experimentation. Do have to give a shoutout to Grant Paisleys video below which was excellent and really started getting me doing a better job starting to fine-tune PowerPivot dashboards – Very good video (aside from the constant swag being thrown into the crowd throughout the video that as a viewer I cannot be a part of 🙂
But this video is where I found out my first Slicer piece of trivia
Lesson #1 – We are not constrained to only having slicers within the automated layout bounding rectangles that PowerPivot provides for Horizontal & Vertical Slicers as seen below – if the AutoLayout bugs you or you prefer to move Slicers somewhere else on your dashboard, clicking the bounding box of your specific slicer and dragging that slicer completely outside of the AutoLayout bounding rectangle frees it from AutoLayout control and places you in charge of where that slicer should be placed, its size, etc
Which brings me to Lesson #2– once in awhile, while wanting to drag Slicers in or out of the AutoLayout bounding rectangle, the rectangle would disappear and I’d start clicking all around the region trying to get it back which all I needed to do is make the “Field List” visible from the PowerPivot tab which automatically makes the AutoLayout rectangles visible again
Lesson #3– If your dashboard contains any Collapsible/Expandable PivotTables, you will find that the Slicers are by default fixed to the current column widths and as entries in the PivotTable are expanded, so are the Slicers as you can see below
Here you can see a Slicer named IterationPath
but once I expand a row in the PivotTable below this slicer to see what User Stories I have listed under “In Development”, you can see that the IterationPath slicer has just expanded pushing all other Slicers listed horizontally with it right off the page
So I looked for a way to keep them fixed size and found it – you need to highlight the AutoLayout Bounding rectangle
Select “Size and Properties” from the menu
And it will allow you to override the default behavior and force the Slicer to remain a fixed size regardless what the underlying cell columns do as a result of Pivot table actions
Lesson #4– Watching Grant’s video really showed me some of the Slicer configuration possible when you highlight a specific Slicer and then select “Options” from the Excel Toolbar as below to get to the various color scheme tailoring available for each Slicer
as well as the ability to tailor the size & column configuration of the buttons of each Slicer
This is powerful stuff and allows one to create some sweet dashboards – and all with a free add-in for Excel 2010 – it does not get much better than this.
Remove the Excel gridlines, change the background color and you have yourself a professional looking dashboard that people have a hard time believing is Excel.
And now for a question………
Before I go though, I have an issue I am hoping someone can help me fix or at least understand better which came up as a result from a question from an end-user
I have the below PivotTable, where its obvious that the user has selected a Slicer value of “In Development” which then limits the PivotTable to showing just values of that Activity – but if the user were to accidentally type over that value in the PivotTable, I seem unable to help them “undo” their actions nor to prevent it – see below example
1) I cannot lock down the worksheet and not allow any editing – as that seems to prevent any further Expanding or Collapsing of regions within the PivotTables
2) Refreshing the data both from the raw database and then into the PowerPivot worksheet has no effect either
I have posted this in MSDN forums (below) as this seemed like one of those trivial things that I’d be slapping my head once I learned how to correct but no one has slapped my head yet with any responses
Would appreciate any help or information that anyone can shed on this – thanks