• 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.
    Quote Originally Posted by Bob Phillips
    on April 7th, 2011 at 12:28 am

    Interesting Ken. What happens when you have a large amount of upcoming events, won’t the slicers get unwieldy?

    BTW, I was interested when you wrote …Outlook has a weird way of storing appointments… especially when you get into recurring appointments. … Apparently Outlook doesn’t actually store the dates of the recurring appointments at all, only the first and the recurrence pattern…That surprises you? How would you have done it, store them all individually?
    Quote Originally Posted by Ross
    on April 7th, 2011 at 3:22 am

    Yeah I guess that way of storing them makes sense, but its horses for course, I guess. Its not like saving the actual data would take up a lot of room, and its more direct… I think I actually would have added them to the db as separate events, but then you have to deal with updating them etc, which is going to be less easy the other way around… horses for course… and what the hell do I know anyway…

    Good old Jim! , knew he would be good for something one of these days!?!

    I like slicers, I wish you could customise them a bit more though, they always look so big. Are they exposed via VBA?
    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 recurrance dates that I can look through. Currently, (if I understand it correctly,) you have to find the first date then parse the recurrance 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.
    Quote Originally Posted by Bob Phillips
    on April 8th, 2011 at 1:56 am

    Odd, I posted a follow-up which seems to have been lost. I suggested that you chuck all the data in a cube and pivot it.
    Strange… not even in the spam queue…

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


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

  • MVP Logo
  • Recent Forum Posts


    Count problem / logic

    Function solved


    stefan8871 Today, 01:58 PM Go to last post

    Count problem / logic

    Could I please ask for some more help?

    I'm trying to get this result on a different sheet. I've updated the document to reflect the change....

    stefan8871 Today, 01:52 PM Go to last post

    Count problem / logic

    Glad we have it sorted! It would have helped if you had broken your requirement down and told us your expected outcomes for each bit of it. ...

    AliGW Today, 01:16 PM Go to last post

    Count problem / logic

    That seems to be different to what you said previously, but how about this?


    AliGW Today, 01:15 PM Go to last post

    Count problem / logic

    Yes, this does it!


    stefan8871 Today, 01:14 PM Go to last post