Friday, November 16, 2018

Saved Search to Display Total Quantity Sold against Items per Transactions in one Line


Create a search which pulls a specific set of transactions (e.g. sales orders, invoices, cash sales) and present the total sum quantity of item A and the total sum quantity of item B sold against each sales order as a single line item record with a field for each item.

eg: SO Id, Sales Person, Total Red Widgets, Total Blue Widgets

Create a saved transaction search and set the following on the results tab:

1. Navigate to Reports>Saved Searches>All Saved Searches>New
2. Click Transactions
3. Under the Criteria tab, set type to certain transaction type (e.g. Sales Order, Invoice, Cash Sale)
4. On the Results tab, set the following:

Field : Number
Summary Type: Group

Formula (Numeric) : case when {} = XXX then {quantity} end
Summary Type: Sum

Note: XXX is the internal ID of the various items. Create as many Formula (Numeric) fields for each item you are selling.

No comments:

Post a Comment