How to maintain cohesion, loose coupling, and SRP when building a reporting tool?

Question

I'm building a reporting tool with three core layers:

1. Extract data from a database
2. Transform this data to compute business metrics
3. Display these metrics in a report (a heavily formatted Excel export in this case)

My challenge is that there seems to be some irreducible complexity in the task, and I'm finding it very challenging to avoid spaghetti code. In particular, I want to avoid tight coupling between my data engineering and my UI work.

To give a high-level example:

• Calculate metric "change in sales" as (Sales Today - Sales Yesterday)
• Format the output as a currency, i.e. f'${x:,.2f}'
• Set the font colour to green if > 10
• Set the font colour to red if < -10

And to illustrate my challenges, suppose the business says "we've changed our mind":

• Calculate metric "change in sales" as (Sales Today / Sales Yesterday - 1)

• Format the output as a percentage change, i.e. f'{x:.0%}'

• Set the font colour to 'green' if > 0.10

• Set the font colour to 'red' if < -0.10

• Set the background to 'blue' if Sales Yesterday = 0, and display the most recent "change in sales" that could be calculated for the store and show the date of this on hover.

I'm not sure how to leverage good software engineering to make these near-daily changes "easy". I either bundle calculation & business logic in one big "metric" object (tight coupling of calculation and display) or I commit myself to modify a formatting pipeline each time the calculation changes, and the data pipeline each time a display requirement needs data extrinsic to the final value (e.g. date last calculated in the example above).

Any ideas on how to address this?

Answer 1

First what reporting encorporates

In order to not typically forgetting a "minor" detail costing much, the entire process.

Here one can learn from failures of the past: report generators. Some report generators work(ed) immediately on the database with a terrible tie in, and needing the know-how at developer level.

• There is the database on which to do queries.
• There is the Excel reports scheme one wants to fill.
• On makes a report model in code: the central meeting point.

Ideally these three parts have one major version common, but may be developed independently. So it would be nice to have as meta information in the Excel some 3 partite version info: 13abc.

One should expect documentation consisting of these three separate parts.

• One can create dummy data to fill in various report models, usable in a unit test.
• One can generate a simplified, but fully connotated export to compare with the later Excel reports.
• One can start developing generating the Excel export.
• One can start developing database queries, partly with a database tool. This may lead to adding indexes, or writing a calculation module.
• The integration might even require a batch run in the night or such thing.

Your case

1. You need test data, possibly taken from the database (queries).
2. Then you make a report model on those data, resulting in a view of what you want reported.
3. It might be necessary to make a mock-up, but hopefully the product owner (business) is aware of 2 and maybe even 1.

The complexity (IMHO) arises when the report is not a straight interpretation of the data available, is strongly parametrized (unclear responsibilities), the code does too much juggling with the data retrieval.

You might consider compartmentalizing the report, if there are separate parts, tables, a graph, whatever. Development documentation (queries used, data gathering & processing) is important. Besides compartments (report parts), there are partial results, you might somehow want to display during development.

Simply ensure that the entire development is transparent, like what the database queries do.

Answer 2

You already got a good answer from Joop Eggen, so let me focus on a different aspect here.

When you get a lot of these daily change requests from business people, it might be worth to analyse which of the changes can be made configurable, so the requestors can design several details of their reports by themselves, without asking the developers for each and every minor change in color or units.

A cost-effective solution might be utilizing Excel (or whatever spreadsheet program your business people prefer) for this:

• provide an Excel template file which contains one or more data sheets, and a report sheet

• the data sheets might contain placeholders for certain values, or named columns which are filled from the database

• the report sheet uses formulas and conditional formatting to pull in the desired values from the data sheets

When a report shall be generated, your program just fills the data sheets from the database. It might fill the named columns, and replace the placeholders according to what it finds in the template (it should also check that the template sheet has a valid structure and give a clear error message to the users in case something is wrong).

The template file, and specifically the report sheet, stays in the responsibility of your business people. That way, whenever they change their mind about the "look" of the reports, they can apply the necessary changes by themselves.

I have seen lots of such solutions in many different flavors working well over the last decades.

Of course, this will only work in an environment where business people are trained how to use Excel. There are also lots of other technical ways to achieve flexible configurations by the users themselves, from domain-specific languages up to graphical configuration editors. At the end of the day, you will have to find the right balance between development of individual requirements, or developing a highly configurable solution, between ease-of-use for the users and their willingness for learning some configuration mechanism. Good luck!