# SyteLine item usage report: get item consumption history

> Source: https://netray.co/erp-help/syteline-item-usage-report-history-sql-excel
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

## Short answer

SyteLine has no single report named item usage that fits every need. The reliable approach is to total issue and shipment transactions from the inventory transaction history (matltran), by item and month, either through the standard transaction reports or with a SQL pull you pivot in Excel, and then use the result to set reorder points and safety stock.

## Kind

how-to

## System

syteline

## Erp

Infor SyteLine (CloudSuite Industrial)

## Module

Inventory / Planning

## Query

syteline item usage report

## Alternate Queries

- syteline item usage history
- syteline item consumption by month
- syteline usage for reorder point

## Applies To

Infor SyteLine 8.x to CloudSuite Industrial. Transaction type codes and available reports differ by version and configuration.

## Steps

### Build an item usage report

**Items:**

- Decide what counts as usage: job material issues, customer shipments, or both. Exclude transfers, adjustments and receipts.
- Check the types in matltran in your database before filtering: SELECT DISTINCT trans_type FROM matltran.
- Pull monthly totals by item with the SQL below, using a date range of at least 12 months.
- Paste the result into Excel and build a pivot by item and month, or send it to a reporting tool.
- Compute average monthly usage and variability, and compare with the on-hand quantity and the lead time.
- Use the result to review reorder points, safety stock and lot sizes on the item or item-warehouse record.
- Reconcile one item to the Inventory Transactions form to prove the query before you trust it.
- Schedule the pull monthly, or expose it as a custom view or SSRS report for planners.

## Sections

### Where usage lives

**Body:**

Every inventory movement writes a row to matltran with item, warehouse, date, quantity and a transaction type. Usage is the subset of those rows that represent consumption: material issued to jobs, shipments to customers and similar.

Because type codes differ by configuration, always inspect your own values and agree the definition with planning before you publish a number.

**Code:**

SELECT m.item,
       DATEFROMPARTS(YEAR(m.trans_date), MONTH(m.trans_date), 1) AS month_start,
       SUM(ABS(m.qty)) AS qty_used
FROM matltran m
WHERE m.trans_date >= DATEADD(month, -12, GETDATE())
  AND m.trans_type IN ('I', 'S')  -- confirm the codes in your database
GROUP BY m.item, DATEFROMPARTS(YEAR(m.trans_date), MONTH(m.trans_date), 1)
ORDER BY m.item, month_start;

### Using the standard reports

**Body:**

- The inventory and material reports in the application can list transactions by item and date range, and the Excel add-in can export a grid directly. For a quick one-item check that is enough.
- For a portfolio of thousands of items, the SQL pull is faster and repeatable.

### From usage to reorder points

**Body:**

A reorder point is demand during lead time plus safety stock. With monthly usage you can estimate average daily demand and its variation, then set the point per item. Do it first for the A items that drive most value.

If MRP is the planning method, usage history informs forecast and safety stock, not manual reorder points. Review planning parameters rather than layering both.

### Quality of the numbers

**Body:**

- One-off large issues, scrap and corrections distort averages. Look at the distribution, trim outliers, and mark seasonal items.
- Check that unit of measure is consistent. Item usage in the stocking UM is safest.

## Pitfalls

- Including receipts and adjustments in usage and doubling the number.
- Assuming transaction type codes without checking your database.
- Using calendar averages for seasonal or lumpy items.
- Mixing units of measure in one total.
- Ignoring multiple warehouses or sites and reporting the wrong scope.

## Ai Angle

A question like 'average monthly usage of our top 200 items over 12 months, with items below their reorder point' is a read-only query. SyteRay turns it into the right SQL for your schema, shows it, and returns a table you can export to Excel, without sending data out of your network.

## Faq

### Is there a standard item usage report in SyteLine?

**Answer:** Not a single universal one. Usage is derived from inventory transactions in matltran, either through the standard transaction reports or a custom query or report.

### Which table holds inventory transactions?

**Answer:** matltran holds the material transaction history, with item, warehouse, date, quantity and transaction type.

### How many months of history should I use?

**Answer:** At least 12 months to capture seasonality, with 24 for seasonal or lumpy items. Trim obvious one-off events before computing an average.

### Can I use this to tune MRP?

**Answer:** Yes. Usage history supports safety stock, lot sizes and forecast accuracy checks, which are the planning parameters that most affect MRP output.

## Search terms

syteline item usage report, syteline matltran, syteline inventory transactions, syteline reorder point, syteline safety stock, syteline usage history sql

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