Leaving aside those odd occasions when the database has exploded, most of the reports from users telling me ‘my report is broken’ are for one of three main reasons:
- User has forgotten how the report works
- Incorrect data does not meet criteria
- Developer error in implementing the report
The anatomy of a report
Reports can be broken down into two main parts:
- What I want to see (the column headings you can see e.g. Snowman_Name and Snowman_Location)
- The basis on which what I want is selected (’show me only snowmen with carrots for noses’ will not show you turnip-nosed snowmen)
What mostly seems to go wrong is that people forget the basis on which the data is selected. There may now be a disagreement about what constitutes a ‘new customer’ – your boss may think it means customers signed up in the last week, you might think it’s customers who have just placed their first order, whereas the actual report thinks it’s customers who signed up in the last month and are yet to place an order. What I’d like to emphasise here is that the report is not wrong. It has been given a definition of ‘new customer’ that has since been forgotten by everyone but the report. The report might now need changing, but it is not returning the wrong data, according to its instructions. I always like to emphasise that point, as people need to be able to trust that their reports will be correct, and the only way to do that is to prove that there is a logical reason for the output.
Fixing the report
As someone who spends quite a lot of her time investigating ‘broken reports’, the first thing I do is to pull the report apart and tell the user what it is actually doing in order to get them the data. Either rightly or wrongly, this is what the report has been taught to do. Nine times out of ten this provokes a discussion of what the report is being used for, and what would actually be meaningful for the report to do. It’s quite easy to forget that data has to have context and relevance in order to be information, and if you are no longer sure what a report is telling you, or have just assumed what it means, then you do not have the information you think you do.
Sometimes the report is doing what everyone thinks it is doing, but somehow, some data isn’t right e.g. you’ve indicated that there should be a fee, without specifying what the fee should be, so it isn’t calculated on the report. That kind of issue is more of a problem with the application itself – why were you allowed to make that mistake with data entry? Why was there no exception reporting? This kind of problem can be a lot harder to fix than a report.
Changing the report
Business needs change over time and it can be difficult to keep track of who needs what information and why. More than once, I’ve changed the criteria of a report, based on a complaint from the user who ostensibly owned the report, only to find that there was someone else who got the report via a forwarded email, used it for unexpected purposes, and was now miffed that the report had changed. I still haven’t worked out the perfect solution to that particular problem. Depending on your particular organisation, it may be practical to list all the official reports and the interested parties, so that all can be consulted. In my own personal circumstances, I’ve inherited an application with hundred of reports, not all of which are used, or even known about. I’ve promised myself that one day I will get time to audit them.
It’s worth noting that you should keep track of changes made to reports – what, why and when – so that when your boss starts comparing year on year by looking at old reports, you can explain why it doesn’t look as expected. That’s not the only reason, of course, but one that I personally find useful. It also helps for those times when none of the users of the report can agree on how it should work and you find yourself endlessly tweaking. And that will happen, despite endless meeting and agreement of specifications.
Incorrect implementation
When I first started creating reports from databases, about 10 years ago, I had no real idea what I was doing and my reports were very simplistic. As I’ve learned, and my grasp of SQL has improved, I can produce more and more complex reports. But the fundamental issue remains the same – what does the user actually want from this report. It is important to get a good grasp of the terms the user is using – what exactly do they mean by ‘new customer’, where is this data found, which out of the many ‘move out date’s is the one they actually want to base the report on. Most implementation problems are due to a misunderstanding of the requirements, so make sure that everyone is clear about definitions.
Trust your developer
As a developer, of course I’m on the side of the report, it is blindly following instructions. But I’m also on the side of the user, as I understand they need information in a timely and appropriate manner. The only real way I’ve found of making everyone happy is to stop doing what people ask for. Yes, you heard. I don’t wish to sound arrogant here but users are seldom best placed for making decisions about technical questions, or know the best way to do something. After years of re-doing reports until everyone is happy, I started just asking what they intend using the report for. It might be that instead of having Mary print out this report weekly and fax it to each member of the team, we can arrange for a personalised report to be automatically emailed out to each person by the database server. You can only make that kind of suggestion once you know the intention and use of the report.
