An Interesting Use For Slicers

Over the past while we’ve been building a Dashboard report for our golf course. It’s got some historical information in it, but we’ve also pulled in things like weather forecasts. The intention is that our managers will be able to see where we’ve been over the past week, as well as look at the key measures that will allow us to staff appropriately for the next week.

We put out a prototype of the Dashboard report, and our Director of Golf said “This is cool. What’s the chance we could also have the events coming up over the next week listed?”

Wow, cool. This is a great thought. Combined with the weather, this makes it a forward looking document that should be really useful.

It’s not like we don’t have the information, either. We have a shared Outlook calendar that we use for recording all of our events. So it’s just a matter of getting the appointments out of the Calendar and into Excel. Easy, right? Ha!

Outlook has a weird way of storing appointments especially when you get into recurring appointments. It seems that if you run code looking for appointments it ignores the recurring ones. Run the code to get the recurring appointments and it gives them to you, but with the date of the first recurring appointment even if you select a recurring appointment from within a date range. (I pulled all appointments from 2011-04-04 to 2011-04-11, and was getting appointments from 2010!) Apparently Outlook doesn’t actually store the dates of the recurring appointments at all, only the first and the recurrence pattern.

After a couple of days fighting with this, (on and off,) I happened to hit Bing looking for help. Lo and behold, I found an article by Jimmy Pena that does it all! Sweet! (Thanks Jimmy!)

I had to make a very simple change to the article in order to make it work on a shared calendar, but that was it. Jimmy’s code pulls down the key pieces I needed and places it all in a nice table:
Now, what we did with it:

I really wanted something to put on the bottom of the report that looked nice, and gave the users appropriate information. I could have pulled this into a PivotTable and put it on the report, but I didn’t really like the look of it. Then I got to thinking; I like the way the slicer looks, I wonder if… So on a whim I decided to try something:

• I created a column for each type of event I wanted to display

• I created a PivotTable that showed the list of events
• I added a slicer off the Pivot Table for each of the event categories at the bottom of the report

So now, I have the following to place on the bottom of my Dashboard to show the team what events are upcoming over the next week (You can picture the charts and tables above that.)
I don’t think that anyone at Microsoft ever intended that someone would use slicers are the report output, but for our purposes it works.

Now, some observations here:

• I can’t get rid of the blank lines. The table formulas use “” at the end of the IF statements, but they still show in the slicers. Using NA() returns #N/A in the slicers.
• One could argue that it might be better to have these grouped by date, rather than by category. We may still do that, depending on the feedback from the team.
• It’s unfortunate that I had to link these to a PivotTable; I wish I could just create a slicer and give it a range to populate, like a chart
• Slicer formatting options are severely limited. You pretty much can choose a colour, to sort ascending/descending and if you’d like to shade unavailable options. (How about hide them all together?)

Granted, this may not be perfect for everyone, but I kind of like the use for this. It adds a bit of polish to our overall report, as the slicers are a bit more glamorous than a standard Excel table (with the rounded edges and all.)

I’m curious what you think of it.
Hi guys,

Bob, because we’re only looking at 7 days on the dashboard, and the slicers will hold 8 rows of visible information in this form, (7 if we leave the blank row at top,) I think we should be okay. Broken up by category like this, it would be quite rare that we would have more than 7 events in any one category in that time frame. In addition, the dashboard gets refreshed daily, and it will be the things that are a week out that wouldn’t be seen, so it will work it’s way through the next day.

Re the Outlook dates… I understand why they are set up as recurring appointments. What really got me is that I think there should be a collection of recurrence dates that I can look through. Currently, (if I understand it correctly,) you have to find the first date then parse the recurrence pattern to figure out if a day falls in your range. If we had a proper collection we could actually target the event much more easily using the following structure (or the FIND method).

i.e. IF Appointment("Event")recurrences(1).StartDate=#4/4/2011# THEN …

The existing method seems like a lot more work.

Ross, I’m just pulling stuff straight out of Outlook. The categories are all maintained there. I don’t really have any need to put them into a db at all, as this info can be retrieved at any time.

In this case, it’s actually the “big” portion of the slicer that I like. With the rounded bubbles, it looks like it belongs on a report and draws the appropriate attention. I do wish I had a way to hide out the blank records… I could probably do it if I created a bunch of individual pivot tables, but that does seem like overkill. Depending how irritating my users find the blanks though, I may yet still pursue that.
Strange… not even in the spam queue…

So you’re suggesting stuffing it into an analysis services cube? (As compared to PowerPivot?)