Exact Online Manufacturing query of the day January 30, 2016 from Pieter Hamans

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 :slight_smile:

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
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.