In the fall of 2016, I was part of an interdisciplinary team made up of students from both Information Systems and Public Policy programs to work with the Turtle Creek Valley Council of Governments to work on their Code Enforcement program. The COG had a three agents in the field inputting information into an Access database, and was creating fairly simple reports of each month's activity in each participating municipality.
Our team had two major recommendations -- upgrading to a PostgreSQL database, and creating more informative and automatically updated reports. We went through a narrative inquiry process with different users to understand the existing system. A prototype database and report template were developed based on iterative feedback from the agents inputting the data, the city managers using the data, and from other potential users in other cities.
Our objectives in creating improved reports were to give the city managers and council members more context about the code enforcement practices by comparing the current month's data to overall trends, and to create some graphics and maps that would make them easier to understand. (Note: Maps, municipality names, and other identifying information have been removed in the reports shared here.)
We also wanted these new reports to be as easy to use as possible, so as to not create more work for existing staff. We created a template in Markdown, and then an R script that runs an individual report for each municipality.