So, which PowerPivot Tools do I use?

(At the recent National Annual Meeting in Orlando, I was asked this question about 50 times, so I thought maybe it would help if I just posted my answer online for anyone who was not there.)

As you might guess, even though I lead the development of the Commissioner Tools PowerPivot tables, (With some great feedback and assistance from a number of commissioners around the country to help fine tune those tools. Trust me, what we have was always made better by the feedback we received.) as a good “Data Dog,” I also use these tools (or, “eat my own dog food,” if you prefer) in order to provide Commissioner Tools analysis for my Council, my Area, and also to the other Councils in my Area who may not have someone who can complete the data analysis for them. It was important in the design of these tools to ensure we had something for everyone, so we made sure to include statistical, visual, and descriptive analytics, since no one method works best for everyone.

When I am working on my Council (or any other Council I may be assisting), I employ the following tools:

I provide these as both Excel spreadsheets, and as PDF documents, for those who do not have Excel. Remember, the DC Visual Analysis report is really an interactive spreadsheet, and while you can print this report (or make a PDF), it is much better used as a spreadsheet.

Now, this being June, and since we re-register our District & Council volunteers on June 30, I also included the Commissioner Tools “Commissioners without Contacts 2017” report in the information I provide to all the District Commissioners. This can be used to assist them in their deliberations on which commissioners should continue in their roles in the coming year.

So, how long does creating all those reports take? Well, it takes me at most 5 minutes to download the 4 needed reports from Commissioner Tools:

  • Assigned Units
  • District Contact Stats 2017
  • Priority Needs Units
  • Unit Health 2017

Then for the actual loading of data into the PowerPivot tools, about 3 minutes.

So, in less than 10 minutes I have completed the initial analysis of the Commissioner Tools data from the previous month. Then I spend probably another 5-10 minutes reviewing the results, and writing up the email to send the reports out to the Council Commissioner’s Cabinet and the District Commissioners.

So, 15-20 minutes total from start to finish.

Now sure, I have probably done this 100 times or more. So, I am certain I have optimized my workflow. But even if this takes you 30 minutes the first time, you’ll get better the more you do it. The reports will become more familiar, you’ll do some prep so you have your folders for your data and the tools all ready to go their job. But you too can get this process down to a point where it comes naturally.

At the Area-level, I really limit myself to only two reports (as they really are the best for a consolidated view):

  • Unit Contact Analysis
    (to get the complete overview of how each Council is doing)
  • Units Needing Attention
    (so my Area Commissioner can work with the Council Commissioners, when I do this for the Area, I add another “slicer” on the Summary page for the Council Name)

Of course, since I am now wanting to create these two reports in a consolidated fashion for a number of Councils, I also use the “Merge Tools” to help me prepare the data:

  • Merge UH (for Unit Health reports)
  • Merge DCS (for District Contact Stats reports)
  • Merge AU (for Assigned Units reports)
  • Merge PN (for Priority Needs Units reports)

Now yes, this takes longer since I need to download the 4 reports I need from each Council in my Area, then I need to merge them together (which takes seconds), before I can feed them into the PowerPivot tools. But I normally spend about an hour total on my Area (including creating the reports for each Council).

Remember, if you get stuck, don’t call Member Care. Instead, send an email to: commissioner.support@scouting.org and one of us who volunteer to answer those emails will get back to you as quickly as we can.