How do I get better reports out of Sage 100 and connect it to other tools?
Short answer: Sage 100 is Sage's accounting and operations system (an ERP, the main system that holds your orders, stock, customers and books) for small and midsize businesses. Its reports come from four places: ready-made reports in each module, Crystal Reports (a layout design tool) for custom printed reports, Sage Intelligence Reporting for reports built in Excel, and Business Insights for drill-down and dashboards. To link Sage 100 to other tools, Sage lists Visual Integrator (import and export through ODBC, a standard way for programs to read a database) and Microsoft Office Link, and anything beyond that is custom work.
You have the numbers somewhere in Sage 100, but getting them into the right shape, on time and without someone rebuilding a spreadsheet every Monday, is the hard part.
I'm Ranjit, and I run Blur Studio: 15+ years building software, 120 apps taken live, and clients who have gone on to raise $10M+. I build AI systems and processes that help businesses do a lot more with a lot less time. I also work differently from most of what else is out there, more on that further down this page. Say hello.
What reporting tools does Sage 100 have?
Sage's own insights and reporting page for Sage 100 lists these tools. Most owners use one or two and never learn the rest. A closer look is in SAP Business One reporting.
| Tool | What it does | Who it suits |
|---|---|---|
| Ready-made reports | Sage says there are over 200 predefined customizable reports, hundreds of journals and over 50 forms out of the box | Everyone, day one |
| Crystal Reports | Modify or create custom reports from Sage 100 tables, with a run-time editor and wizards | Printed forms, invoices, fixed layouts |
| Sage Intelligence Reporting | Pulls Sage 100 data into Excel automatically, so you build reports with Excel skills. Has Report Viewer, Manager, Designer and Connector modules | Staff who know pivot tables and formulas |
| Business Insights Explorer | Drill into customers, balances, credit limits and sales history | Credit and sales questions |
| Business Insights Reporter | Predesigned reports you can customize | Managers who want a head start |
| Business Insights Dashboard | Real-time visual dashboards | Owners who want a glance view |
Sage sells Sage 100 in three plans: Essentials, Advanced (adds purchase orders, sales orders and inventory management) and Complete (adds return merchandise authorization, bill of materials and barcode mobility). The Sage page lists Business Insights and Sage Intelligence under Essentials. Sage's page offers a quote request rather than a price. Ask your reseller which reporting tools your license already includes before you pay for anything extra.
Sage Intelligence has its own site, which lists a "Sage 100 Intelligence Reporting" product alongside versions for other Sage systems. It describes pre-built reports and dashboards you can use at once, editable financial reports, drill-down from an overview into detail, and a Report Utility library of updated reports that is imported into the Report Manager module. It also lists a directory of report writers for custom work, which is useful if nobody on your team knows Excel well enough to build the workbook.
What is the Sage 100 report designer?
There is no single button called report designer. People use the phrase for two different things, and mixing them up wastes weeks.
- Crystal Reports: A report design tool that Sage lists for modifying or creating custom Sage 100 reports. You drag fields onto a page, group rows and add formulas. It is the right pick for an invoice, a pick ticket or a statement that must look the same every time.
- Sage Intelligence Reporting Designer: One module of Sage Intelligence. It works inside Excel, so a report is a workbook that refreshes from Sage 100 data.
My rule: if the output gets printed or emailed as a form, use Crystal. If the output gets sorted, filtered and charted by a person, use Sage Intelligence. Trying to make Crystal do spreadsheet work is where most people get stuck.
How do I customize a Sage 100 report?
Whichever tool you use, the habits that save you are the same.
- Copy before you change. Never edit the standard report. Save a copy under a new name so a Sage update does not overwrite your work.
- Write the question first. One sentence, such as "Which customers have open orders older than 30 days, and how much is each worth?" If you cannot say it in a sentence, the report will wander.
- Start from the closest ready-made report. Adding two columns to an existing report is cheaper than building one.
- Test against a number you trust. Compare the new report with one figure you already know, such as total accounts receivable at month end.
- Keep a change log. A short note on what you changed and why makes repairs after an upgrade far easier.
Sage also says Custom Office lets you change screens, add user-defined fields and set default values. Ask your Sage partner whether a field you add, such as a "delivery window" on a customer, can be pulled into your reports.
How do I export Sage 100 reports to Excel?
You have three routes, from simplest to most useful.
- Send a single report to a file. Many Sage 100 reports can be sent somewhere other than the printer. The exact options depend on your version, so ask your Sage partner to show you the one for the report you use most. This gives you a snapshot only.
- Sage Intelligence Reporting. Sage says it extracts Sage 100 data automatically and imports it into Excel. You refresh the workbook instead of re-exporting. This is the route I would try first.
- Visual Integrator. Sage lists it for importing and exporting data from ODBC-compliant sources, with scheduled jobs and data checks. It suits repeat jobs, such as a nightly file of open orders for another system.
A snapshot is out of date the moment someone changes an order. If a person runs the same export every Monday and pastes the results together, that is a sign you need a refreshing report, not a better export.
A guide that republishes Sage 100's Visual Integrator help describes the setup in Job Maintenance. You pick the target (for example customers or invoices), the source file, and whether the job adds records, updates them or both. You then match the file's delimiter and header rows, and map each source column to a Sage field, starting with the key such as the customer number. An ASI Sage partner page adds that jobs can run on a schedule, one job can trigger the next (customers first, then invoices), and a script can launch after a successful import.
A worked example, with made-up figures
Say a distributor with 400 customers wants a Monday report of open orders by customer. In a spreadsheet, a clerk exports orders (20 minutes), exports invoices (15 minutes), matches them by hand (40 minutes) and fixes mistakes (20 minutes). That is about 95 minutes a week, or roughly 82 hours a year. With a Sage Intelligence workbook that refreshes from Sage 100, the clerk opens the file, clicks refresh and checks three totals. Call that 10 minutes. The saving is about 70 hours a year, and the numbers stop depending on who built the sheet. These figures are an illustration, not a benchmark.
Why are my Sage 100 reports not working?
When a report looks wrong, the data is usually fine. The report is asking a slightly different question than you think. Check these in order.
- Filters: Posted versus unposted entries, and open versus historical documents, change totals.
- Date basis: Two reports can disagree because one uses invoice date and the other uses posting date.
- Timing: A report shows data as it stood when it ran. A mismatch between sub-ledgers (the detail behind accounts receivable and payable) and the general ledger can appear if entries have not been posted.
- Master data: A customer with no sales rep, or an item with no product line, falls out of grouped totals.
- Permissions: A user without access to a module may get a blank report.
- After an upgrade: A custom Crystal report can break when database fields change. Re-run it and compare against a known number.
- Excel connections: A Sage Intelligence workbook can point at the wrong company or period after a rollover.
If none of these explain it, ask your Sage partner to compare the report with a simple direct query on the same data.
What goes wrong with Visual Integrator imports
Visual Integrator applies Sage's normal business rules, so an import is held to the same checks as typing, according to the republished help. These are the three errors it lists most often.
- "Record not on file": The file refers to a key, such as a customer number, that does not exist in Sage. Import customers first, then their invoices.
- Invalid or missing required field: Usually a date, yes/no value or number in the wrong format.
- Length or overflow: The source value is longer than the Sage field allows.
The same guide advises choosing, per job, whether bad records are skipped or the job stops at the first error. Stopping is safer for general ledger postings, because a half-loaded batch is harder to untangle than one that never started. Test with a small mixed sample in a test company, check the result in Sage's own screens, run the job by hand until it succeeds, then schedule it, and keep the timestamped input files so a failure can be traced.
How can I connect Sage 100 to other tools?
Sage's customization and integration page lists the options.
- Visual Integrator: Import and export through ODBC, with scheduling.
- Microsoft Office Link: Merges accounting data into Word, Excel or Exchange for documents and messages.
- Other apps: Check whether your web store or CRM (customer relationship management, the tool that tracks leads and customers) lists a Sage 100 connection on its own page.
- Sage CRM: Listed in the Essentials plan.
- Custom work: Anything else, such as a customer ordering page that writes orders into Sage 100, needs a developer or a Sage partner.
When a vendor says it integrates with Sage 100, ask four questions: what data moves, in which direction, how often, and who fixes it when it stops. A connection that runs once a day is a different product from one that runs on every order.
If your question is about orders arriving by email and being retyped, my guide to email order processing covers that gap, and the B2B customer portal software guide covers letting customers place orders themselves.
When should I build a dashboard instead?
Sage Business Insights Dashboard covers a lot. A separate dashboard makes sense when you want one page that mixes Sage 100 with other sources, such as your web store, your CRM and a stock spreadsheet, or when people outside accounting need a view without a Sage license. My guide to a KPI dashboard lists which numbers are worth showing, and inventory reporting metrics covers the stock side. A dashboard that only reads from Sage 100 does not change your books, which makes it a low-risk first project. Related reading: Sage 100 alternatives.
How I'd approach this
I would not start with a tool. I would write down the five numbers the owner looks at every week, then find out how each one is produced today and how long it takes. For most small distributors and manufacturers, three of the five can come from Sage Intelligence or Business Insights with no new software. The other two usually need data from outside Sage 100, and that is where a small custom dashboard earns its cost. Start with the report that eats the most hours, fix that one, and measure the hours saved before touching the next. If you are weighing whether to keep Sage 100 at all, my custom ERP guide explains when a layer around your current system beats a replacement.
Questions people ask
Where are the reports in Sage 100?
Ready-made reports sit in the reports menu of each module. Custom reports appear once your partner deploys them, and Sage Intelligence and Business Insights have their own entry points. Ask your reseller which ones your license includes.
Can Sage 100 reports be exported to Excel?
Yes. Many reports can be sent to a file, and Sage Intelligence Reporting pulls Sage 100 data into Excel automatically. Visual Integrator can export data from ODBC-compliant sources on a schedule. A plain export is a snapshot, so prefer a refreshing workbook for weekly reports.
Does Sage 100 have a dashboard?
Sage lists Business Insights Dashboard, which shows real-time data as visual dashboards. For one page that mixes Sage 100 with other tools, see my KPI dashboard guide.
How do I connect Sage 100 to a CRM or web store?
Sage lists Sage CRM in the Essentials plan and Visual Integrator for custom import and export. Check the other tool's own page for a Sage 100 connection and ask what data moves and how often. My AI integration services page covers custom links.
Do I need a developer to change a Sage 100 report?
Not always. Sage Intelligence reports are built in Excel by people who know pivot tables. Crystal changes need Crystal skills, which usually means your Sage partner or a contractor.
Why do two Sage 100 reports show different totals?
Usually the filters or the date basis differ, for example posting date against invoice date, or posted against unposted entries. Run both with identical settings before assuming one is wrong.
More in this topic: B2B: ERP reporting and integrations (10 guides) →