Automated report generation workflow: how to build one
The build: pulling from several sources, handling systems without an API, scheduling and time zones, output formats, and monitoring a reporting pipeline once it runs unattended.
Rithul Palazhi10 min read
On this page
A reporting workflow has five stages, and four of them are where it breaks. Pulling data, reconciling sources that disagree, assembling the output, delivering it, and knowing when any of that failed.
This page covers the build. It assumes you have already decided the report is worth having, which is a separate and more important question covered in which reports are worth automating.
What we'll cover
The five stages
Extract. Get the data out of each source system, for the period the report covers.
Normalize. Make the sources comparable: matching identifiers, consistent currencies, aligned date boundaries.
Compute. Apply the aggregations, comparisons, and thresholds the report is built on.
Render. Produce the output in whatever format the audience needs.
Deliver. Send it where it is read, and record that this happened.
Most build effort goes into computing, which is the stage least likely to cause trouble later because it is deterministic and testable. Extraction and delivery are where production failures live, because both depend on systems outside your control.
Pulling from several sources
Single-source reports are straightforward. The difficulty scales with the number of systems, and not linearly.
Pull in parallel, with independent failure. Four sequential calls take the sum of their times and fail entirely when the third one does. Parallel extraction with per-source error handling means a slow CRM does not delay everything, and one unavailable system produces a report noting the gap rather than no report at all.
Decide what a partial report means. This is a decision, not an implementation detail. For an exception report, missing one source means missing exceptions, which may be worse than no report because it looks complete. For a summary, a clearly flagged gap is usually better than silence. Make the choice deliberately and label the output accordingly.
Cache what does not change. Reference data such as product catalogs, team structures, and account lists rarely changes between runs. Fetching it every time adds failure modes for no benefit.
Respect pagination properly. The most common quiet bug in reporting is a report built on the first page of results. It works during development against a small dataset and silently under-reports in production forever. Always exhaust pagination, and log the record count so a sudden drop is visible.
Pull raw and transform afterwards. Fetching everything for the period and filtering in your own code is easier to debug than constructing elaborate queries per source, and it lets you re-run the transformation without hitting the source systems again.
Systems with no usable API
Every reporting project meets at least one. The options, in descending order of preference:
A scheduled export. Many systems without an API can email a CSV on a schedule, or drop one on SFTP. An automation that watches the inbox or the folder and picks up the file is straightforward and reliable, and it is by far the most common solution in practice.
A database replica. Where the system runs on a database you can reach, querying a read replica sidesteps the missing API entirely. Check the licensing position first.
A reporting or webhook feature. Systems without a general API frequently have a specific reporting endpoint or webhook. It is worth reading the documentation properly rather than assuming.
Screen scraping. Last resort. It breaks whenever the interface changes, which is often, and it usually contravenes the terms of service. If you must, isolate it so its failure does not take the rest of the report with it.
A person. Genuinely the right answer sometimes. One manual upload a month, into a known location, with the rest automated around it, beats a fragile scraper. Design the workflow to wait for the file and to say something when it has not arrived.
When sources disagree
Two systems reporting different numbers for the same thing is the normal case rather than the exception, and a report that quietly picks one is worse than one that flags the difference.
Identifiers rarely match. The CRM's customer ID, the billing system's account number, and the support tool's organization ID are three different things referring to one company. Maintaining an explicit mapping is unglamorous and it is the foundation everything else rests on.
Date boundaries differ. One system's "last week" runs Monday to Sunday, another's Sunday to Saturday, and a third reports in UTC while your business runs in local time. Define the period explicitly in your own code and impose it on every source, rather than asking each system for its own idea of last week.
Currency and units drift. Amounts stored in different currencies, or in minor units in one system and major in another, produce errors that are large and invisible. Normalize on extraction, and record which rate was used.
Deletions and amendments. A record cancelled after the last report changes historical figures. Decide whether your reports are snapshots or restatements, and say which on the report itself, or somebody will eventually ask why two copies of the same month disagree.
Build the reconciliation in. Where two sources should agree, have the workflow compare them and report the variance. This turns a hidden data quality problem into a visible line item, and it is frequently the most valuable output of the whole exercise.
Scheduling, and the time zone problem
Schedule from the deadline backwards. Work out when it must be read, subtract the run time, subtract a margin for a retry, and schedule there. Scheduling for a convenient hour and hoping is what produces reports arriving after the meeting.
Be explicit about time zones. A report scheduled at nine runs at nine in whichever zone the platform uses, which may not be yours, and daylight saving will shift it twice a year. State the zone deliberately and check what happens at the transitions.
Handle the boundaries. Month-end reports that run on the first should know which month they cover. Reports skipping weekends need to say whether Monday covers three days. These rules are easy to state and easy to omit, and their absence shows up as numbers nobody can reconcile.
Wait for upstream, rather than guessing. If your data depends on an overnight job, trigger on that job completing rather than scheduling an hour later and hoping. Guessed delays fail on the day the upstream job runs long, which is the day the numbers matter most.
Make re-runs safe. Someone will need to re-run last Tuesday's report. If that is straightforward and produces identical output, you have saved yourself an afternoon at some point in the future.
Output formats
Chat message for anything a team acts on together. Short, visible, discussable in place, and the format most likely to be read.
Email for individual and external recipients, with the summary in the body rather than only in an attachment. An attachment-only email is opened noticeably less often.
Spreadsheet where the recipient will filter and pivot. Writing to a shared sheet that updates in place preserves any charts built on top, which people appreciate more than a new file each time.
PDF where the output is formal, external, or needs to look the same for everyone.
Dashboard where the question is continuous rather than periodic. Worth noting the distinction: a dashboard answers "how are things now", a report answers "what changed and what should I do". Building a dashboard when people wanted a report produces something nobody visits.
Most reports want a short message with the headline and actions, plus a link to detail. Choosing one heavy format usually satisfies fewer people than that combination.
Describing all of this is more productive than specifying it. On CodeWords you describe the report in plain language: which systems, which period, what to compute, what format, who receives it, and what should happen when a source is unavailable. Cody, the automation builder, builds it, connects it to your systems, and deploys it. Automations connect to more than 3,000 integrations. The free plan covers light use, with Pro at $39 per month and Business at $100 per month as usage grows; details are on the pricing page.
Monitoring a pipeline nobody watches
Reporting workflows fail quietly, because their output is a thing arriving rather than a person waiting. Nobody notices absence for a surprisingly long time.
Report success, not only failure. A brief confirmation that the run completed, with the record count, makes silence meaningful. Without it, no report and a broken report look identical.
Alert on absence. A check that fires when an expected report has not been produced catches the failure mode where the whole workflow never started, which error handling inside the workflow cannot catch by definition.
Log the counts. Records fetched per source, per run. A source that returned four hundred rows every week and today returned nine has a problem, and the count is what makes that visible before the numbers do.
Sanity check the output. Totals within a plausible range, no unexpected nulls, period boundaries as intended. A report that fails its own checks should say so rather than send.
Keep the raw extracts briefly. When somebody queries a figure three weeks later, having the data as pulled turns an investigation into a lookup.
Frequently asked questions
How do I handle a source that is sometimes unavailable?
Retry with backoff, then decide deliberately between sending a flagged partial report and sending nothing. Either is defensible; sending an incomplete report that looks complete is not.
Should I build this as code or on a platform?
Code gives total control and you maintain the scheduling, retries, credentials, and monitoring yourself. A platform provides those and you describe the logic. For a business report as opposed to a product feature, the platform is usually the better use of the time.
How do I test a report before people rely on it?
Run it against a past period where you know the answer and compare. Then run it alongside the manual version for two cycles. Discrepancies in that window are almost always genuine findings about the data rather than bugs.
What about reports containing sensitive data?
Decide where it may be delivered before building, since that constrains format and channel. Keep sensitive fields in the systems that already hold them where you can, and have the report reference rather than reproduce them.
How long should a reporting workflow take to run?
Long enough not to matter, which means finishing well before the deadline with room for a retry. If a report takes forty minutes and is needed at nine, starting at half past eight is not a schedule.
Can one workflow produce several versions for different audiences?
Yes, and it is usually the right structure: compute once, then render and deliver per audience. It avoids the situation where two reports on the same subject disagree because they were built separately.
What is the most common cause of a reporting workflow breaking?
A source system changing its export format or API response without notice. Validating the shape of what you receive, rather than assuming it, converts this from a silently wrong report into a clear failure message naming the source that changed.
Should the workflow write to a database as well as sending the report?
Often worth it. Keeping the computed figures gives you history independent of the source systems, makes trend reporting straightforward, and means a question about last quarter does not require re-extracting data the sources may have since amended.
How do I handle a report that needs data not yet available?
Decide between waiting and proceeding, and make it explicit. A workflow that waits should have a deadline after which it gives up and says so. A workflow that proceeds should mark the output clearly as provisional, and ideally re-run and reissue once the data lands.