Example of Advanced Smartsheet Reporting & Interconnectivity
I have built a series of Smartsheet that collects data from 8 different sources and combines it into one easily accessible Dashboard that is interlinked with other Dashboards. We use forms to collect responses and store them in 6 Data Files. In addition we have a special Key File for each project, that tracks that project's billables automatically.
In the end we now have a completely automated system that is hands off, and relies off of employee end of day reporting. Supervisors can see profitability that pulls in references from a secret wage table. There are over 150 different full reference values generating cell links automatically.
Basically, let me show you what is going on
Note: All names are fake names from my favorite book series.
This hub is the homepage of our Dashboard system and is the default home page for all employees. Here employees can find important information and links. This is still under construction and I am still working on it. But for now it meets my needs:
We have a variety of forms to collect information. Each form collects different information relating to a workflow step. These forms write to a Data File regarding it's step. Here is one of the data entry forms:
Here is an example of one of our multiple data files. This data file has our employee names pixelated. But, basically everything reported each day is recorded. We record Project Name, how long they worked (down to the minute). We then use LENS formulas to figure out what the heck the duration is because Smartsheet can't do time math, I hacked it. This produces a value for for multiplication. The weight uses the shift value to determine what a person would've done if they had spent a full shift doing said thing. This puts everyone on equal ground.
You can see a variety of references on this. We reference the checkboxes so we can hide/show data, the projects for filtering, along with the weighted numbers. In addition we track the amount of "boxes" done which helps us determine our averages for our key files.
We have "Key Files" that go along with our "Data Files". These are hugely interconnected. They reference each other back and forth. They have a symbiotic relationship and they depend upon one another to work. We have a three-way symbiotic relationship with Key, Data, and Dashboards.
This is an example of one of our many Key Files. Each Project has its own. This breaks down the steps with the amount of time we allot for each step. This also has our average wages for the said step. This generates a list of what our profit for said step should be based upon their percentage of alotted time. We put the value of the box in manually. Everything else updates on it's own including the averages (clips/pages/documents).
One of the bread and butters of all of this interconnectivity is a mini reporting/dashboard in Smartsheet. This one will show all of our employee names, along with their average in the past 30 days. We can then break down the total they did, amount of days, along with their profit. We can also see if they hit our revenue goal of 60% profit or not. Along with this we use the new RANK EQ to determine the top performers. We pull in our "Reported Issues" along with this.
We use a secret "Wage" table, which I am not sharing for reasons. These are pulled into these calculations. When an employee gets a raise it is counted against their profitability. So one employee on this list makers almost double what others do, and their margins are still amazing. It helps us determine if someone is worthy of a raise when they ask for one, or if anyone is struggling and costing us money. We have a negative on here, who is an intern with a learning disability. He's part of a special program for a year long training so we can make him profitable
We have other Dashboards that show how everyone is doing from step broken down with details that are automatically generated. I can't share some of our "secret" ones we use for performance weights. But here's one of the ones I can share:
I hope you guys can see what we're pulling off in Smartsheet. Smartsheet has not shown any examples near this, that we feel. We basically were able to replicate everything we wanted so far. We're pushing hard by requesting features and being vocal about what would make our lives easier.
I feel like I have mastered Smartsheet and am eagerly waiting for new features to pursue and push this even further.
I hope this provides some insight of what can be done!
Nick Burrus, MAOL, PhD Doctoral Candidate
I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.