Every company has one: a beautiful dashboard with twelve charts, a gauge, a map and a donut chart, built with real care. And every Monday morning, the manager it was built for opens a spreadsheet instead.

It's not because they're stubborn. It's because the dashboard answers questions nobody was asking, and the spreadsheet answers the one they have right now.

Reporting is a big part of what I build in Kate PMS and Kate HRM, where managers rely on reports to keep track of contracts, payments, maintenance and people. Over time I've come to believe the hard part of reporting is rarely the SQL. It's figuring out what the report is for. Here are the principles I follow, from the first conversation to the query plan.

1. Start with the decision, not the data

The worst way to start is "what data do we have? Let's show it." You end up with charts of everything, which means insight into nothing.

I start with a different question: "What will you do differently when this number changes?"

• "Which contracts expire in the next 90 days?" leads to an action: start renewal conversations.
• "Which maintenance requests have been open longest?" leads to an action: chase them.
• "Total number of records in the system" leads to... a nod. Nobody acts on it.

If a number doesn't lead to a decision or an action, it's decoration. It can live somewhere, just not on the first screen.

2. Fewer numbers, each with a definition

A good dashboard has a handful of numbers, not dozens. Each one should survive the question "what exactly does this count?"

This sounds pedantic until two managers look at "active tenants" and get different numbers from different screens. Does "active" include contracts signed but not started? Notice periods? The day the contract ends? When two reports disagree, people stop trusting both of them, and trust is the whole product.

So I write definitions down, agree them with the people who use the report, and put them in the code in one place. One method, one scope, used by every report that shows that number:

// One definition, reused everywhere (simplified)
public function scopeActiveOn(Builder $query, CarbonInterface $date): void
{
    $query->where('starts_on', '<=', $date)
        ->where('ends_on', '>=', $date)
        ->whereNull('terminated_at');
}

A small tooltip in the UI that explains each number in plain words helps as well. It answers the question before it becomes a support ticket.

3. Every number should open the rows behind it

A manager sees "14 overdue payments" and the very next thought is "which ones?" If the answer is "ask IT for a list", the dashboard has failed at the exact moment it became useful.

Make every number clickable through to a filtered list of the records it counts. It does two jobs: people can act on the number, and they can check it. A number you can check is a number you can trust.

4. Exports aren't a failure, they're a feature

Developers sometimes treat "can I get this in Excel?" as a sign the dashboard isn't good enough. I don't. Managers need to filter, annotate, forward and combine data in ways no screen will predict. Give them a clean export of every list, and you've made the report useful for things you never designed.

For exports, stream the file rather than loading every row into memory:

return response()->streamDownload(function () use ($from, $to) {
    $out = fopen('php://output', 'w');
    fputcsv($out, ['Reference', 'Due date', 'Amount', 'Status']);

    Payment::query()
        ->whereBetween('due_date', [$from, $to])
        ->lazyById(1000)
        ->each(fn ($p) => fputcsv($out, [$p->reference, $p->due_date, $p->amount, $p->status]));

    fclose($out);
}, 'payments.csv');

lazyById() fetches rows in chunks behind the scenes, so memory stays flat however big the export gets. For really heavy exports, generate the file in a queued job and notify the user when it's ready, instead of keeping a request open.

5. Respect who is allowed to see what

Reports are a classic place for access control to leak. A screen might be properly locked down, while the report that summarises the same data isn't. Apply the same role-based rules to reports and exports as to the rest of the system: the same policies and scopes, not a separate set of checks someone has to remember. An export is a copy of your data walking out the door, so treat the export button with the same care as the screen.

6. Make report queries fast, and keep them fast

Reports are often the slowest queries in an application, because they scan ranges and aggregate a lot of rows. A few habits keep them quick:

• Aggregate in the database, not in PHP. SUM, COUNT and GROUP BY in SQL beat loading thousands of models to add them up in a loop.
• Index for the filters reports actually use, usually a date column combined with a status. Check with EXPLAIN rather than guessing.
• Always have a date range. Default to something sensible like this month. "All time" should be a deliberate choice, not the default.
• Pre-compute what's expensive. If a summary is slow and doesn't need to be live to the second, a scheduled command can build a small summary table each night, and the dashboard reads that instead.
• Cache short-lived results for dashboards many people open at the same time, and show "last updated" so nobody is confused by a number that's a few minutes old.
• Be explicit about time zones. "Today" in a report should mean today where the user is, not where the server is.

7. Ship small, then watch what people use

Don't build the full dashboard in one go. Ship two or three numbers that answer real questions, then listen. Which ones do people click? What do they still export to Excel and rebuild? That spreadsheet they keep making by hand is the best requirements document you'll ever get.

The short version

• Start from the decision the report supports.
• Few numbers, each with one agreed definition in one place in the code.
• Every number links to its rows. Every list can be exported.
• Same access rules for reports and exports as for screens.
• Aggregate in SQL, index for the filters, pre-compute the heavy parts.

What's the one report in your company that people actually open every week? I suspect it's simpler than the one everyone thinks they need.