Blog

A close up shot of a man typing on a keyboard and taking notes on a piece of paper with data
April 6, 2021 10:00AM

String theory! ... Or, why your reporting should not be thwarted by dirty data

We are not going into theoretical physics today! The only science I’d like to address here is the behavior of incorrect data entry. When you decided to use user-defined fields to store custom tenant data, lack of end user training is one hurdle we all potentially face. The expectation is to put only the desired information in the right fields.

For example, if a user-defined field was meant to hold a digits-only dollar amount, and an end user performing data entry included a dollar sign or a comma in the field, this field can no longer be used to perform a calculation because the characters “$” and “,” make it impossible to do so. The fields themselves are flexible and are designed as a data type (in this case, “string”) that can handle any character data, so there isn’t any way to prevent these characters from being entered in a programmatic way. When you started using those fields, you may have let everyone know to put in only digits, but time has somehow worked away this diligence.

Of course the end user had the best intentions. But now, because we have non-numeric characters in a field in which we wanted to calculate a sum, an average, or a top 10, the mere presence of a single comma or dollar sign can cause a report to break. This can leave you wondering why you bothered with the fields to begin with! Even worse, you may not know why a particular report stopped working.

string-theory-image-1-1

Fixing broken reports

Fear not, friends—solutions abound! Solutions to even potentially train the end user. Data hygiene lives!

There are several approaches you can take, and each is empowering in different ways.

First, you will want to decide if you want to involve the end user in the data hygiene process. What I mean by this is whether you want to train the end user to do this process correctly. If so, fantastic! REdirect can write a simple report that highlights all the anomalies that need fixing. You’ll know exactly where you need to go to fix the bad data, instead of checking every single record for the needle in the haystack.

If you don’t care to fix the end user behavior, this is fine too. We can write a script that scrubs those characters out of those fields, and even assign it to a task that can clean that up on a regular basis.

A last option is to modify any existing reports that use these fields so that they ignore special characters when you are expecting to store only numeric values. Perhaps even engaging more than one of these options will set this straight for good.

If you’ve got reports sitting dormant because you don’t know what went wrong, reach out to us. We can figure it out and get your reports back up and running!

Redirect Logo Mark
Jack Freifeld
About the Author

Jack Freifeld

Jack Freifeld comes to REdirect Consulting with certifications in systems engineering/security, Cisco Networking, SQL SSRS/BI and Yardi Advanced reporting solutions. In Jack's 20 years working in Information Technology, he spent the last 10 at Empire State Realty Trust.  As Senior Business Application Specialist he worked hands on with …