Office 365 Business Reporting Tool

Office 365 Business Reporting Tool Hacking For Defense

Who is the DLA?

The Defense Logistics Agency provides Global, end-to-end and supply chain solutions to aid the warfighter.

The DLA is organized into 8 business units called JCODES. Our team is collaborating with J6 - Information Operations to address their current process of reporting up through the organization. Currently, J6 uses a reporting system that fails to support efficient collaboration between departments and is disorganized when it comes to collection of large data.

DLA Information Vid

What is Hacking for Defense?

Hacking for Defense is a project-based course focused on bringing together students with diverse backgrounds to apply design thinking to real-world problems. This program is a partnership between James Madison University and the Department of Defense.

Meet the Team

Andrew Hunt, Brandon Robison, Eric Medley Jr., Jackson Burns

Andrew Hunt - Computer Information Systems Major, Global Supply chain Management Minor

Brandon Robison - Management Major, Innovation & Entrepreneurship concentration

Eric Medley Jr. - Management Major, Global Supply Chain Management minor

Jackson Burns - Computer Science Major

The Problem

Difficulties created from emailed spreadsheets

There are number of different reports distributed throughout the entire J6. Many of these reports are being distributed as spreadsheets sent through email.

This process of sending spreadsheets through email creates difficulties for J6 staff to efficiently collect and run analysis on reports.

From this described process of reporting, we discovered some common pain points through our interviews with DLA Staff

Version Control
Privacy of Data
Human Error
Cutting/Pasting Spreadsheets
Report Analysis

Our Solution

Over the course of this semester our team was tasked with finding tools under the Microsoft Office 365 to provide an alternative way of reporting that addresses these pain points...

Our team has created a SharePoint environment to shadow the current infrastructure and operations of J6's SharePoint. We needed to mimic their current SharePoint environment so that when our reporting idea is presented to the DLA, they can see it being easily implemented into their workplace.

The DLA doesn't use SharePoint to its full capacity and many employees find it daunting to navigate. Our goal was to offer a new way of reporting within SharePoint that is easy and useful to use for all staff. We have created two existing reports within the J6 to showcase our solution in action:

Vaccine roll out - for monitoring who has, can, and will get the vaccine
DMAP Shares - for employee bonus pay based on rating (Employee Performance)

Working through this project was an iterative process of finding/learning an Office 365 tool that solved our problem at hand, hitting a wall, and then finding a new tool that would get us over that wall until a complete front to end solution was built. Now Lets talk about these "walls" we hit and which tools got us over them...

Replacing Spreadsheets with Forms and Lists

Occasionally, staff who are filling in a response to a spreadsheet may make a mistake manually inputting data. Using shared spreadsheets, one user could accidently overwrite another users response. These mishaps create tedious work for program managers to fix.

Our reporting solution is built around using Microsoft Forms for users to enter data and respond to reports. Forms provide a more interactive way of filling in information, and allow for data lookups which will reduce the amount of human error in reports.

A snippet from our DPMAP Shares form

Lists are used to store all the Form responses in a centralized location. Permissions can be set on different Lists, which addresses the issue of privacy of data, addressed in some of our interviews.

"Glorified Spreadsheet" - The structure of Lists look similar to spreadsheets, but offers more capabilities as it can be integrated with many other Office 365 Tools

Addressing Version Control with Power Automate

Management does not have version control over the forms that are shared through email. Oftentimes program managers, who are managing roughly 300 responses to spreadsheets, are not sure if the spreadsheet his/her team is looking at is up to date.

By learning how to use Power Automate, which is within the Office 365 subscription, we could create "flows" based on conditions we set. Below is an example of conceptual and literal flow that we created so that users responses to Forms were automatically sent to a centralized List

Below is a video of what the end user will see when this flow executes

Graphical Displays using Power BI - Report Analysis

Storing our data in Lists allows for easier ways of reporting on data than with spreadsheets

Using Microsoft Power BI, we were able to connect our already populated List (with report responses), and perform calculations on our data to create graphical displays that are relevant to making business decisions within J6.

This is a screenshot of the Power BI desktop, showing our three connected Lists DPMAP Shares, Employee Info, and Leadership

Reporting Examples

Here is one report that calculates the number of shares & amount of money to be given as bonus for each staff member based on their performance score.

This shows how much money each employee was awarded, grouped by their organization

The Final Product

Our reporting solution offers an easy way to distribute and collect responses to reports throughout the J6. It managers can integrate all sorts of workflows into SharePoint using Power automate, and project managers can easily create necessary calculations and graphics aid decision processes. Although we cannot link our SharePoint to this website, below is a video walk through of the front-end of part of our solution

Appreciate