Stock quantities multiplied by the average landed purchased price per product.

Current Value = sum of (QuantityOnHand * averagelandedpriceaftertransaction) for all products in the dim_stocktransactions table where

• QuantityOnHand = sum of current quantity per product across all warehouses
• averagelandedpriceaftertransaction is from the most recent transaction per product.

Stock On Hand Enquiry: https://au.unleashedsoftware.com/v2/Enquiry/StockOnHandEnquiry

or

Stock Movement Enquiry.

Value of stock allocated to Open sales orders, Assemblies or stock Transfers.

Current Value = sum of (AllocatedQuantity * latest averagelandedprice) for all products in the dim_stocktransactions  table where

• AllocatedQuantity = sum of quantity in dim_warehouseproductallocations per product across all warehouses
• averagelandedpriceaftertransaction is from the most recent transaction per product.

Product Allocations:

https://au.unleashedsoftware.com/v2/Enquiry/ProductAllocations

Value of stock available for sale (stock not linked to a sales order, assembly or transfer).

Current Value = sum of (AvailableQuantity * averagelandedpriceaftertransaction) for all products in the dim_stocktransactions  table where

• AvailableQuantity = QuantityOnHand - AllocatedQuantity per product across all warehouses
• averagelandedpriceaftertransaction is from the most recenttransaction per product

Could use Stock on Hand Enquiry and filter out allocated stock so that You only have available stock: https://au.unleashedsoftware.com/v2/Enquiry/StockOnHandEnquiry

Cost of Open purchase orders (stock on order but not yet received).

Value = sum of bclandedcost in dim_purchaseorderlines table where

• OrderStatus = 'Placed' in dim_purchaseorders

NOTE: Because this is a calculated value, using the status that will change over time, we are unable to calculate values for the past. Values will only be calculated and display from the date BI data was extracted.

View Purchases:

https://au.unleashedsoftware.com/v2/PurchaseOrder/List#status=Placed

The average number of days required to sell all current stock on hand.

Value = days_in_period * average_value_of_stock / cost_of_goods_sold where

• Days_in_period = 365 if the value of stock was > 0 one year ago. If the date of the earliest stock valuation is less than one year ago, days_in_period = days from the earliest date to the current date, with a minimum of 30 days
• average_value_of_stock = (opening value of stock at start of period + current closing value of stock) / 2
• cost_of_goods_sold = sum of abs(transvalue) for transactiontype = 'SalesInvoice'  - sum of transvalue for transactiontype = 'Credit' in dim_stocktransactions table for the period defined above

NOTE: This calculation is slightly different from the one used to display average days to sell on the classic dashboard.

Not available

How many times the Inventory is sold or used.

Value = cost_of_goods_sold / average_value_of_stock where

• cost_of_goods_sold = sum of abs(transvalue) for transactiontype = 'SalesInvoice'  - sum of transvalue for transactiontype = 'Credit' in dim_stocktransactions table for the period
• The period is the last 365 days if the value of stock was > 0 one year ago. If the date of the earliest stock valuation is less than one year ago, the period is from the earliest date to the current date, with a minimum of 30 days
• average_value_of_stock = (opening value of stock at start of period + current closing value of stock) / 2

NOTE: For the Stock Turns calculation, a product must have a non-zero average SOH value and non-zero COGS.

This calculation is slightly different from the one used to display stock turn on the classic dashboard.

Not available

Cost of goods sold calculation: Cost of inventory at the start of the period plus purchases minus cost of inventory at the end of the period.

Value = sum of abs(transvalue) for transactiontype = 'SalesInvoice'  - sum of transvalue for transactiontype = 'Credit' in dim_stocktransactions  table where

• transactiontype in ('SalesInvoice', 'Credit') and
• entrydate >= utc_datetime - '12 months' and
• entrydate <  utc_datetime

Stock Movement Enquiry:

https://au.unleashedsoftware.com/v2/Enquiry/StockMovementEnquiry

Total value of all stock adjustment transactions over the latest 365 days.

Value = sum of transvalue in dim_stocktransactions  table where

• transactiontype = 'StockAdjustment' and
• entrydate >= utc_datetime - '12 months' and
• entrydate <  utc_datetime and status = 'Completed'

Reports - Inventory: Transaction Enquiry:

https://au.unleashedsoftware.com/v2/Enquiry/TransactionEnquiry

Total count of stock adjustment transactions over the latest 365 days.

Value = count(distinct(transactionid)) in dim_stocktransactions  table where

• transactiontype = 'StockAdjustment' and
• entrydate >= utc_datetime - '12 months' and
• entrydate <  utc_datetime and
• status = 'Completed'

Reports - Inventory: Transaction Enquiry:

https://au.unleashedsoftware.com/v2/Enquiry/TransactionEnquiry

Backorders are a status of Sales Orders which are linked to purchase orders where products were out of stock at the time of the sale. Timeline chart will show summary per day linked to creation date of the SO.

Value = sum of bclandedcost in dim_purchaseorderlines table where

• OrderStatus = 'Backordered' in dim_salesorders

Sales report - Backorder Enquiry: https://au.unleashedsoftware.com/v2/BackorderEnquiry

Sales Revenue from completed invoices, including charges minus credits.

Value = sum of bclinetotal in fact_salesinvoice table where

• invoicestatus = ‘Completed’ and
• invoicedate >= period start date and
• invoicedate < period end datetime (to the current minute for any-to-date periods) and
• reporttype in ('Invoice', ‘Credit’, ‘CreditCharge’, ‘OrderCharge’)
• bclinetotal = round(InvoiceQuantity * bcunitprice, 2)

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and Include Credits unticked,  with Status = Completed. 

Also, run the Credit Enquiry report for the same period, Credit Status = Completed
https://au.unleashedsoftware.com/v2/Enquiry/CreditEnquiry

Note: This Invoice Enquiry report Total however excludes order charges which BI includes. Charges will however be displayed as a total at the bottom of the Invoice Enquiry report.

Free Credits are excluded from the Invoice Enquiry report therefore we cannot use the include credits option except if you are sure you do not have any free credits for the period.

Calculation: BI Sales Revenue = Total on the Invoice Enquiry report + Charges - Credits

Gross Profit from completed sales invoices = Sales Revenue minus Cost of Goods Sold.

Value = sum of bclinetotal - sum of bclinecost in fact_salesinvoice table where

• invoicestatus = ‘Completed’ and
• invoicedate >= period start date and
• invoicedate < period end datetime (to the current minute for any-to-date periods) and
• reporttype in ('Invoice', ‘Credit’, ‘CreditCharge’, ‘OrderCharge’)
• bclinetotal = round(InvoiceQuantity * bcunitprice, 2)
• bclinecost = (InvoiceQuantity - ShipmentQuantity) * latestaveragecost

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Add the hidden Profit field to the results. 

Note: This Invoice Enquiry report however excludes order charges which BI includes.

Gross Profit % from completed sales invoices = Gross Profit expressed as a % of Sales Revenue.

Value = 100 * sum of gross profit / sum of abs(bclinetotal) in fact_salesinvoice table where

• invoicestatus = ‘Completed’ and
• invoicedate >= period start date and
• invoicedate < period end datetime (to the current minute for any-to-date periods) and
• reporttype in ('Invoice', ‘Credit’, ‘CreditCharge’, ‘OrderCharge’)

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Note: This Invoice Enquiry report however excludes order charges which BI includes.

Count of sales orders from completed invoices.

Value = count of distinct ordernumber in fact_salesinvoice table where

• invoicestatus = ‘Completed’ and
• invoicedate >= period start date and
• invoicedate < period end datetime (to the current minute for any-to-date periods) and
• reporttype = 'Invoice'

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Use the item count displayed at the bottom center of the results page.

Average Sales Revenue per sales order from completed invoices, including charges minus credits.

Value = sum of bclinetotal / order count in fact_salesinvoice table where

• invoicestatus = ‘Completed’ and
• invoicedate >= period start date and
• invoicedate < period end datetime (to the current minute for any-to-date periods) and
• reporttype in ('Invoice', ‘Credit’, ‘CreditCharge’, ‘OrderCharge’)

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Export to Excel where you can calculate the average value per SO

The number of units sold/invoiced minus units credited and returned for the period selected. 

Value = sum of quantity in fact_salesinvoice table where

• invoicestatus = ‘Completed’ and
• invoicedate >= period start date and
• invoicedate < period end datetime (to the current minute for any-to-date periods) and
• reporttype in ('Invoice', ‘Credit’) and if ‘Credit’ the products must be returned to stock

Count of all current customers at the end of the period selected (excluding obsolete customers).

Value = count of IDs in dim_customers table where

• customer is not obsolete

View Customers

https://au.unleashedsoftware.com/v2/Customer/List#ObsoleteStatus=No

Count of new customers created in the period selected.

Value = count of IDs in dim_customers table where

• customer is not obsolete
• createdon >= period UTC start date

N/A

Count of customers placing sales orders in the period selected.

Value = count of distinct customerid in fact_salesinvoice table where

• invoicestatus = ‘Completed’ and
• invoicedate >= period start date and
• invoicedate < period end datetime (to the current minute for any-to-date periods) and
• reporttype = 'Invoice'

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Group by Customer and count them for the period.

Average Order count per active Customer from completed sales invoices.

Value = order count / active customer count in fact_salesinvoice table where

• invoicestatus = ‘Completed’ and
• invoicedate >= period start date and
• invoicedate < period end datetime (to the current minute for any-to-date periods) and
• reporttype in ('Invoice', ‘Credit’, ‘CreditCharge’, ‘OrderCharge’)

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Export to Excel and calculate average orders per Customer.

Average Revenue per active Customer from completed sales invoices.

Value = sum of bclinetotal / active customer count in fact_salesinvoice table where

• invoicestatus = ‘Completed’ and
• invoicedate >= period start date and
• invoicedate < period end datetime (to the current minute for any-to-date periods) and
• reporttype in ('Invoice', ‘Credit’, ‘CreditCharge’, ‘OrderCharge’)

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Export to Excel and calculate average Revenue per Customer.

Average Gross Profit per active Customer from completed sales invoices.

Value = (sum of bclinetotal - sum of bclinecost) / active customer count in fact_salesinvoice table where

• invoicestatus = ‘Completed’ and
• invoicedate >= period start date and
• invoicedate < period end datetime (to the current minute for any-to-date periods) and
• reporttype in ('Invoice', ‘Credit’, ‘CreditCharge’, ‘OrderCharge’)

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Add hidden Profit column to the output.

Export to Excel and calculate average Gross Profit per Customer.

Total value (including costs) and count of Purchase Orders created for the period selected.

Value = sum of bclandedcost

Count = count of distinct ordernumber

Average = Value/Count (BI Vision Only)

Excluding purchase orders with status = "Deleted"

Using the Purchase Enquiry report https://au.unleashedsoftware.com/v2/Enquiry/PurchaseEnquiry, select your period, Transaction date = Order Date and Order Status = All to extract all purchased on order dates.

Total value (including costs) and count of all Purchase Orders with Open status since BI was last refreshed.

Value = sum of bclandedcost

Count = count of distinct ordernumber

Average = Value / Count (BI Vision Only)

in fact_purchaseorder table where

• orderstatus IN (‘Costed’, ‘Parked’, ‘Placed’, ‘Receipted’, ‘Unapproved’)

Using View Purchased https://au.unleashedsoftware.com/v2/PurchaseOrder/List?orderStatus=Open with statue = Open to extract all open purchase orders.

Total value (including costs) and count of all Purchase Order transactions with a Completed status for the period selected.

Value = sum of bclandedcost

Count = count of distinct ordernumber

Average = Value / Count (BI Vision Only)

in fact_purchaseorder table where

• orderstatus = ‘Complete’ and
• completeddate >= period start date (UTC) and
• completeddate < period end datetime (UTC) (to the current minute for any-to-date periods)

Using the Purchase Enquiry report https://au.unleashedsoftware.com/v2/Enquiry/PurchaseEnquiry, select your period, Transaction date = Receipt Date and Order Status = Complete to extract all purchased on Receipted dates. (Note: This report will be updated in the future to allow you to select a completed date in the future.) 

The value of additional supplier costing for all completed Purchase Orders. This cost is calculated as a percentage of all completed orders for the selected period.

Costs = sum of bccosttotal

Ratio = 100 * sum of bccosttotal / sum of bcsubtotal

Average = Costs / count of distinct ordernumber (BI Vision Only)

in fact_purchaseorder table where

• orderstatus = ‘Complete’ and
• completeddate >= period start date (UTC) and
• completeddate < period end datetime (UTC) (to the current minute for any-to-date periods)

No report available - Could use View Purchases and add hidden column Costing Sub Total field to the view. 
https://au.unleashedsoftware.com/v2/PurchaseOrder/List#status=Complete

For the period selected, this is the average time between when a Purchase Order was placed and when it was receipted. We also calculate it against the required date to determine the average days late.

Value = sum of date_part('day', coalesce(dpo.receiveddate, dpo.completeddate) - dpo.orderdate) / count of distinct ordernumber

Avg Days late = sum(CASE WHEN purchaseorderlinenumber > 1 THEN 0 WHEN requireddate IS NULL THEN 0 ELSE date_part('day', receiveddate - requireddate) END) / count(DISTINCT CASE WHEN fpo.requireddate IS NULL THEN NULL ELSE fpo.ordernumber END)

in fact_purchaseorder table where

• orderstatus IN (‘Complete’, ‘Receipted’) and
• receiveddate >= period start date (UTC) and
• receiveddate < period end datetime (UTC) (to the current minute for any-to-date periods)

No report available

For the period selected, this is the value and count of stock returned to suppliers due to defects or other related reasons.

Value = sum of (dsrl.returnquantity * dsrl.returnprice) / dsr.exchangerate in dim_supplierreturnlines (dsrl)

Count = count of distinct supplierreturnnumber

Average = Value / Count (BI Vision Only)

in dim_supplierreturns (dsr) table where

• status = ‘Completed’ and
• returndate >= period start date and
• returndate < period end datetime (to the current minute for any-to-date periods)

View Supplier Returns on Status = Completed
https://au.unleashedsoftware.com/v2/SupplierReturn/List#status=Completed

The total cost of all components used plus additional supplier costs during assembly for the period selected.

Value = sum(coalesce(unitprice,0) * (coalesce(quantity,0) + coalesce(wastagequantity,0)) + coalesce(suppliercost,0))

from fact_assembly table where

• assemblytype  = 'Assembly' and
• assemblystatus = ‘Completed’ and
• assemblylevel IN ('Component','SupplierCost')
• assemblydate >= period start date and
• assemblydate < period end datetime (to the current minute for any-to-date periods)

Production Enquiry report, Value of Production Type = “Assembly In”: https://au.unleashedsoftware.com/v2/Production/ProductionEnquiry

The total number of assemblies completed for the period selected.

Count = count of distinct assemblyid

from fact_assembly table where

• assemblytype  = 'Assembly' and
• assemblystatus = ‘Completed’ and
• assemblylevel = 'Assembly'
• assemblydate >= period start date and
• assemblydate < period end datetime (to the current minute for any-to-date periods)

Production Enquiry report, number of Production Type = “Assembly In”: https://au.unleashedsoftware.com/v2/Production/ProductionEnquiry

The Total cost of all completed assemblies divided by the number of assemblies completed during the period selected.

Avg. Cost = Total Assembly Cost / count of distinct assemblyid

from fact_assembly table with conditions above.

Manual calculation using above 2 results

The average days between the date Assemble By and the Assembly Date indicating if assemblies were on average Early, On-time, or Late.

Value = sum(extract(day from (assemblydate - assembleby)))::numeric / count of distinct assemblyid

from fact_assembly table with conditions above.

Not available - manual calculation.

Cost of wastage as a % of the total cost of assemblies completed in the period selected.

Value = 100 * Cost of Wastage / Total Assembly Cost

from fact_assembly table with conditions above.

Not available - manual calculation.

The total number of units produced for the period selected.

Value = sum(coalesce(quantity,0)) 

from fact_assembly table with conditions above.

Production Enquiry report, Value of Production Type = “Assembly In” Qty column: https://au.unleashedsoftware.com/v2/Production/ProductionEnquiry

The total assembly cost divided by the number of units produced.

Value = Total Assembly Cost / Units produced

from fact_assembly table with conditions above.

Not available - manual calculation.

Total wastage captured when completing assemblies for the period selected.

Value = sum(coalesce(unitprice,0) * coalesce(wastagequantity,0))

from fact_assembly table where

• assemblytype  = 'Assembly' and
• assemblystatus = ‘Completed’ and
• assemblylevel = 'Component'
• assemblydate >= period start date and
• assemblydate < period end datetime (to the current minute for any-to-date periods)

Not available - manual calculation.

Sum of any additional supplier costs added to assemblies completed for the period selected.

Value = sum(coalesce(suppliercost,0))

from fact_assembly table where

• assemblytype  = 'Assembly' and
• assemblystatus = 'Completed' and
• assemblylevel = 'SupplierCost'
• assemblydate >= period start date and
• assemblydate < period end datetime (to the current minute for any-to-date periods)

The total cost of Completed Assemblies minus Additional Supplier Costs for the period selected.

Value = sum(coalesce(unitprice,0) * (coalesce(quantity,0) + coalesce(wastagequantity,0)))

from fact_assembly table where

• assemblytype  = 'Assembly' and
• assemblystatus = ‘Completed’ and
• assemblylevel = 'Component'
• assemblydate >= period start date and
• assemblydate < period end datetime (to the current minute for any-to-date periods)

Not available - manual calculation.