Per article and assortment code in Exact Online the revenues per month

Question

I want to determine per article and assortment code in Exact Online the total quantity of items shipped and the division currency amount. Exact Online only offers an overview per account. How can I retrieve this list with Invantive Control?

Answer 1

This is a frequent occurring question. You will need to use an Invantive SQL statement, which sums the quantities and amounts from the sales journal in the general ledger of Exact Online. It would resemble something like this:

select tle.division
,      tle.financialyear
,      tle.financialperiod
,      tle.itemcode
,      tle.item
,      itm.class_01
,      itm.class_10
,      sum(tle.quantity) quantity
,      sum(tle.amountdc) amountdc
from   transactionlines tle
join   exactonlinerest..items itm
on     itm.id = tle.item
where  tle.journalcode = '70'
and    tle.itemcode is not null
and    tle.financialyear = 2017
and    tle.financialperiod = 3
group 
by     tle.division
,      tle.financialyear
,      tle.financialperiod
,      tle.itemcode
,      tle.item
,      itm.class_01
,      itm.class_10
order
by     tle.division
,      tle.financialyear
,      tle.financialperiod