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 PeÃ±a 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.