Ad Hoc Analysis and Ad Hoc Reporting: Meaning, Examples and a Worked SQL Query
by GainHQ Editorial Team | Oct 26, 2026 | Software Development Insights | Editorial policy
Table of Contents
- What is ad hoc analysis?
- What is an ad hoc report?
- Ad hoc reporting vs canned reports vs dashboards
- Ad hoc analysis examples
- Worked example: one ad hoc question, answered with SQL
- How to do ad hoc analysis in six steps
- Guardrails that keep ad hoc answers trustworthy
- When should an ad hoc question become a dashboard?
- Tools for ad hoc analysis and reporting
- How GainHQ can help
Ad hoc analysis is the process of using data to answer a specific business question that your existing reports and dashboards don't already answer. It usually starts with a one-off question, such as "Why did refunds jump last week?", and ends when you have enough evidence to make a decision. Ad hoc reporting is the narrower step of producing a one-time report for that question.
This guide explains both terms, shows how they differ from scheduled (canned) reports, and walks through a small, worked SQL example so you can see what an ad hoc analysis actually looks like. It finishes with the guardrails that stop quick answers from turning into conflicting numbers.
What is ad hoc analysis?
Ad hoc analysis is an on-demand investigation of data to answer a question that comes up in the moment. "Ad hoc" is Latin for "for this", and Merriam-Webster defines it as "for the particular end or case at hand without consideration of wider application". That is the key idea: the work is built for one question, not for a recurring audience.
Microsoft's Business Central documentation puts it practically: sometimes you need to analyze data "in a way that the standard reports don't support", and those analyses are often called ad hoc. In practice, ad hoc analysis is iterative. You get a first answer, it raises a follow-up question, and you slice the data again until the cause is clear.
Ad hoc analysis is not a separate type of analytics. Most ad hoc questions are descriptive ("what happened?") or diagnostic ("why did it happen?"). If you want the foundations of the first kind, see our guide to descriptive analytics.
What is an ad hoc report?
An ad hoc report is a one-time report created to answer a specific question, rather than a report that runs on a schedule for a fixed audience. A sales manager asking "How many orders came through the partner channel in Q3, by region?" needs an ad hoc report: one query, one table, done.
The difference from ad hoc analysis is scope. An ad hoc report answers what. Ad hoc analysis keeps asking why until there is something to act on. A report is often the first step of an analysis, but an analysis usually needs several reports, comparisons and a written conclusion.
Ad hoc reporting vs canned reports vs dashboards
The simplest way to tell these apart is by who asks the question, how often it is asked, and whether the format is fixed in advance. Canned (standard or scheduled) reports and dashboards answer known, recurring questions. Ad hoc work answers new ones.
| Ad hoc report | Ad hoc analysis | Canned / scheduled report | Dashboard | |
|---|---|---|---|---|
| Question | One specific "what" question | A "why" question that evolves as you dig | A known question asked every period | A set of known metrics watched continuously |
| How often | Once | Once (may lead to follow-ups) | Daily, weekly, monthly | Always on |
| Format | Whatever answers the question | Several cuts of the data plus a conclusion | Fixed layout | Fixed layout with filters |
| Built by | The person asking, or an analyst | Analyst or data-literate business user | Analyst or BI team, once | Analyst or engineering team, once |
| Typical output | A table or chart | A short write-up with a recommendation | PDF, email or spreadsheet | Interactive web view |
| Main risk | Inconsistent metric definitions | Wrong conclusion from thin data | Answers stale questions | Clutter; nobody checks it |
A useful rule: if the same ad hoc question gets asked three or four times, it has stopped being ad hoc. It belongs in a scheduled report or a dashboard (more on that below).
Ad hoc analysis examples
Ad hoc questions show up in every team. The examples below are illustrative, not GainHQ client data. Each one starts as a single question and would normally lead to at least one follow-up cut of the data.
| Team | Example ad hoc question | Typical first cut | Likely follow-up |
|---|---|---|---|
| Sales | Why did average deal size drop this quarter? | Deal size by segment and quarter | Discount levels by sales rep |
| Marketing | Which campaign drove last week's sign-up spike? | Sign-ups by source and day | Activation rate of those sign-ups |
| Finance | Why is gross margin down while order volume is flat? | Margin by product line | Supplier cost changes by month |
| Product | Did the new onboarding flow change activation? | Activation rate before vs after release | Activation by device and plan |
| Support | Why did ticket volume rise on Tuesday? | Tickets by category and hour | Tickets linked to a specific release |
| Operations | Which warehouse is causing late deliveries? | Late orders by warehouse | Late orders by carrier within that warehouse |
Notice the pattern: the first cut is a report, and the follow-up is what makes it analysis.
Worked example: one ad hoc question, answered with SQL
The best way to understand ad hoc analysis is to watch one happen. The data below is a small, made-up orders table (illustrative only, not real company data). The numbers were computed by running the queries in SQLite, and the SQL is standard enough to run in PostgreSQL too.
The question
On Monday, a finance lead asks: "Refunds feel high this week. Is that real, and where is it coming from?" No existing report splits refunds by sales channel, so this is a classic ad hoc question.
The table orders has 18 rows covering two weeks (5–11 October and 12–18 October 2026), with these columns: order_id, order_date, channel (web, app or partner), amount and status (paid or refunded).
Step 1: Is the change real? (the ad hoc report)
The first query answers what: how many orders and refunds did each week have?
SELECT
CASE WHEN order_date < '2026-10-12' THEN 'Week 1' ELSE 'Week 2' END AS week,
COUNT(*) AS orders,
SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunds
FROM orders
WHERE order_date BETWEEN '2026-10-05' AND '2026-10-18'
GROUP BY week
ORDER BY week;
The query returns:
| Week | Orders | Refunds | Refund rate |
|---|---|---|---|
| Week 1 | 8 | 1 | 12.5% |
| Week 2 | 10 | 4 | 40.0% |
The refund rate went from 12.5% (1 of 8) to 40.0% (4 of 10). The finance lead's instinct was right. On its own, this is an ad hoc report.
Step 2: Where is it coming from? (the analysis)
The follow-up question is why, so we split the same counts by channel.
SELECT
channel,
COUNT(*) FILTER (WHERE week = 1) AS wk1_orders,
COUNT(*) FILTER (WHERE week = 1 AND status = 'refunded') AS wk1_refunds,
COUNT(*) FILTER (WHERE week = 2) AS wk2_orders,
COUNT(*) FILTER (WHERE week = 2 AND status = 'refunded') AS wk2_refunds
FROM (
SELECT channel, status,
CASE WHEN order_date < '2026-10-12' THEN 1 ELSE 2 END AS week
FROM orders
WHERE order_date BETWEEN '2026-10-05' AND '2026-10-18'
) AS t
GROUP BY channel
ORDER BY channel;
COUNT(*) FILTER (WHERE ...) counts only the rows that match each condition. It works in PostgreSQL and in SQLite 3.30 or later; in MySQL, use SUM(CASE WHEN ... THEN 1 ELSE 0 END) instead. The query returns (refunds / orders):
| Channel | Week 1 refunds / orders | Week 2 refunds / orders | Week 2 refund rate |
|---|---|---|---|
| app | 1 / 3 | 3 / 4 | 75.0% |
| partner | 0 / 2 | 0 / 2 | 0.0% |
| web | 0 / 3 | 1 / 4 | 25.0% |
Three of the four week-2 refunds came from the app. Outside the app, week 2 had 1 refund in 6 orders, against 0 in 5 orders in week 1. The increase is concentrated in one channel.
The filter on dates sits in WHERE, not HAVING. As the PostgreSQL tutorial explains, WHERE selects input rows before groups and aggregates are computed, while HAVING filters groups after aggregation. Putting row filters in WHERE is both correct and cheaper.
Step 3: What the result does and does not tell you
The analysis points to the app, but it does not prove a cause. Four app orders is a tiny sample, and three refunds could be a coincidence. The honest conclusion is: "Week-2 refunds are concentrated in the app channel; check whether an app release or payment change went out around 12 October before acting."
That last sentence is the deliverable. A good ad hoc analysis ends with a finding, its confidence level, and the next check, not just a table.
How to do ad hoc analysis in six steps
A repeatable process keeps one-off work fast without making it sloppy. These steps follow the worked example above.
- Write the question down in one sentence. "Are refunds up this week, and in which channel?" is testable. "Look into refunds" is not.
- Agree the metric definition before you query. What counts as a refund, and is the refund rate per order or per revenue? Use the official definition if one exists.
- Start with the smallest query that answers "what". Totals by period first, then break down.
- Drill down one dimension at a time. Channel, then region, then product. Changing several cuts at once hides which one matters.
- Sanity-check the result. Do the totals match a known number? Are any groups too small to trust? Are NULLs handled correctly?
- Write a short conclusion and save the query. State the finding, the confidence, and the next step, and keep the SQL with it so someone else can rerun it.
Guardrails that keep ad hoc answers trustworthy
The biggest risk with ad hoc analysis is not a slow answer but a wrong or conflicting one. When many people write their own queries, "revenue" or "active user" can quietly mean different things in each. These guardrails are cheap to put in place.
| Guardrail | Why it matters | How to apply it |
|---|---|---|
| Shared metric definitions | Two people asking the same question should get the same number | Define key metrics once in a modelling or semantic layer. The dbt Semantic Layer docs describe this approach: centralising definitions so business units "are working from the same metric definitions, regardless of their tool of choice". |
| Read-only access | An exploratory query should never change production data | Give analysts read-only roles. In PostgreSQL, a read-only transaction "cannot alter non-temporary tables", and default_transaction_read_only makes that the default. |
| Query time limits | A runaway ad hoc query can slow down the systems customers use | Use a replica or warehouse for analysis, and set limits such as PostgreSQL's statement_timeout, which aborts any statement that runs longer than the set time. |
| Cost checks before running | On pay-per-query warehouses, one careless scan can be expensive | Estimate first. BigQuery's dry run validates a query and estimates the bytes it will process, and you are not charged for it. |
| Correct NULL handling | Counts and averages can silently differ | In SQL, COUNT(*) counts every row while COUNT(column) counts only non-NULL values, and AVG() ignores NULLs, as the SQLite aggregate function docs set out. Decide which one your metric needs. |
| Minimum sample sizes | Small groups produce dramatic but unreliable percentages | Show counts next to every rate, as in the worked example, and flag groups too small to trust. |
| Saved, documented queries | Undocumented answers can't be checked or reused | Store the query, the date it ran and the conclusion together. |
| Access to sensitive data | Ad hoc queries can expose personal data to people who don't need it | Limit access to personal and financial fields, and prefer aggregated or masked views for exploration. See our SaaS security best practices. |
Not every guardrail needs a data team. Business tools increasingly build some in. For example, Microsoft's data analysis mode in Business Central lets users group, filter and pivot list data for "quick fact checking and ad hoc analysis", and states that it "doesn't change the underlying data".
When should an ad hoc question become a dashboard?
An ad hoc question should become a scheduled report or dashboard once it is asked regularly, by more than one person, to support a recurring decision. At that point, rebuilding the answer by hand each time wastes effort and invites inconsistent numbers.
| Signal | Keep it ad hoc | Promote it to a report or dashboard |
|---|---|---|
| How often it's asked | Once, or rarely | Several times a month |
| Who asks | One person or team | Several teams or leadership |
| Decision it supports | A one-off decision | A recurring decision (weekly pricing, monthly budget) |
| Metric definition | Still being worked out | Agreed and stable |
| Data needed | Ad hoc extract is fine | Needs reliable, regularly refreshed data |
In the worked example, if finance starts asking for refunds by channel every Monday, that query should become a tile on a refunds dashboard. Our guide to custom dashboard development covers how to plan one, and writing the metrics down first in a short product requirements document prevents rework.
The reverse also matters: dashboards that nobody uses should be retired, and their questions handled ad hoc when they come up.
Tools for ad hoc analysis and reporting
The right tool depends on who is asking and how much data there is. Most teams use a mix.
| Tool type | Good for | Watch out for |
|---|---|---|
| Spreadsheets | Small extracts, quick pivots, sharing with non-technical colleagues | Copies go stale; formulas are hard to audit |
| SQL on a database replica or warehouse | Precise, repeatable answers on large data | Needs SQL skills and the guardrails above |
| BI tools with self-service exploration | Business users slicing governed data without SQL | Metric definitions must be set up first |
| Analysis features inside business apps (ERP, CRM) | Questions about that app's own data | Limited to one system's data |
| Custom internal tools | Recurring questions that span several systems | Worth building only once a question is frequent; see custom internal tools and build vs buy |
If your ad hoc questions are mostly about what will happen next rather than what happened, you have moved into forecasting territory; our guide to predictive analytics software covers that next step.
How GainHQ can help
GainHQ is a custom software development company with 15+ years of experience and 13+ products built, including our own products Payrun (HR and payroll) and Gain.io (CRM and helpdesk). When ad hoc questions keep coming back, we help teams turn them into reliable internal tools, dashboards and data workflows with clear metric definitions and safe access controls. See our services for custom software development, SaaS solutions, UI/UX design and technology consulting.
Frequently asked questions
What is ad hoc analysis in simple terms?
Ad hoc analysis means using data to answer a specific question that your regular reports don't cover, at the moment it comes up. You query or slice the data, look at the result, ask a follow-up question if needed, and stop once you have enough evidence to make a decision.
What is an ad hoc report?
An ad hoc report is a one-time report built to answer one specific question, such as "How many partner-channel orders did we get in Q3, by region?" Unlike a scheduled report, it isn't sent to a fixed audience on a regular cycle.
What is the difference between a standard report and an ad hoc report?
A standard (canned) report answers a known question on a schedule, in a fixed layout, usually for a defined audience. An ad hoc report is created once, on demand, for a new question, and its format is whatever best answers that question.
What is the difference between ad hoc analysis and ad hoc reporting?
Ad hoc reporting answers "what happened", for example a refund count by week. Ad hoc analysis continues to "why", drilling into the data, such as by channel or product, until it reaches a conclusion with a recommended next step. A report is often the first step of an analysis.
Can you give an example of ad hoc analysis?
A finance lead notices refunds look high. A first query shows the refund rate rose from 12.5% to 40% week on week in an illustrative dataset. A second query splits by channel and shows three of four week-2 refunds came from the app, so the next check is recent app changes.
What is an ad hoc query?
An ad hoc query is a database query written for a one-off question rather than saved inside an application or scheduled report. It is usually written in SQL or built in a BI tool's visual query editor, and it should run with read-only access and sensible time limits.
What are the risks of ad hoc analysis?
The main risks are inconsistent metric definitions, conclusions drawn from very small groups, queries that slow down production systems or run up warehouse costs, mishandled NULL values, and exposure of sensitive data. Shared definitions, read-only access, time limits and showing counts next to rates reduce these risks.
When should an ad hoc report become a dashboard?
When the same question is asked regularly, by several people, to support a recurring decision, and the metric definition is agreed. At that point a scheduled report or dashboard is faster and more consistent than rebuilding the answer by hand each time.