Last query resulted in two nice new questions by Mr. Pieter Hamans:
- A list of (approved) labor hours of a certain week, by employee by shop order.
- A list of machine hours in a certain period by work center (machine) by shop order. With subtotals.
In fact both questions have an almost identical answer. Although man and machine are totally different, but need some rest after periods and maintenance after intensive production work and the answer on such queries will tell you whether it is already overdue 
Of course, you can use the add-ins on Excel or Word or the Query Tool to retrieve this information with formulas or SQL, but you can also use the free Online SQL Editor.
Please note that the ability to perform joins on the free Online SQL Editor may require a subscription on our extended Exact Online SQL driver in the future.
SQL Manufacturing Query
The query is:
select sor.shopordernumber
, sor.description shoporderdescription
, mtn.date
, mtn.activity setup_or_run
, mtn.status hour_status
, mtn.laborhours man_hours
, emp.birthname man_name
, mtn.hours wcr_hours
, wcr.code wcr_code
, wcr.description wcr_description
from MfgTimeTransactions mtn
join Employees emp
on emp.id = mtn.employee
join Workcenters wcr
on wcr.id = mtn.workcenter
join shoporders sor
on sor.id = mtn.shoporder
order
by sor.shopordernumber
, mtn.date
, mtn.activity
Invantive Query Tool Results
To access the data you will need to choose either our REST-based provider for Exact Online or on beta-sites the upcoming combined XML&REST provider for Exact Online.
Of course I lack a lot of knowledge on actual manufacturing processes, but not hindered by this inconvenient lack of knowledge I think the man hours can be found in the column “man hours” with the “hour status” indicating the approval status. The machine hours are in “wcr hours”. Please let me know when this is a wrong assumption!
Online SQL Editor Results
The query on the online version is identical across all platforms, so even from your iPad or Mac you can retrieve the information in the following format. In this case, we have summed all totals not using the GUI as in the previous example, but using the SQL group function ‘sum’:
Please note that the Online SQL Editor currently only uses Invantive’s REST-based provider for Exact Online. Even beta-sites can not use the combined REST and XML provider in the online SQL Editor
What’s next?
When you also need a custom report or query, please add them as a comment and challenge us to get your cloud-based data delivered to you. I am looking forward to learn from you what the essential reports are.