Custom Report Builder Software: Feasibility, Cost and the Hard Parts

What a report builder becomes once real users touch it

The demo is easy. Checkboxes for columns, a filter row, a Run button, a table. Two weeks after launch, your head of sales builds "Revenue by Rep" by picking fields from customers, orders and order lines. The report says $4.1M. The accounting ledger says $1.6M. Nothing crashed. No error appeared.

What happened is a fan-out. Joining orders to order_lines multiplied every order row by its line count, so SUM(orders.total) counted the same order once per line item. The user does not know what a join is. They know the number is wrong, and from that morning forward every number your product produces is suspect. That is the real risk here. A reporting feature that is 95 percent correct is worth less than no reporting feature, because a wrong number that looks confident travels to a board deck before anyone checks it.

The join graph is the product, not the user interface

Fan-out is one of two classic traps. The other is the chasm trap: two one to many branches off a single hub table, say a customer with both orders and support tickets, which produces a Cartesian product and inflates both counts simultaneously. Neither throws an error. Both are silently wrong.

A competent build does not let end users touch raw tables. It defines a semantic layer: entities, their primary keys, their joins declared with explicit cardinality, and measures defined once. The query compiler then either refuses an unsafe path or rewrites it. Look at how the mature tools solve it. Looker's LookML requires a primary_key on every view and uses symmetric aggregates, a hash based SUM(DISTINCT) trick, so a fanned out sum still returns the right total. Cube declares joins with relationship: one_to_many and pre-aggregates to the correct grain. dbt's MetricFlow and Malloy take similar positions. The alternative, aggregating each fact to its own grain in a subquery before joining, is more code but easier to reason about.

This is the decision that sets your budget. Modelling 12 to 20 entities properly, with cardinality, defensible measures and a compiler that respects them, is 2 to 4 weeks of work that almost no quote includes. Skip it and you have shipped a machine that generates wrong numbers on demand.

Every report builder is a remote code execution surface

Values parameterise. Identifiers do not. You cannot bind a column name, a table name, an ORDER BY target or a LIMIT through a prepared statement, so every one of those must be resolved against your model and rejected if it does not match. Any string concatenation from the request body into SQL is an injection, and a report builder is nothing but user supplied identifiers.

Multi-tenancy is worse, because the failure is invisible. The tenant predicate must be injected by the compiler, never assembled in the UI or trusted from the request. The stronger pattern is PostgreSQL row level security, an isolation feature that filters rows in the database itself: a dedicated read only role without BYPASSRLS, with SET LOCAL app.current_org inside the same transaction as the query. Then a forgotten WHERE clause returns zero rows instead of your other customer's revenue.

Then set the limits nobody sets. PostgreSQL's default statement_timeout is 0, meaning forever. One user picking "all time" with no filter on a 200 million row table pins the replica, the connection pool fills, and your entire application starts returning 500s. That is the 3am page, and it is caused by a feature working exactly as designed. A competent build carries a statement_timeout on the reporting role, a separate connection pool so report load cannot starve the app, an idle_in_transaction_session_timeout, a per tenant concurrency cap with a queue, and an EXPLAIN cost gate that rejects a query above a row estimate threshold and tells the user to add a filter.

Where the query actually runs

Across our builds, a well indexed PostgreSQL read replica comfortably serves filtered reports over roughly 20 to 50 million fact rows at a few seconds p95. Past that, or once users want unfiltered group-bys, you need a column store: ClickHouse, BigQuery, Snowflake, or DuckDB for single tenant deployments. Migrating there mid project is the single most common reason these builds double in cost.

Two details that bite. First, COUNT DISTINCT is the expensive operator, and the approximate versions built on HyperLogLog trade exactness for memory, landing within a percent or two of the true value. That is fine for a traffic dashboard and unacceptable for anything finance reads. Decide per metric, in writing, which may be approximate. Second, caching. If your cache key is the generated SQL text alone, two users with different row permissions share a cache entry and one of them sees data they are not entitled to. The permission context belongs in the key.

Time is its own project. You store timestamps in UTC, the user reports in America/New_York, and date_trunc has to happen in their zone or every daily total is shifted by hours. Then add fiscal calendars, ISO 8601 week numbering, and the argument about whether the week starts on Monday or Sunday. Budget for it.

Exports, schedules and the pager

Excel has a hard limit of 1,048,576 rows per sheet. A CSV export of 4 million rows opens truncated, with no warning the user will notice. Synchronous exports die at the load balancer's idle timeout, commonly 60 seconds, so exports have to be async jobs that stream to object storage and hand back a link. That link is usually a presigned URL, which is a bearer token: anyone holding it reads the file. A 15 minute expiry is safe and useless when the recipient opens the email three days later. Choose deliberately between short expiry and an authenticated download endpoint that re-checks permissions on every fetch.

PDF rendering means headless Chrome, roughly 200 to 400 MB resident per render, in its own container with a hard concurrency cap. Everyone's schedule fires on the hour, so 8am Monday is where it runs out of memory. Add jitter.

Scheduling is where naive builds fail quietly. Store the IANA timezone name, never a fixed offset. A 2:30am daily schedule does not exist on the spring forward day and happens twice on the fall back day. "Monthly on the 31st" has to mean something in February. And a worker that retries after a timeout will send the same report twice unless you have an idempotency key on schedule_id plus the scheduled instant, claimed before the send rather than after.

Saved reports rot, and so do permissions

A saved report is a pointer into your schema. Rename a column six months later and 400 saved reports break, or worse, keep running and return a different number. Reference stable model identifiers instead of raw column names, and add a report impact check to continuous integration so a migration that orphans saved reports fails the build.

Permissions drift the same way. A finance admin builds a report and schedules it to a regional manager. Run it as the owner and you have just emailed company-wide revenue to a regional manager. Run it as the recipient and the report silently arrives empty, which nobody reports for a quarter. The correct build runs as the recipient and tells the owner at schedule time that this recipient can see 0 of N rows. And when the owner leaves, deprovisioning through SCIM, the identity standard for user lifecycle, must transfer or disable their schedules. Otherwise a former employee's reports keep mailing themselves out forever.

What it costs and how long it takes

These are Digital Heroes delivery bands across 2,000+ projects, not market averages. A focused report builder inside an existing product, clean schema, tables and basic charts, runs $15,000 to $60,000. Built as part of a first release, with scheduling, exports and permissions, $50,000 to $130,000 in 10 to 16 weeks. A full reporting platform with pivots, embedding and per tenant theming, $150,000 to $350,000 over 6 to 12 months.

What moves this specific number: the size of the join graph, since 12 entities and 60 entities are not the same project. Whether your metric definitions exist anywhere, because reverse engineering what "Active Customer" means from six stakeholders and four spreadsheets is the biggest schedule risk on these builds and it is not an engineering task. Whether you need a warehouse migration. Whether you want a real pivot table with subtotals and expand or collapse, which is 3 to 5 weeks on its own. And scheduling plus PDF plus large exports, which together add 2 to 3 weeks that get quoted as "and export".

When you should not build this

If your data model is under roughly 30 tables, your users are internal or a small set of business customers, and an embedded frame in your app is acceptable, do not build this. Embed Metabase, which is free to self host under the AGPL licence, or Apache Superset under Apache 2.0. If you want a metrics layer without owning a query compiler, Cube Core is Apache 2.0 and you write YAML instead of a SQL generator. Managed embedded options worth quoting include Explo, Luzmo, Omni, Sigma and Power BI Embedded.

Take that path if reporting is a retention feature rather than the thing you sell. Build it yourself when reports are the product, when your row level permission logic is too specific for any vendor's model, when the builder must live inside your own components with no frame, or when per seat vendor pricing destroys your margins past a few hundred tenants. One honest caveat: buying is not free. Modelling, embedding, single sign-on and theming any embedded business intelligence tool is realistically 3 to 6 weeks. Vendors call it an afternoon.

How to brief and vet a developer for this

Anyone who quotes this without asking to see your schema is quoting a column picker. Send the schema first, then ask these on the call.

How do you stop a fan-out from double counting revenue when a user joins orders to line items? If the answer is not symmetric aggregates or aggregating to grain in a subquery, they have never shipped this. Where exactly does the tenant filter get injected? The right answer is the compiler or row level security, not the UI or a repository method. What is your statement_timeout and which database role carries it? What goes into the cache key? If permission context is not in it, stop. What happens to a 2:30am America/Chicago schedule on the spring forward day? What happens to 400 saved reports when we rename a column? Do scheduled reports run as the owner or the recipient, and why? Finally, ask them to demo a 5 million row export end to end. The people who have shipped this will have opinions before you finish the question, and they will tell you which parts they would refuse to build.

When you are ready to turn this into a specification, Digital Heroes builds and runs its own products, so the people choosing your architecture live with those decisions on their own revenue. The document is yours whichever way you go.