SAP Business One: Reporting to Excel, Module List and Price Lists
sap business one reporting excel
Also searched as
- sap business one modules list
- sap business one price list
- export sap business one to excel
- sap b1 query manager excel
Short answer
In SAP Business One you can export most lists and reports to Excel with the Microsoft Excel button, write your own SQL in Tools > Queries > Query Manager, or use Excel-based reporting tools offered with your version. The main modules are Administration, Financials, Sales A/R, Purchasing A/P, Business Partners, Banking, Inventory, Production, MRP, Service, HR and Reports. Price lists live under Inventory > Price Lists.
Applies to: SAP Business One 9.x and 10.x (SQL Server and HANA editions)
Get data into Excel and manage price lists
- 1Open any report or list (for example Sales A/R > Sales Order report) and click the Microsoft Excel icon in the toolbar to export what you see.
- 2For custom data use Tools > Queries > Query Manager (or Query Wizard), write SQL against the tables, run it, then export the result to Excel.
- 3Save queries as User Queries so colleagues can reuse them, and add a query to a menu or a Formatted Search for field lookups.
- 4For pivots, use the Excel report and interactive analysis feature available in your version, or Crystal Reports connected to the company database.
- 5For price lists: Inventory > Price Lists > Price Lists to create lists (stored in OPLN) and set a base list and factor.
- 6Maintain item prices in the Item Master Data (Inventory Data tab Price List) or through Inventory > Price Lists > Update Prices from other lists; item prices live in table ITM1.
- 7Assign a price list to each business partner in the Business Partner master (Payment Terms tab) and use Special Prices and Period/Volume Discounts for exceptions.
Choosing an Excel route
The toolbar export is the quickest and respects what the user sees, including permissions. Query Manager gives full SQL control but runs with the queries' permissions in the database layer, so restrict who can run it. Excel add-ins and BI tools can read through the Service Layer or ODBC, and read-only database users are common for reporting.
Avoid pointing live Excel connections at the production database for many users; refresh load can slow transactions. Use a reporting copy or scheduled exports.
Module list in the main menu
The menu contains Administration (setup, authorizations, system initialization), Financials (chart of accounts, journal entries, cost accounting), Opportunities, Sales - A/R, Purchasing - A/P, Business Partners, Banking, Inventory, Resources, Production, MRP, Service, Human Resources and Reports. Availability depends on licence and version.
Reports sit across modules; the Reports menu collects financial, sales, purchasing, inventory and MRP reports. Drill down by clicking the orange arrow to open the source document.
How price lists resolve
Price determination starts with the price list on the business partner, then applies special prices for the partner or group, and period and volume discounts according to the priority rule in Administration > Setup > Business Partners > Discount Groups. Price lists can be based on another list with a factor so one change flows to dependent lists.
SELECT T0.ItemCode, T0.ItemName, T1.PriceList, T1.Price
FROM OITM T0 INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode
WHERE T1.PriceList = 1
Try it here, free
SAP B1 Implementation Cost Calculator
Enter users, rates and effort days to see first-year and three-year SAP Business One cost, using your own partner quotes.
Common pitfalls
- !Giving every user Query Manager rights, which exposes tables outside their authorizations.
- !Exported Excel data going stale; people keep a copy and forget it is a snapshot.
- !Changing a base price list without checking dependent lists that use factors.
- !Forgetting to assign a price list to a new business partner, so default pricing applies.
How an ERP-grounded AI assistant handles this
Business users often want an Excel answer, not a query. A read-only assistant over Business One can answer a plain question, show the SQL used, and export the result, with the same permissions as the user. This is optional, and Business One's own query tools are enough for many teams.
Frequently asked questions
How do I export a report from SAP Business One to Excel?
Open the report and click the Microsoft Excel button in the toolbar. For data not on a standard report, write a query in Tools > Queries > Query Manager and export the result.
Which table holds price lists?
OPLN holds the price list header and ITM1 holds item prices per price list. OITM is the item master. Special prices are in OSPP and related tables.
What are the modules of SAP Business One?
Administration, Financials, Opportunities, Sales A/R, Purchasing A/P, Business Partners, Banking, Inventory, Resources, Production, MRP, Service, Human Resources and Reports, depending on licence and version.
Can Excel connect directly to Business One?
Yes, through ODBC to the database or through the Service Layer in newer versions. Use read-only access and a reporting copy to avoid slowing the production system.
Related
SAP Business One tables list: the core tables and how the names work
SAP Business One table names follow a pattern: O plus a short object name for header tables (OITM items, OCRD partners, ORDR sales orders), the same letters with a 1 for row tables (RDR1), and U_ prefixed columns or @ prefixed tables for user-defined objects. Read them for reports, but write only through the DI API or Service Layer.
How-toSAP Business One integration: Integration Framework (B1i), Service Layer, DI API and connectors
Business One offers four main ways to integrate: the Service Layer (REST/OData, the default for new work), the Integration Framework B1i (scenario based middleware), the DI API (COM/.NET, for legacy and add-ons) and third party or SaaS connectors. Pick the Service Layer for new web integrations and B1i when you want packaged scenarios and message flows.
How-toSAP Business One production planning: MRP, production orders, cost and variances
Planning in SAP Business One starts with demand (sales orders, forecasts), runs through the MRP wizard to produce recommendations, converts them into production and purchase orders, and ends with cost and variance review at close. The standard module is strong on BOM and MRP, light on scheduling and shop floor, where add-ons fill the gap.
How-toSAP Business One Financial Reports and Reporting Features
SAP Business One ships standard financial reports under Financials > Financial Reports, such as Trial Balance, Balance Sheet, Profit and Loss Statement and cash flow, plus aging reports under Business Partners. For custom needs it offers Query Manager (SQL), Crystal Reports layouts, Print Layout Designer for documents and dashboards or cockpits. Drill-down arrows trace every figure to its journal entry.
How-toHow to Create a Generic Inquiry in Acumatica
Generic Inquiries (GI) are Acumatica's no-code query builder for joining tables, adding filters, and exposing the result as an inquiry screen, dashboard data source, or API endpoint. Open System > Customization > Generic Inquiry, add your base table, define joins and conditions, then run and save. No SQL or customization project is required.
Error fixFix Odoo AccessError: You Are Not Allowed to Access Records
Odoo raises AccessError when a user's security groups do not grant a CRUD right on a model through ir.model.access.csv, or when an ir.rule record rule filters the specific record out for that user or company. Fix it by adding the user to the group named in the error, editing the model's access rights, or reviewing the record rule's domain under Settings > Technical > Security.
AI for ERPAI for SAP Business One, Without Sending Your Books to a Public API
Add AI to SAP Business One without sending customer data or financials to a public API. Service Layer and DI API integration, sized for an SMB budget.
AI for ERPAI on SAP Business One for Electronics Manufacturers: BOMs, Shortages and Traceability
Private AI on SAP Business One for electronics makers: multi-level BOM questions, component shortages, lot and serial traceability, RoHS and obsolescence checks.
Stuck on SAP Business One?
Talk to engineers who work inside SAP Business One every week, and who build private AI that answers these questions from your own ERP data.
Keep exploring
Free tools
- SAP B1 Implementation Cost CalculatorEnter users, rates and effort days to see first-year and three-year SAP Business One cost, using your own partner quotes.
- SAP B1 Approval Procedure TemplateFill in thresholds and approvers to generate a written SAP Business One approval procedure you can sign and configure from.
- SAP Business One Database Tables ReferenceSearchable lookup of the core SAP Business One tables, with the matching row table and what each one stores.
ERP fixes and how-tos
- SAP Business One Financial Reports and Reporting FeaturesStandard SAP Business One financial reports (trial balance, P&L, balance sheet, aging), plus Query Manager, Crystal layouts and dashboards and when to use each.
- SAP Business One tables list: the core tables and how the names workA practical list of core SAP Business One tables (OITM, OCRD, ORDR, OPOR, OWOR, OINM) with prefix conventions and safe query advice for reports.
- SAP Business One Query: Released Production Orders with Component ShortagesReady-to-run SAP Business One queries on OWOR, WOR1 and OITW to list released production orders with missing components, for SQL Server and HANA.
Runs with your ERP
- Spreadsheet Server with SAP Business One: live Excel reporting on inventory, sales and costHow insightsoftware Spreadsheet Server gives Excel live drill-down on SAP Business One inventory, sales and production cost data, with setup and pitfalls.
- Ecosire quality control and inspection with SAP Business One: CoA, NCR and CAPAHow Ecosire's quality add-on for SAP Business One handles inspections, certificates of analysis, NCR and CAPA ageing and supplier quality reporting.
- CompuTec WMS and SAP Business One: Mobile Warehouse TransactionsHow CompuTec WMS posts goods receipts, pick lists, transfers and production receipts into SAP Business One, with setup steps, pitfalls and a private AI layer.
AI on your ERP
- AI for SAP Business One, Without Sending Your Books to a Public APIAdd AI to SAP Business One without sending customer data or financials to a public API. Service Layer and DI API integration, sized for an SMB budget.
- AI for SAP Business One Purchasing, From Purchase Request to A/P InvoiceGround AI in SAP Business One purchasing: purchase requests, POs, goods receipts and A/P invoices via Service Layer, read-only, on-prem or private cloud.
Integrations
- How to add a mobile app to SAP Business OneBuild or choose a mobile app for SAP Business One: barcode scanning, inventory moves, pick lists, approvals, Service Layer access, offline sync and security.
- How to add AI agents to SAP Business OneHow to put AI agents on SAP Business One: Service Layer tool design, read-only first, draft documents for human approval, and an on-prem model option.
Software
- SAP Business OneSAP Business One explained for small manufacturers: modules, Service Layer and DI API, deployment, limits, alternatives and where AI helps on top of it.
- SAP Business ByDesignSAP Business ByDesign explained: cloud ERP modules for midsize firms, extensibility, integration options, limits, alternatives and where AI fits.