• From: Greece
  • Registered: 05/11/2012
  • Posts: 7

Topic: Report of sales orders by salesman

hi, i am new to frontaccounting and i must say it is awesome and beats down several free and commercial software.I try to learn it and the wiki is excellent for reference.I have a problem only with the reporting.I see that we can have a report with the sales that have being paid by the customers and the ammount of the commition the salesman deserves.Is there any report where we can see the sales orders the salesman have commited?I have found a way in the audit trail but there is no amount only the sales order submitted and no amount,can someone help me to display also the amount?

  • From: Netherlands, Amsterdam
  • Registered: 01/09/2013
  • Posts: 16

Re: Report of sales orders by salesman

Did you try Sales / Inquiries and Reports / Customer and Sales Reports / Salesman Listing (under Reports For Class: Customer)?

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Report of sales orders by salesman

Mockup of desired report would be useful along with name of report that nearly fits the bill.

apmuthu's Website

  • From: Greece
  • Registered: 05/11/2012
  • Posts: 7

Re: Report of sales orders by salesman

Well none of the above is ok.Being trying to figure out what tables is needed i cant figure out exactly nor find any documantation on the sys_types table and what the describe.I have found that the id=32 is the sales order which in my case is what i need.Thens in sales_order table i can use the trans_type which is 32 to the sales order and from there debtor_no is the customer number and total is the amount of the given sales order money.Then using debtor_no and join it with table cust_branch  debtor_no and use also salesman id to identify the salesman.

This is a quick look of table.I will try to make some sql querys for the above.Any help will be welcome especially for creating a new report.Will keep you informed.

5 Reply by special (edited by special 03/07/2013 03:59:49 pm)

  • From: Greece
  • Registered: 05/11/2012
  • Posts: 7

Re: Report of sales orders by salesman

trying my luck in sql i have created this query:
select 0_cust_branch.*,0_sales_orders.*, SUM(total) as "total sales"
        from 0_sales_orders
        inner join 0_cust_branch on 0_sales_orders.debtor_no = 0_cust_branch.debtor_no
        where trans_type=30
        group by salesman

This will join the 2 tables cust_branch and sales_order then match debtor_no and sum the sales orders by salesman.
Tester and opinions are always welcomed :-)

  • From: Greece
  • Registered: 05/11/2012
  • Posts: 7

Re: Report of sales orders by salesman

A new update with only info of what we want and by editing the 2 date inputs you can get the report with the dates you want

select 0_cust_branch.debtor_no,0_cust_branch.salesman,0_sales_orders.debtor_no,0_sales_orders.trans_type,0_sales_orders.total,0_sales_orders.ord_date, SUM(total) as "total sales"
        from 0_sales_orders
        inner join 0_cust_branch on 0_sales_orders.debtor_no = 0_cust_branch.debtor_no
        where trans_type=30 and ord_date >= '2013-01-01' and ord_date <= '2013-03-28'
        group by salesman