Skip to Content
0
Former Member
Dec 13, 2012 at 09:56 AM

Branchwise sales analysis Query

181 Views

Hi

My client wants the sales analysis branchwise report like same as sales analysis by customer , item or sales person.

which having colum like doc num, posting date, Customer Name, Sales Person, Doc Total, Gross Profit, GP%.

I write a simple query on invoice to get sales analysis branchwise from invoice

select

M.DocNum,M.DocDate,M.CardName,O.SlpName,M.DocTotal,M.GrosProfit, T2.DocNum,T2.DocDate

FROM OINV M LEFT OUTER JOIN INV1 L on L.DocEntry=M.DocEntry

left Outer JOIN ORIN T2 ON T2.DocEntry = L.TrgetEntry

left Outer JOIN DLN1 T3 on T3.DocEntry = T2.Docentry

LEFT outer join OSLP O on M.SlpCode = O.SlpCode

WHERE M.DocDate >= '[%0]' AND M.DocDate <= '[%1]'

and L.WhsCode='[%2]'

group by M.DocNum,M.DocDate,M.CardName,O.SlpName,M.DocTotal,M.GrosProfit, T2.DocNum,T2.DocDate

Union All


select Null, Null, Null, 'Total',SUM(M.DocTotal), NULL ,NULL,NULL from OINV M

LEFTOUTER JOIN INV1 L

left JOIN ORIN T2 ON T2.DocEntry = L.TrgetEntry

left JOIN DLN1 T3 on T3.DocEntry = T2.Docentry

on L.DocEntry=M.DocEntry

where M.DocDate >= '[%0]' AND M.DocDate <= '[%1]'

and L.WhsCode='[%2]'

But in that i m not able to show the effect of Credit Note on taking a total of doc total of invoice.

Is my Query correct?

The sum of doctotal of invoices of customer wise is shown less than sum of doctotal of invoices in branchwise beacuse of credit note.

Can u please help to get correct total