Sales by date and employee

[former_member239716]

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]')

[former_member252592]

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

[former_member239716]

Hi Jamil - thanks for your reply, but this gives the same data as I have already writen. It just sorts it without me having to do so in the query.

[former_member252592]

Hi Roy Bright

Standard Sales Analysis report in sales Reports can fulfill your requirement i hope did you check that out?

Regards

Jamil

[former_member239716]

I have Jamil - but as mentioned above, the directors want a Crystal Report version with graphs and so I assumed the easiest thing to do would be to recreate the query and load through CR

Regards

Roy

[former_member252592]

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

[former_member252592]

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

[Former Member]

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

[kothandaraman_nagarajan]

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

[former_member239716]

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.