I am trying to generate a query that will give me the same information as is listed in the QB report income by customer filtered by Sales Rep.  Not the sales rep that sold it but who is assigned now.

Basically I am trying to use it like a territory and we pay commission to the rep based on the increased income from this year over last year for the accounts that he is assigned.  So if the reps customers generated 10,000 in income in Sept last year and 15,000 in income this year they will be paid commission on the difference of 15000 - 10000 = 5000 in income. But I can't pull a report that will tell me how much those customers had in income for last Sept because those customers were spread across 3 reps last year.

Could you suggest a way to like the customer table filtered by current rep to the income by customer report?

Ok try something like this:

SELECT Customer.SalesRepRefFullName as "Rep", ReceivePaymentLine.TxnDate as "Date",
Invoice.CustomerRefFullName as "Job",ReceivePaymentLine.AppliedToTxnRefNumber as "Inv No.",
Invoice.SubTotal as "Orig. Amt.", ReceivePaymentLine.AppliedToTxnDiscountAmount as "Discount",
ReceivePaymentLine.AppliedToTxnAmount as "Payment" FROM ReceivePaymentLine, Invoice, Customer
where ReceivePaymentLine.AppliedToTxnTxnID= Invoice.TxnID
and Invoice.CustomerRefListID = Customer.ListID
and (ReceivePaymentLine.TxnDate >= {d '2008-01-01'} and ReceivePaymentLine.TxnDate < {d '2008-07-01'})
Order by Customer.SalesRepRefFullName, Invoice.CustomerRefFullName

Tom,

Thanks for that. That does give me sales for those customers but what I was looking for was income/GP like the income by customer summary report. I don't need it by invoice the summary is fine.

Thanks

Sorry, there's no real way to link in the COGS and Bills against the Customer to this report. My first example was for purely commissions and not income. You would need to do multiple queries on both the Sales and Bill tables.

See also: YTD Column not show in Sp_reports IncomeByCustomerSummary