Monday, July 26, 2010

Report Functional Requirements - Too Often Overlooked

A template for what follows can be found on my personal website.

Reports are often an afterthought and commonly believed to be an easy thing to create. As a result they are often overlooked during the requirements gathering and planning phases of a project and when they are considered they tend to be the red-headed stepchild and don't get the attention they deserve. This is usually a time consuming and costly mistake.

Reporting can often be complex and requirements interpreted many different ways by many stakeholders (e.g. business users, business managers, developers, architects, etc.). Reporting tools have come a long way and can do many different things many different ways. The tools can actually serve as a fully functioning UI (user interface) in many cases. Not to mention, data itself is just that - data. It can be manipulated in infinite ways to provide information that not only looks different but is actually different and leads to different values and therefore conclusions.

Most, if not all of the time, report writing will be an iterative process whether your an agile shop or not. The key here is to not iterate to the point that a single report costs $20k to create (yes I have seen this) and/or that the developers, business analysts, end-users, etc. are completely frustrated and just want to say it's done so they don't have to deal with it anymore (yes I have seen this as well).

I believe there are a few key here that can help avoid the $20k meaningless report.
  1. Gather the minimum set of requirements necessary to create the fundamental pieces of a report. Use a template to collect this information. This will ensure that you have consistent information from which you can create a consistent set of reports.
  2. Require the report requester to provide mock-ups. When possible, have them create the mock-ups in Excel and use real formulas for calculated columns. The greatest value mock-ups provide is that they force the requester to think about what they are asking for. They make the requester realize things like their idea was malformed and what they're asking for doesn't make sense and/or isn't possible. It also helps them think through things they wouldn't have thought about until the first iteration of report was delivered which helps cut down in the number of iterations and the need for developers and/or BAs to asked "stupid" questions that can annoy and embarrass the requester.
Minimum Set of Requirements
It doesn't matter what tool(s) you are using. It can be Crystal Reports, Cognos Report Studio, Jasper Reports, SQL Server Reporting Services, etc. They all have the same basic functionality and therefore the same requirements needed to develop a report using them.
  1. A meaningful title for the report (e.g. Active Customers Name & Address)
  2. A concise description of the report that includes
    • The primary "thing" being reported on (e.g. customer)
    • The subject of the attributes being report (e.g. name and address)
    • The main criteria (e.g. status = active)
    • For example, "The Active Customers Name and Address report includes the names and addresses of all customers with a Status of "Active" and is grouped by State of residence."
  3. A list of the columns with
    • Database table and column name if possible
    • Label text for the report
    • Any calculation
    • If it's a link to another report or external source and if so a description of how the link should work
    • If it's a sortable column
    • Right, left, or middle alignment
    • Value formatting (MM-DD-YYYY, YYYY-MM-DD, $100, ($100.00), etc.)
  4. Layout
    • Column order
    • Landscape or portrait
    • Cross-tab or row/column
    • Headers and footers
      • Image/logo
      • Creation date
      • Page number and format (p. 1, page 1 of 10, etc.)
      • Selected parameter value
  5. Grouping
    • If grouping, what are the group levels?
    • How should each group be sorted?
    • Should the group have subtotals and if so which columns and what is the calculation if complex (e.g. weighted average)?
    • Should there be a page break and/or line before/between/after the group?
  6. Sorting
    • How should the report be sorted (which column(s), in what order, and which direction - ascending or descending)?
    • Should the user be able to specify the sort order (dynamic sorting)?
    • If grouping is present, what order should the groups be sorted?
    • Is run-time sorting allowed on a column after report execution?
  7. Filter Criteria
    • What filter criteria (WHERE clause) are there if any? For example, should only "active" customers be included. Anothe example would be "completed" orders.
    • Should these criterion be evaluated using an AND operator or an OR operator?
    • The column(s) that the filter is applied to should be clearly stated.
  8. Parameters/Filters
    • Label/text for the parameter
    • Drop-down, text, yes/no
    • Are multiple selections allowed?
    • What are the values or where do they come from (static list of values or from a table)
    • Should we assume that all criteria are applied to the result set or are the "OR" conditions?
    • Does one parameter drive the value list of another parameter (e.g. Country changes values in state/region parameter drop-down)?
    • What column or field in the result set should this parameter/filter be applied to?
  9. Schedule
    • If the report is deployed to a "report server" should it have one or more standard schedules on which it runs?
  10. Export format
    • If the report is deployed to a "report server" what is the default format (Excel, Word, CSV, PDF) that it should be written to? If there are scheduled instances, what format should those be written to?
  11. Recipients (burst or data-driven - what is the logic?)
    • If a scheduled report instance is burstable (Cognos) or a data-driven email subscription (SSRS) who are the recipients? Is it a static list or is it data-driven?
  12. Misc
    • Charts or graphs?
    • Drill-downs?
    • Links to other reports (drill-through)?
This isn't an exhaustive list but it is a good starting place to build a template from which you can more effectively develop reports. Open communication and an iterative approach is good but why waste iterations on requirements you could have known upfront? Save time, save money, and produce a quality product. Our job as developers is to deliver value. This is one way to help do that.


  1. Good Blog Dave, are you on Twitter yet? I tweeted your article

  2. Great Article. Exactly what I was looking for. Thank you so much. Do you have anything on gathering requirements for SSIS projects. - Iqbal