Methods to Manage/Document "one-off" Reports

Question

I'm a programmer that also does database stuff and I get a lot of so-called one-time report requests and recurring report requests.

I work at a company that has a SQL Server database that we integrate third-party data with and we also have some third-party vendors that we have to use their proprietary reporting system to extract data in flat file format from that we don't integrate into SQL Server for security reasons.

To generate many of these reports I have to query data from various systems, write small scripts to combine data from the separate systems, cry, pull my hair, curse the last guy's name that made the report before me, etc. My question is, what are some good methods for documenting the steps taken to generate these reports so the next poor soul that has to do them won't curse my name? As of now I just have a folder with subfolders per project with the selects and scripts that generated the last report but that seems like a "poor man's" solution. :)

Answer 1

Honestly, it sounds like you're on the right track with what you're doing.

Things that I would do to "take it to the next level":

• implement a lightweight source control system (svn, git, Hg etc...) where you keep those folders. This will also make it easier to see what's changed over time.
• have a related documents folder for the 3rd party documentation, if any.
• create a standard documentation set that you apply to the mini-projects. A README or START_HERE type document is always a good first step. That lets the poor soul who comes after you know where to start looking for information.
• have a standard document that says how to run the XYZ to generate the report.
• identify the things you wish you had been left behind and make sure the mini-projects have that aspect available.

Answer 2

You need an answer to the question, "Remember that report you ran for me last year?"

First, take care of the housekeeping and be able to track:

• Who made the request?
• When was the request made and the solution provided?
• How was it done? code, scripts, and any other documentation.

Second, formalize the request process. I know this is easier said than done. Try to at least avoid the "Drive By" request. This is where someone literally walks past your desk or stops you in the hallway/coffee machine and asks for a report. Use your boss or a fear of forgetting as an excuse, so they'll send an email.

Third, Source Control. Don't let these little one-off requests fool you. They can have even more change requests than a production application. "Can you put back that column I had you take out and exclude the Honolulu Branch for the 4th quarter only?"

There are a lot of tools out there to handle some of these things. I've always made it a habit to create a new database with each new job I get and throw as much in there as possible. Not only reporting, but the new person may want to know what is that database AccountingMonth7 used for?

Answer 3

A year or two ago I was testing a change I had made and got annoyed by having to do a calculation manually over and over. I wanted to write a graphical utility to do that calculation for me. Since I write embedded software, I didn't have any programming environments already set up for GUI programming, so for no other reason than it was readily available, I implemented it in JavaScript in a single HTML file.

When I finished the feature and submitted it for testing, the tester asked me how to do that calculation. It was harder to explain than to just give him the utility, so that's what I did, after adding a little polish. He put it on an internal web server that the testers use and I promptly forgot all about it. A couple weeks ago someone in a completely different department sent me a patch for that utility. It had spread by word of mouth and I had no idea anyone else was even using it.

I tell you this story to illustrate how putting it on a web server made it trivial to share and reuse a "one-off" script, even though that wasn't the intent in the beginning. My recommendation is to spend a little extra time making your reports able to be run from a web page, and put them on an internal web server somewhere.

Answer 4

Source Control is an obvious one. I'd suggest git, or maybe you have a VSS or TFS server somewhere on your network since you're an MS shop.

Another option is setting up a wiki on your network -- easy enough to update and can't beat the user-friendliness. This will also earn you some brownie points with the bosses for initiative :)