# Sales by date and employee

Hi guys - i have the following query so we can show sales by date range and employee - is there a way that I can group these together so that it groups all of the sales by employee showing one total only?

SELECT T0.CardCode, T0.CardName, T0.DocDate, T1.ItemCode, T1.Dscription, T1.Quantity, T1.LineTotal, T3.SlpName

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode]

WHERE (T0.DocDate>='[%0]' and T0.DocDate<='[%1]')

Select '','',Null, '','Total',Null, Sum(T1.LineTotal)

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

WHERE (T0.DocDate>='[%0]' and T0.DocDate<='[%1]')

10|10000 characters needed characters exceeded

### Related questions

• Posted on Jul 21, 2016 at 11:08 PM

Hi,

Have you tried with standard sales analysis report?

1. Try with PIVOT function to get total for individual sales emo total

OR

2. You have to sum for each employee in above query

Thanks

10|10000 characters needed characters exceeded
• This of course I can do Nagarajan, my friend but my seniors want it to be run in Crystal Reports with graphs and such and I was hoping to be able to subtotal beforehand.

• Former Member
Posted on Jul 22, 2016 at 03:29 AM

Hi,

You mean you need subtotal by Sales Employee, in this case create query with temp tables and insert the values and insert the subtotal by sales employee and bring the final result with all values

Regards,

Kiran

10|10000 characters needed characters exceeded
• Former Member
Posted on Jul 22, 2016 at 08:31 AM

Hi Roy Bright Try this

SELECT T0.CardCode, T0.CardName, T0.DocDate, T1.ItemCode, T1.Dscription, sum (T1.Quantity) as'Quantity'

, sum (T1.LineTotal) as 'Amount', T3.SlpName

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode]

WHERE (T0.DocDate>='[%0]' and T0.DocDate<='[%1]')

Group by T0.CardCode, T0.CardName, T0.DocDate, T1.ItemCode, T1.Dscription, T3.SlpName

Have a good day

Jamil

10|10000 characters needed characters exceeded
• Former Member Roy Bright

Hi Roy Bright if u want the crystal report then use same query as i posted and made a CR,

by CR tools you can achieve your requirements, for CR details Post your question on SAP Crystal Report Forum

Regards

Jamil