# SAP Business One: Reporting to Excel, Module List and Price Lists

> Source: https://netray.co/erp-help/sap-business-one-reporting-to-excel-modules-and-price-lists
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

## 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.

## Kind

how-to

## System

small-erp

## Erp

SAP Business One

## Module

Reporting / Modules / Price Lists

## Query

sap business one reporting excel

## Alternate Queries

- sap business one modules list
- sap business one price list
- export sap business one to excel
- sap b1 query manager excel

## Applies To

SAP Business One 9.x and 10.x (SQL Server and HANA editions)

## Steps

### Get data into Excel and manage price lists

**Items:**

- Open 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.
- For custom data use Tools > Queries > Query Manager (or Query Wizard), write SQL against the tables, run it, then export the result to Excel.
- Save queries as User Queries so colleagues can reuse them, and add a query to a menu or a Formatted Search for field lookups.
- For pivots, use the Excel report and interactive analysis feature available in your version, or Crystal Reports connected to the company database.
- For price lists: Inventory > Price Lists > Price Lists to create lists (stored in OPLN) and set a base list and factor.
- Maintain 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.
- Assign 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.

## Sections

### Choosing an Excel route

**Body:**

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

**Body:**

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

**Body:**

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.

**Code:**

- 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

## 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.

## Ai Angle

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.

## Faq

### How do I export a report from SAP Business One to Excel?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

sap business one reporting excel, sap business one modules list, sap business one price list, sap b1 export to excel, sap b1 query manager, OPLN ITM1 price list tables, sap business one special prices, sap b1 reports menu

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json