Ad hoc reporting: what it is and how managers can do it with AI
Ad hoc reporting is building a report to answer one question when someone asks it, as opposed to running a standard report that produces the same layout every week. “Which customers received lot 2417?” is an ad hoc question. So is “which jobs lost money last quarter, and on which operation?” When no standard report answers a question like these, it becomes a spreadsheet or a request in IT’s queue. AI changes who can build the answer: a manager asks in plain language, and the AI writes the query and shows its work for the manager to check. This guide covers ad hoc and standard reports, why canned ERP reports fall short, ad hoc reporting tools and the governance that keeps the numbers consistent.
What ad hoc reporting means
A standard report, sometimes called a canned report, is designed once and run many times with the same columns, filters and layout. An ad hoc report is built to answer one question when it comes up. It may be used once and discarded, or it may prove useful and become a standard report itself.
Questions that come from events are ad hoc by nature. A large customer threatens to leave, or an inspector asks for records, and someone needs numbers that no report was designed to produce. An ad hoc report helps only if it arrives before the decision is made.
Ad hoc reports compared with standard reports
| Aspect | Standard report | Ad hoc report |
|---|---|---|
| Built by | The ERP vendor, a consultant or IT, once | The person with the question, or an analyst, when the question comes up |
| Runs | On a schedule or on demand, the same way each time | Once, or a few times while the question is open |
| Answers | Questions known in advance | Questions that come from events |
| Definitions | Fixed in the report’s design | Chosen each time, so two people can choose differently |
| Checked | When the report is built and tested | Each time, by whoever builds it |
| Example | Monthly sales by salesperson | Sales to customers who also had a late delivery this quarter |
The two work together. Standard reports cover the questions a business asks every week. Ad hoc reports cover the rest, and the useful ones should become standard reports, as the governance section describes.
Why canned ERP reports fall short
Canned reports answer the questions their designers expected. Microsoft’s own example of a report to schedule in Business Central is a weekly sales report by salesperson. A report like that is useful because it doesn’t change. It falls short in four situations:
- The question crosses modules. “What did this job really cost?” needs the quote, the purchase orders, the production hours and the invoice.
- The data lives outside the ERP. Rebates sit in a spreadsheet, and freight bills sit in a separate accounting system.
- The question needs a different definition. A report that counts invoiced sales cannot answer a question about orders shipped but not yet invoiced.
- The question has a deadline. A recall or a large customer’s complaint needs an answer the same day.
In food, the deadline is set by regulation. Under Canada’s Safe Food for Canadians Regulations, a food business covered by the traceability rules must provide its traceability documents to the Canadian Food Inspection Agency within 24 hours of a request, or sooner if the agency believes there is a risk of injury to human health. Electronic documents must be “in a single file,” in plain text and capable of being imported into standard commercial software. A traceability request is an ad hoc report with a 24-hour deadline: every customer who received a given lot, with dates and addresses.
How ad hoc reports get built by hand
Without AI, an ad hoc report follows a familiar path. A manager asks the person who knows the ERP. That person exports several lists, matches them in a spreadsheet, pivots the result and checks it against a total they trust. The next time someone asks a similar question, they start again from the export.
ERP vendors have made parts of this easier. Oracle says NetSuite’s SuiteAnalytics Workbook is designed so that users “without much record schema or query language knowledge” can build workbooks by drag and drop. Tools like it help most when the data sits inside the ERP. Data held in spreadsheets or other systems still has to be brought in first.
The work is real analysis, done by someone who knows the data. The constraint is the queue. When one or two people answer every question, each question waits for their time.
What AI changes
AI moves the query writing from a specialist to the person with the question. The manager asks in plain language. The AI looks up the definitions, writes the query, runs it with read-only access and returns the table with the query and the rows it used. The manager checks the result and decides whether to share it. Microsoft says Copilot in Power BI can “generate DAX queries for ad hoc calculations,” DAX being the query language Power BI uses. AI reporting covers each step and how to check the answers.
Three examples show the pattern. The numbers are illustrative.
A manufacturer: which jobs lost money?
The plant manager asks: “Which jobs closed last quarter with actual hours more than 20% over the estimate, and which operation ran over?” The AI joins estimated hours from the quotes, actual hours from production and shipped quantities from sales, and returns 14 jobs with the operations listed. Before the list goes to the estimators, the manager opens three of the jobs and confirms that the hours match the time records.
An auto-parts distributor: who stopped buying?
The sales manager asks which accounts bought in each of the last four quarters but not in this one. The AI returns 37 accounts with the last order date, the sales rep and the product family each account bought most. Two accounts look wrong. One turns out to have moved to a new account number, so the definition of “customer” gets a rule for merged accounts, and the report is rerun.
A food producer: who received the lot?
The quality manager receives a traceability request for lot 2417. The AI traces the lot from the production batch to every shipment and returns each customer, quantity, ship date and address in one file. The manager compares it with the shipping documents and sends it the same morning.
In each case the AI does the joining and counting, and a person who knows the business checks the result before anyone uses it.
The people who used to write these reports still have a role, earlier in the process. Instead of building each report, IT or the ERP consultant maintains the definitions, the permissions and the test questions that every answer depends on. Someone who already knows your ERP’s tables is the right person to write the first definitions.
Find out which questions a manager could ask directly
Tell Derik which ERP you run and which reports people wait for today. He will tell you which ones AI can answer from your data and what that takes.
Start a conversationAd hoc reporting tools
Ad hoc reporting tools fall into four groups, and a business can use more than one.
| Group | Examples | Good for | Watch for |
|---|---|---|---|
| Spreadsheets | Excel or Google Sheets with ERP exports | Starting today with no new software | Manual steps repeated each time, and no record of how a number was produced |
| ERP report writers and list analysis | Business Central analysis mode, NetSuite SuiteAnalytics Workbook | Questions about data inside one ERP | Data held outside the ERP |
| BI tools with AI assistants | Power BI with Copilot, Looker conversational analytics, Tableau | Questions over a data model your team maintains | Someone has to build the model and keep it current |
| AI query tools on a data platform | Snowflake Cortex Analyst, Databricks Genie, custom systems on a reporting database | Questions that span many sources | Needs a reporting database and written definitions first |
To choose, look at where your questions’ data lives. If most questions are about data inside one ERP, start with that ERP’s own analysis tools. If they combine the ERP with the accounting system and spreadsheets, you need a reporting database, and an AI query tool on top of it. Whatever the tool, ask the vendor how it applies your permissions, whether it shows the query behind each answer and where it processes your data. AI business intelligence compares the categories in more depth.
The governance ad hoc reporting needs
Without rules, ad hoc reporting can produce two numbers for the same thing. Five rules keep it consistent.
- One definition per term. Write down what “sales,” “margin,” “late” and “active customer” mean, once, in a place that every report and the AI read. Looker’s conversational analytics works this way: it uses the definitions in its data model “as its source of truth.”
- Permissions follow the person. Each question runs with the access of the person asking, so a sales rep sees their own territory and nothing more.
- Every question is logged. Keep the question, the query, the data refresh it ran on and who asked. When a number is challenged a month later, the log shows how it was produced. It also shows which questions keep coming back.
- A second person checks what leaves the building. The asker checks every result. Anything going to a customer, a lender or a regulator gets a second review. Human in the loop covers designing that review.
- Repeated questions become standard reports. When the same question comes up a third time, save the checked query as a standard report with a named owner.
Start with the first rule. When two reports disagree, compare their definitions before anything else, such as whether a sale counts on the order date, the ship date or the invoice date.
What data ad hoc reporting needs
Ad hoc questions draw on the same sources as any reporting: the ERP, the accounting system and the spreadsheets that hold prices, rebates and budgets. Copy them read-only into one reporting database, so heavy questions don’t slow the ERP that people are using to ship orders. A nightly copy answers questions about last week or last quarter. Questions about the last hour need a live, read-only connection. Keep history as well: an ad hoc question about last year’s stock levels needs last year’s data.
How to start small
Start with the questions already waiting. Ask whoever builds reports by hand for the last two months of requests, and pick the ten that come up most. Collect the answers they produced and use them as a test set. Write the definitions those questions depend on, copy the data read-only, and compare the AI’s answers with the known ones before any manager uses it alone. AI evals covers building and running that test set.
Once the first ten work, widen the circle one team at a time. Sales and purchasing questions make a good second round, because they reuse the same customer and item data. Margin analysis is a natural third, since it asks ad hoc questions of the same sales and cost lines every month.
Risks and limits
- Answers that look right and aren’t. A query with a wrong filter returns a tidy table. The five checks in AI reporting are designed to catch these, as long as someone runs them.
- More numbers in circulation. When asking is easy, more figures get shared. Label ad hoc results as unchecked until a person reviews them.
- Definitions chosen by the model. If no definition exists, the AI picks one. Make it state which one it used, then add the missing definition.
- Where the data goes. Each question and its results pass through an AI model. Ask where the model runs and what the vendor keeps. Data sovereignty covers what a Canadian business controls, and private AI for business covers keeping the model on your own server.
How ThriveAI helps
ThriveAI is an AI engineering company in Ottawa that builds private AI systems on a company’s own data, for businesses that make, move or sell physical goods. It brings what a company’s ERP, email, drawings and spreadsheets hold into one database that belongs to the company. A manager asks questions in plain language, and every answer shows where it came from. When the data cannot answer a question, the system says what is missing instead of guessing.
Every connection only reads data, including from older ERP versions installed on a company’s own server. The platform is designed to keep each client’s data on its own server in Canada. The client chooses a model on that server or a hosted model under a written zero data retention agreement, and a hosted model may process requests outside Canada. Derik Lawlis, the founder, leads every project, and working sessions run on site in French or English. About ThriveAI covers the company.