PowerPivot Slicer Lessons Learned

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).

powerpivot

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 🙂

http://channel9.msdn.com/Events/TechEd/Australia/Tech-Ed-Australia-2011/DAT303/player?w=640&h=480

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

image

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

clip_image008

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

clip_image010

So I looked for a way to keep them fixed size and found it – you need to highlight the AutoLayout Bounding rectangle

clip_image011

Select “Size and Properties” from the menu

clip_image012

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

clip_image013

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

image

as well as the ability to tailor the size & column configuration of the buttons of each Slicer

image

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

clip_image015

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

http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/dfa7afb3-946c-4e76-b591-c897c335fb19

Would appreciate any help or information that anyone can shed on this  – thanks

Weather

Advertisements

About bradosterloo

.NET Software Developer working for Innovative Systems, LLC in Mitchell, SD
This entry was posted in BI, PowerPivot and tagged , . Bookmark the permalink.

One Response to PowerPivot Slicer Lessons Learned

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s