How to be an Effective Report Writer

Fri Apr 13, 2007 by Jeff Smith in techniques, report-writing

Learn the Database Schema

Before you can write any reports off of any system, you must be familiar with the schema you are working with. Print out the data dictionary, schema diagrams, documentation -- anything you can find that will help you work with the database. While we all strive to design and work with perfect databases, the reality is that all of them have quirks and odd design features to be aware of. The more aware you are of the database you are working with, the better off you will be. Don't simply rely on "query designer" screens that show all tables and relations; be sure that you have a good understanding above and beyond this.

Become Familiar with the Data

The best tool that any analyst or report writer can have installed on their desktop is ... drum roll please ...Microsoft Access. That's right! There is NO better tool at linking to pretty much ANY data source on the fly and letting you quickly query and cache locally and examine and work with the data than MS Access. It is a tool that I use every day to assist me when it comes to analyzing a system I am not familiar with. Even though I ultimately only use it for "helper" tasks these days, it is a great helper to have. Use a tool such as this to browse your data and really get familiar with it.   It may help to print out small "look up" tables to use as a reference, and to add notes regarding conventions used or what certain values in certain columns indicate.

Often, when a database is not custom designed (i.e., an Accounting system) certain columns might be used in ways other than they were initially intended. For example, a "DiscountType" column might be used to indicate something else, if the customer doesn't use discounts at all and there was no other place to indicate this information.

Embrace the Business

While the physical schema and data are important to be familiar with, it is equally important to understand the business behind the data.   This is often the hardest part for any technical person who is suddenly thrust into an Accounting department and must suddenly understand how a balance sheet works, but it is very important to make the effort to understand the business you are supporting. I personally found it very interesting and refreshing to sit in on meetings and to get to know other aspects of my company -- Accounting, Finance, HR, Acquisitions, etc -- which really increases your value in and your understanding of your environment. Many other "IT folks" seem to hate doing this, it all depends on your goals and your perspective. I do guarantee that if you know 50% more than me technically, but I know 200% more than you about the business, not only will I outperform you in just about every task we work on, but the company will be happier with my work and before long I'll be your boss.

Think of it as an opportunity: You have a chance to get knowledge and free training in a field other than the one you were educated for! Fresh out of college, I would have loved to be a full-time programmer in an IT department or company, but instead I was a reporting analyst in the Finance department of financial consulting company. And I never would have obtained the business and database knowledge I have today if it wasn't for that experience. Being in that role forced me to focus on the business aspects of the company and to think of everything from a database/reporting perspective (I rarely got to write any code other than SQL and formulas on reports) and it was the perfect way to transform a procedural programmer into a data-oriented set-based thinker. When I finally did transition back to a full-time developer, I was much better off for the experience, and when I interact with the business users in my company it is much easier to me to appreciate and try to understand the perspective they are coming from with their requests. And, now I have a decent background in Accounting, Finance, Human Resources and more, which has served me many times since.

Clarify the Specifications and Requirements

When given a reporting assignment, like any other programming task, the most important part happens before you write a single line of code: You MUST ensure that the requirements are complete and make sense from both a technical and business perspective.   Common problems with reporting requests and specifications are:

* The Specification is "hard-coded"; it is too specific when it should be more generic.

For example, you might get a request for a report that shows everyone in the HR department. Then, next month, you get a request for a report that shows everyone in the IT department. What you actually needed to help the requester realize is that they want a single report with a Department parameter.   And how did you handle active versus inactive employees? Was it even specified? Maybe that should be a parameter as well. Always try to take specific information for a single report and generalize it, if you can, and anticipate future requests so that it can be made into a more flexible report that will meet the needs going forward.

I have many requests go like this:

customer: "Please show me a report that shows account 3501,3506,3510,3522,3530,3560 and 3570."
me: "Sure. Do those accounts have anything in common? A common attribute or significance?"
customer: "Yes, they're ones I want to see on the report."
me: "OK, but why do you want to see those accounts?"
customer: "Because all of the accounts in that range tell me <something important>."
me: "A range? Oh! Is that what you want? Should the report show all accounts in the range 3500-3599?"
customer: "Yes! Isn't that what I asked for? Are you simple, son?"

It can be tough to deal with this, and to understand what the heck they are thinking, but it's your job to interpret these requests and to transform them into clean, clear criteria that is easily maintainable and avoids being too specific or hard-coded. In fact, maybe the report in the example above ultimately ends up being one that simply prompts for Starting/Ending account number!

* The specification is incomplete or not specific enough

A simple statement like "show me all customers and total sales last year versus this year, with a percentage increase" is too generic and open-ended. Show all customers? Or just those that had sales this year, or last year, or both? Should it be sorted or grouped into regions or territories? how about subtotals? Will any parameters be required so that you can pick certain situations?

Some of these issues pop up from a technical standpoint as you are trying to construct your WHERE clauses, and others come up from a business perspective when you present your report but it is useless because following the specs exactly just resulted in a 500 page, unsorted customer list. It is vital to take any specification for a report and to interpret and restate the specifications from both the technical and business perspective to ensure that you don't end up redesining and rewriting things over and over.

* The specification is illogical -- it just won't work!

These can be tough to spot, and even tougher to discuss with your CFO without getting fired! But often, what the user wants simply does not make any sense, and perhaps they don't realize it. This often is the result of the trained "logic-based" mindset of a programmer versus a different way of thinking that a business user might employ. The key is to identify these illogical request and to bring up the issue tactfully so that you don't insult the intelligence of the person requesting the report. An example of this might be a report that shows "only employees hired in 2003 who have 10 years of experience or more" or something similar -- sounds silly and obvious, but it happens all the time. Try to catch these errors and explain with examples why they will not work. It goes without saying that sometimes these errors will not be obvious to you as well; a common way to catch them is when you have verified your requirement and criteria over and over but the report still returns 0 rows!

* The Customer needs to refine their specifications

Remember that, as I mentioned, typically you are the "logic guy" who can clearly and efficient write down a specification or an algorithm, while your customer might not think that same way. They may need assistance from you in helping them understand exactly what they need; often they have an idea of what they want, but they don't know how to "design" a report that will give them that. So, if they try to present you only with a general idea but nothing else, you may not be delivering to them what they are really after.

I often ask the customer: "What question is this report answering?" I really try to get them to create, in a single sentence, an actual question that the report will be an answer to. Once they do that, often you realize that instead of showing 500 pages of customers, maybe all they really need is a simple summary page showing totals.

Manage Expectations

Customers don't always understand that a dynamic, data driven report is not the same as hand-creating one in Excel. You cannot always ensure that things all fit on one page, or that data will never wrap to two lines, or that totals will be the number they are looking for, since the data is always changing. It is also very difficult sometimes to explain that without a value in the data itself somewhere to flag things, you cannot randomly highlight or change the way different parts of data are presented, since there is not a human cutting and pasting or typing in this report and "eyeballing" it.

I spent lots of my time trying to explain the concept of a template-based report and why lots of this is not possible; I have yet to come up with an easy analogy to help a customer understand this (any suggestions?). Usually, I just try to give an example of page that looks perfect to them, and then I ask: "what happens next month, when two more customers are added? Do you see that they can no longer physically fit on this one page?". It can be challenging, but if you can demonstrate to them that it is cleaner to add a page break between groupings to make things organized in a more logical manner (as opposed to stuffing it all on one page), they might see the benefit of that approach and you both win.

Often, the problem is not the data, the specs or the criteria, but real estate. There is only so much room on that piece of paper (or web page) that you can deal with! I once spoke with someone for over an hour trying to explain how a report with 30 columns could not fit all on one standard 8.5x11" page without using a tiny font (not acceptable) or splitting the columns up (nope). Yet, it had to be done! This comes up often, and it can really be a challenge sometimes to show people that there are no technical limitations you are hitting here, but physical limitations of the real world that, unfortunately, you do not have the super powers to change. (Of course, this will not stop them from suggesting that maybe if we bought new reporting software we could get it to work!)

Take Pride in your Design

A report is not just information; it is a visual presentation of that information as well. I cringe sometimes when I see report thrown together quickly with text being cut off, inconsistent formatting, fields that are not aligned, random fonts that have no consistency, and so on. While the data itself is crucial and that indeed is the point of the report, presenting that data in a clean, consistent and efficient manner is very important as well. A few extra minutes of checking your layout can make a huge difference in how your reports are received by the customer, and it also shows that you have the discipline and attention to detail to do a job well. You don't have to be an artist or be especially creative to design an attractive report, you just need to make the effort to do it.

Validate your reports

For some reason, I always instinctively did this before handing any report to anyone. And, for some reason, I was always commended on this. I never understood why ... shouldn't you validate that something works and is accurate before submitting it? Then I learned that many report writers don't do this -- they expect the business user to go through and validate the data, check totals and calculations, and so on. Well, indeed they should -- but so should you. It is often easier and quicker for someone who has access to the raw tables to quickly verify that the report totals equal a quick "SELECT SUM(..) FROM ..." query, or to do some simple checks to validate some of the formulas.

Perhaps this opinion comes from my programming background and not all report writers have that, but: don't you debug your programs and test them before sending them to QA? Why not do the same for your reports? Again, your customer will be happier and so will you, since ultimately the reports will be done quicker if you can find many of these errors yourself.

Another validation technique is to ask for existing reports that show the same basic data to verify things. Again, this is often a customer task, but you can save a lot of time and really make that customer happy if you can catch some of the errors before handing the reports over to them.

Summary

I hope you find some of these tips useful, and if you have any others to suggest, please let me know. I am staying away from technical tips in today's post, but I hope to follow up with more from that perspective soon.

Report writing is a lot of fun and can be very rewarding; it is a nice change of pace from programming, and the best part is there is a distinct end when a report is finished, which you don't really have when writing an application. You also get to be creative with the formatting and how you present the data, and you really get to learn a lot about databases in general when you focus on getting data out in an accurate and efficient manner (since, ultimately, that's the entire point of the database, right?).

Legacy Comments


Coolerbob 2007-04-20	re: How to be an Effective Report Writer What utter drivel! I got to the Access comment and gave up reading the rest! I'm sure you make some good points but that one is totally wrong. Have you used SSRS - properly? I use to think it was rubbish, but it aint. I'm met some "Old School" developers that have made the Access claim years ago. I was even uncomfortable with it then too... You wont catch me using Access. Bad practice in most cases.

Jeff 2007-04-20	re: How to be an Effective Report Writer coolerbob - Oh well, your loss. If you don't understand the difference between quickly browsing, caching and analyzing data with a tool such as Access versus writing a report with reporting services, then that's your loss and I feel for you. There is truly nothing sadder than ignorance or people so insecure that they are afraid to "be caught" using "toys" like Access since the "other programmers will make fun of them!" .... Best of luck to you and thanks for you comments.

coolerbob 2007-04-24	re: How to be an Effective Report Writer I can "quickly browse"/cache, etc... using SQL in Query Analyzer. I have done this for 10 years. Works just fine for me. Access offers most real developers very little. For one thing, the syntax is different. But it's a great tool to give to some power users who can do add-hoc querying - without bothering me to write a query for them.

Jeff 2007-04-24	re: How to be an Effective Report Writer You can cache data offline with Query Analyzer? Wow, I am impressed. Can you share how you do this? I've love to learn! As for "real developers" -- who is talking about "real developers"? Who is talking about writing SQL using Access at all? Do you have any idea what I wrote at all or what I am talking about? It doesn't seem like it. I'll tell you what: You can be a hard-core "real programmer" and use Query Analyzer for ad-hoc off line browsing, filtering and reporting; I'll be a "noobie" and simply use tools designed to do those tasks. Thanks for the insightful feedback.

Coolerbob 2007-04-27	re: How to be an Effective Report Writer You can cache data offline with Query Analyzer? Wow, I am impressed. Can you share how you do this? I've love to learn! Answer: BCP (unless I'm not understanding what you are saying.) As for "real developers" -- who is talking about "real developers"? Who is talking about writing SQL using Access at all? Do you have any idea what I wrote at all or what I am talking about? It doesn't seem like it. Answer: To understand data, you have to browse it, sample it. There are varying degrees of doing this. But Access still has to use a SQL SELECT statement to go get the data. Why not do that in Query Analyzer? You can look at any kind of data source you want. There's nothing Access can look at that something like OPENDATASOURCE or OPENROWSET can't accomplish through OLEDB. Not trying to annoy you. Just explaining my experience.

Jeff 2007-04-27	re: How to be an Effective Report Writer Bob -- you are still not getting it, and probably never will ... You don't seem to understand that Access is a windows application with a user interface that facilities quick sorting, filtering, and caching of large sets of data,and that I can quickly cache tables from SQL to Access locally, open the table, quickly browse it, right click and choose "filter for: xxx" and type a code, quickly re-sort, exclude certain values, through together simple reports that I can print out with headers and footer and subtotals all in a matter of a few minutes, and on and on, all quickly within the Access UI? Or you even aware of those features? (my guess: no) And do you realize that you cannot do ANY of that within Query Analyzer -- it is simply NOT designed or optimized for that! It is a query analyzer, not a DATA analyzer!! That is a huge difference, and you seem to be thinking for some reason that I recommending writing applications or SQL in Access, as opposed to query analyzer, when I never even came close to hinting at that! Maybe reading the "drivel" that I wrote might be helpful before judging it ??? If your "experience" dictates that "Access is a toy, use Query Analyzer to analyze DATA" then unfortunately somewhere along the line you really got lots of bad experience. And if experience has taught you to read a few words in an article, and immediately stop reading and slam the article as "drivel" just because it mentions a use for MS Access that you don't even understand, then you REALLY aren't getting any benefit at all from your experience and it might be best to re-evaluate where you are and what you've learned.

john 2007-06-20	re: How to be an Effective Report Writer Good day, nice one! maybe you could expand more or talk on the qualities of a good report writer...thank you

nwachukwu maryqueen 2009-01-09	Opening of a canteen I want to write a report on a visit made for opening a canteen

Kunal 2009-04-15	re: How to be an Effective Report Writer Guys, can we talk about someting about the report writing...I think the purpose has not been met.

Kingsley Tagbo 2009-07-16	re: How to be an Effective Report Writer It looks like a lot of people on this thread are missing the excellent tips provided in this article. It is a really good summary or high-level overview of what report writers do.