A Recipe for Reports (or "Spaghetti Code")
authored by Decisioncell, Inc.

Summary:

This article discusses the importance of a process-driven approach to developing reports. A methodology is proposed for analyzing reporting requirements, designing the report, and implementing the solution. This methodology is not linked to any specific reporting tool, but rather the approach discussed here applies to any brand of report writing application, since the emphasis of this article is on the process of writing reports that meet the standards of accuracy, efficiency, ease of use, long-term customer satisfaction, and minimal rework.

Think of your favorite meal you like to cook and eat at home. Chances are the recipe you follow is not documented, or if it is, you no longer need to consult the written instructions, as you have walked through the preparation steps so often, the meal seems to come together without much thought and effort.

Often writing reports can be like cooking your favorite meal: the report is so simple to produce because it involves an area of the business with which you are very familiar. Also, the report may contain very little processing logic or is nothing more than a quick “data grab” meant to answer some ad hoc question of the moment. These kinds of reports are effortless to create, and can be like making ready-cook spaghetti. Boil water, add pasta, heat sauce. Simple.

Yet what happens when the report is more complex, requiring difficult processing logic and perhaps involving an unfamiliar area of the business? Here more than ever a clear, process-driven approach to developing reports can greatly improve the quality and timeliness of your work.

While writing a report may not follow the steps below in exact sequence and some of the steps may be performed concurrently, like a good meal a good report is produced not by accident, but by a deliberate process.

Step 1: Identify Stakeholders and Their Business Requirements

Before you start thinking about what to make for dinner, you need to know who’s coming. This dinner may be a routine family meal, or perhaps guests are attending. Whatever the case, you should know how many people you’re serving. After you’ve identified the meal participants, you quickly determine their food preferences. In other words, you make sure everyone wants spaghetti.

Like the meal preparation example, identifying the report users and their needs is an important first step. In addition to report users, there may be others within the organization who have a stake in the proposed report. For this reason, these people are referred to as stakeholders.

In general, report stakeholders can be identified by answering the following questions. Some stakeholders may fit into more than one group:

• Who is sponsoring the report? This is the person(s) who not only understands the value of the proposed report, but also has the authority within the company to allocate the resources necessary to produce the report. This person(s) is sometimes called the Executive Sponsor with projects that tend to be larger. While s/he may realize the need for and purpose of the proposed report, this stakeholder may never actually use the report once it’s created.
• Who is the Champion or Star? This type of stakeholder has high-level knowledge about and authority over the report. S/he may be a regular user of the report, but more important, this person will be able to approve changes to the report design and sign off on the report as being satisfactorily completed.
• Who are the direct users of the report? This question speaks for itself.
• Who are the Advisors or Sages? These people may not ever see or use the report, but they know business or technical details relevant to its development.

Accompanying stakeholder identification in Step 1 of the report development process is business requirements gathering. Except perhaps the business or technical advisors, the stakeholders will all have needs with respect to the proposed report. This report will be required to solve a business problem, answer a business question, or take advantage of a business opportunity. A good reports developer has a clear understanding of this question, opportunity, or problem. This understanding leads to a clear statement (either written or verbalized) of the report requirements. “We need a report to show gross margin on our sales” is an example of a business requirement, admittedly a vague requirement, but at least a start. From this vague generalization, the report developer works with stakeholders to fine-tune their needs. “We need this gross margin report to summarize total sales and cost of sales by product line.” Now, the requirement is starting to take shape. Working with the stakeholders to identify what they expect the report to accomplish will develop this requirement even further. Completing steps 2 and 3 below will also provide further insight into the business requirements.

Step 2: Understand Report Logistics

To return to our spaghetti dinner example for a moment, after you have determined who’s eating and what they like, you should think about the logistical issues: When will dinner be served? Who will sit where? Is dinner self-service, or should you plate the food for your guests?

Stop thinking about food! We’ve still got a report to write. The main questions at this point are as follows:

• How frequently is the report needed?
• Who will need to generate this report? (perhaps it's automated)
• What are the security issues with this report or overall system in which it operates?
• How is the report distributed?
• To which locations?

Step 3: Identify Report Contents and Layout

Consider the details of the meal a little more closely. Will bread be served? How about a salad? What should you put in the salad? How hungry will everyone be? Should you make enough spaghetti for everyone to have seconds?

While not as tasty, and probably not suitable for ingestion, the contents of a report can be thought of as the information it presents. The user will rely on this report information to make decisions that affect the company. Thus, not only should you be concerned with identifying the actual data elements on the report, but also a suitable format for these data elements and the report’s overall ease of use are important.

A report prototype is an excellent means for developer and stakeholders to come to agreement about the contents and appearance of the finished product. The following graphic illustrates a report prototype. Notice that this report prototype is merely a piece of paper containing some hand-written information meant to represent the report face. This prototype should also assist the report developer with determining grouping, filtering, subtotaling, and page break information.

Hand-written report prototype

Step 4: Analyze Data Requirements

Before any meal is prepared, you must identify the ingredients. If your supper plans are a little more challenging than a ready-cook spaghetti dinner, you’ve got to think carefully not only about the ingredients, but also about how these ingredients are assembled. After you locate the tomatoes, oregano, garlic, thyme, and basil for homemade sauce, you need to determine how to mix these items for the best results.

Just as the meal you prepare is a compilation of ingredients, a report is a compilation of data. Up to this point, you the developer have been concerned primarily with gathering business requirements. You have purposefully avoided delving into the nitpicky technical details behind the report, mainly to prevent distracting stakeholders and yourself from the important task of identifying the business need.

However, now it’s time to roll up your sleeves and dig in to the technical details. If Steps 1 through 3 focused mainly on the business requirements and some cosmetic and logistics issues, this step zeroes in on the data requirements of the report, arguably the most important area of reports development. This type of work may require an investigative approach, using some type of data querying language or tool to examine the source data in the physical database for information about data structures, volume, and relationships. Due to the technical nature of this step, the report stakeholders may have only the faintest idea about the source and structure of the report data, sprinkling their comments with nonspecific references to “files.” These individuals may be familiar with the computer application that allows them to enter the source data or view it on screen, but where this information goes may be a mystery to them. Nevertheless, the data requirements impact report accuracy, efficiency, and usefulness, so they must be determined.

Analyzing the data requirements involves these activities:

• A complete tracing of contents on the report prototype (identified in Step 3) to their source
• Relationship identification between all database tables that will provide data to the report
• Careful consideration of data volume, which could cause processing bottlenecks

The following illustration is a conceptual representation of tracing the contents on the report prototype (from Step 3) to their source. Some of the report contents will come directly from fields of various tables located in a company database. However, some of the report contents may not be tied to an actual physical storage location, but instead will be calculated by the reporting tool itself.

Tracing report contents to their source

Relationship identification between the database tables containing data needed on the report is also an important activity. The following illustration is an Entity Relationship Diagram. While designing good reports does not necessarily require formal diagrams such as this, the report developer will have to acquire this level of understanding about how the report data logically relates between tables.

Entity Relationship Diagram

Step 5: Identify and Approve Constraints with Stakeholders

It’s only natural that the more involved in the process one becomes, the more details emerge. Sometimes these details have little impact on the overall outcome. Yet often this new information introduces new limitations or constraints. If we turn back to dinner for a moment, during the process of preparing the meal you may discover that you are out of your child’s favorite salad ingredient, cucumbers. Is this important? Will it mean your child will want to skip the salad altogether now? Better clear it with junior.

In regard to writing reports, let’s say stakeholders expected the report to have concurrency with the production database at all times throughout the business day, but based on the analysis of all factors, you realize the best solution is a datamart that can be refreshed only a couple of times a day; thus, report concurrency is constrained. Is this acceptable to the stakeholders? It’s certainly better to approve this constraint with them before you begin implementing the solution. Or perhaps the sheer complexity of processing logic or volume of data contained in the report will mean that it requires several minutes to run. This may seem like a short time, but what if your stakeholders were expecting the report to generate during a customer service phone call, when more than a few seconds can seem like an eternity? There are too many constraints to mention. The point is, as you the developer are aware of significant constraints, this information should be clearly communicated to stakeholders for the sake of creating a solution that will not disappoint them. Constraints that are implemented but prove unacceptable to stakeholders usually mean the report is an expensive failure.

Step 6: Implement the Reporting Solution

Your meal is ready to be prepared using the tools you have in your kitchen. Now and only now will the preparation process involve pots, pans, utensils, and cooking devices.

Similarly, technology has been secondary to analyzing the reporting need and the data requirements up to this point. Perhaps we have worked for hours or even days on the previous steps, not once turning our full attention to the technology we will use to implement the solution. We have deliberately waited this long. Just as your selection of appropriate kitchen utensils has everything to do with what you are cooking, your selection of tools for creating the report depends on the kind of report you are developing. It would be silly to ask a hungry spouse or child, “What kind of pan shall I cook with?” Rather, the question they want to hear is, “What do you want for dinner?” Likewise, stakeholders usually don’t care how the report is produced; they just want the results. Of course, unlike our meal preparation analogy, a report developer will probably not have an unlimited set of tools with which to work. The cost of software licensing and compatibility issues alone will limit the kinds of technology at your disposal. Yet developing good reports means doing first things first and not always trying to force the solution to a preferred technology.

While you the developer may have relied to some extent on a specific reporting or data access tool to perform analysis in Steps 3 and 4, Step 6 is all about working with the technology that thrills the “techie” in every report developer. Programs are written, reporting views are built, SQL is crafted, and output is designed for best aesthetic effect. Depending on the logistical considerations and data requirements, report generation and distribution may also need to be automated, and various deployment technologies (internet, intranet, email, etc) may need to be utilized.

Conclusion

This article has focused on the process of reports development. We have not dealt with the issue of maintenance. Business requirements tend to change, which necessitates a change in reports. And sometimes, even the best report developers let a little bug slip through. Alas, maintenance is necessary from time to time during the life of the report.

No matter how informal the process you follow for developing reports, a system that guides the process can greatly improve the quality and timeliness of your work, particularly when faced with more complex reporting needs. After all, a report developer skilled with programming languages, reporting tools, and various other technologies fails miserably if the finished product is inaccurate, difficult to generate and use, or inconsistent with what stakeholders need.

So here’s to developing good reports. Pass the Parmigiano!

Decisioncell has created reporting solutions for companies across North America.