Thursday, January 23, 2020

In-Field and Out-of-Field Educator Designation Report

Over the past week, school districts in Massachusetts received information as to whether their teachers are designated as "in-field" or "out-of-field" for the courses they are teaching. Under new requirements under the Every Student Succeeds Act (ESSA), the Department of Elementary and Secondary Education annually summarizes this information and posts it in School and District Profiles and School and District Report Cards.

This information was delivered in an Excel spreadsheet to the DESE Security Portal > Drop Box Central > EPIMS File Exchange, and has the file name like In_field_data_2019_xxxxxxxx.xls. If you convert this file to a Google Sheet, you can use it as the data source for a simple report I've created to explore this data.

This report uses a pivot table as a filter, so that you can click anywhere in the summary table to filter the data. In the example below, I've clicked the "N" column for Sample1 School, and the list below updates to show the 6 educators who are Out-of-Field and why.

Feel free to check out the report template (which contains sample data), and follow the instructions to connect your own Google Sheet as the data source to create your own copy of the report. As always, please make sure the link sharing settings on your reports and data sources are are turned off, so that if you need to share the file you are sharing only with designated, authorized individuals.


Upcoming Google Data Studio workshops:
2/11/2020 @ Southeastern Massachusetts Educational Collaborative (Dartmouth, MA)
3/31/2020 @ The Education Cooperative (Walpole, MA) 

Upcoming Conference presentations:
2/28/2020 @ METAA CTO Clinic (Milford, MA)
3/6/2020 @ MASCD and MassCUE Leadership Conference (Worcester, MA)
3/27/2020 @ Medfield Design Your Learning Day (Medfield, MA) 

Tuesday, January 14, 2020

Simple School or District Staff Directory Tool

Here's an idea for a simple Google Data Studio tool that your school or district may find useful! Not all staff members have access to the Staff module of your SIS (such as Aspen), so answering questions like, "Who are all the reading specialists in the district?" or "Who are the members of the math department?" or "What is Mrs. Smith's room number?" might not be all that easy to answer without asking a secretary or an administrator. Here's an example of a district/school directory using Google Data Studio. Users can search/filter by school, name, department, or job title, or sort by clicking the column headers.

Want your own version of this directory? This is a great project for anyone new to Google Data Studio.

First, create a data source by pulling the following fields from the Staff listing in your SIS and placing them in a Google Sheet (you don't need to share it). Make sure the column headers match this list exactly (feel free to make a copy of my sample data source to use as a template):
  • First
  • Last
  • Email
  • Ext
  • Room
  • Position
  • Department
  • School
Next, access the example above and click the Use Template button in the upper right corner of the screen. Follow these instructions to connect your Google Sheet to the report.

Finally, you need to decide who you would like to access your new report. Use the "Share" button to share as view-only with specific individuals, with everyone in your GSuite domain, or with anyone with the link. If this is a district or school directory, I would suggest sharing it with the domain, so that only members of your school or district will have access to the directory when they are logged into their school accounts.

As the editor of the report, you can then modify the color scheme to match your district's colors, add a logo or text, or make other modifications as desired. You can also embed the report into a web page for easy access.

You will need to manually update the Google Sheet on the "back end" periodically to make sure the report is up to date. I hope to be able to share in the near future a solution for automatically updating a Google Sheet from your SIS, so stay tuned!


Upcoming Google Data Studio workshops:
  • 2/11/2020 @ Southeastern Massachusetts Educational Collaborative (Dartmouth, MA)
  • 3/31/2020 @ The Education Cooperative (Walpole, MA) not yet open for registration
  • 2/28/2020 @ METAA CTO Clinic (Milford, MA)
  • 3/27/2020 @ Medfield Design Your Learning Day (Medfield, MA) 
Lately, I have been tweeting more and blogging less, so please follow me on Twitter @tiltondata to stay in touch! As a reminder, you can find my complete Google Data Studio report catalog at