cancel
Showing results for 
Search instead for 
Did you mean: 

Sales Comparison By BP

Former Member
0 Kudos

Hello All -

We need help to devise a query that compares 2009 and 2010 sales for each BP.

Columns of info we would like are Acct #...Foreign Name...Sales Rep...Company...Bill To Address...Bill To City...Bill To Zip...Bill To Phone...Contact...Jan 09 Sales...Feb 09 Sales...etc. for all 12 months in 2009 AND all 12 months in 2010. Not sure if this is too long to display.

Also, would need to include returns / credit memos.

Thanks very much!

Mike

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Mike,

This one would actually be quite simple in XL Reporter, if you are comfortable with the tool?

Former Member
0 Kudos

Hi Julie -

Unfortunately, we do not know how to use XL Reporter.

Mike

JesperB1
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Mike,

While it is possible to write that as a query it will probably look like a novel.

Here is a link to the information page on XL reporter. It contains all help files, training material, recorded sessions and some sample reports.

[XL Reporter|https://websmp110.sap-ag.de/~form/sapnet?_SHORTKEY=01100035870000706509&_SCENARIO=01100035870000000183&_ADDINC=011000358700001192682007E&_OBJECT=011000358700001172792006]

I hope it will help with other reports as well.

Jesper

Former Member
0 Kudos

HI Jesper -

Thanks for the pointer. How about if we shorten the query to the following:

Acct #...Foreign Name...Sales Rep...Company...Bill To Address...Bill To City...Bill To Zip...Bill To Phone...Contact...Jan 09 Sales...Feb 09 Sales...etc. for all 12 months in 2009 --- FORGET 2010, we can do a separate query for that then combine later.

Will that make it easier? We actually have a similar query now but instead of Monthly sales for 2009 it shows total sales.

Thanks,

Mike

Former Member
0 Kudos

Hi Mike,

As the other experts point out, this is not a suitable report by query. I have tried to achieve as such:


SELECT P.[Acct#],P.[ForeignName],P.[Company],P.[BillToStreet],P.[BillToCity],
P.[BillToZip],P.[BillToState],
 [1] as [Jan],
 [2] as [Feb],
 [3] as [Mar],
 [4] as [Apr],
 [5] as [May],
 [6] as [Jun],
 [7] as [Jul],
 [8] as [Aug],
 [9] as [Sep],
 [10] as [Oct],
 [11] as [Nov],
 [12] as [Dec]
FROM (SELECT T0.CardCode as [Acct#],T1.CardFName as [ForeignName],
T1.CardName as [Company], T1.address AS [BillToStreet],T1.City AS [BillToCity],
T1.ZipCode AS [BillToZip],T1.State1 AS [BillToState],
T0.DocTotal as [DocTotal], 
MONTH(T0.docdate) as [month] 
FROM dbo.oinv T0
INNER JOIN dbo.OCRD T1 ON T1.CardCode = T0.CardCode
WHERE Year(T0.docdate)=2009
)  S
  PIVOT  (SUM(DocTotal) FOR [month] IN 
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
ORDER BY P.[Acct#]

It might be too difficult to include everything you want or even not possible at all.

Thanks,

Gordon

Former Member
0 Kudos

Gordon -

Actually, this is very close to what we are looking for.

However, would need Sales Rep column -- tried to do ourselves but kept getting error.

Also, would like to see entire account list -- even those that did not have purchases in 2009...this will allow us to keep constant and compare one year to the other.

Can this be done?

Mike

Former Member
0 Kudos

Updated:


SELECT P.[Acct#],P.[ForeignName],P.[SalesRep],P.[Company],P.[BillToStreet],P.[BillToCity],
P.[BillToZip],P.[BillToState],
 [1] as [Jan],
 [2] as [Feb],
 [3] as [Mar],
 [4] as [Apr],
 [5] as [May],
 [6] as [Jun],
 [7] as [Jul],
 [8] as [Aug],
 [9] as [Sep],
 [10] as [Oct],
 [11] as [Nov],
 [12] as [Dec]
FROM (SELECT T1.CardCode as [Acct#],T1.CardFName as [ForeignName],T2.SlpName as [SalesRep],
T1.CardName as [Company], T1.address AS [BillToStreet],T1.City AS [BillToCity],
T1.ZipCode AS [BillToZip],T1.State1 AS [BillToState],
T0.DocTotal as [DocTotal], 
MONTH(T0.docdate) as [month] 
FROM dbo.OCRD T1
LEFT JOIN dbo.OINV T0 ON T1.CardCode = T0.CardCode
LEFT JOIN dbo.OSLP T2 ON T2.SlpCode = T1.SlpCode
WHERE Year(T0.docdate)=2009 AND T1.CardType = 'C'
)  S
  PIVOT  (SUM(DocTotal) FOR [month] IN 
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
ORDER BY P.[Acct#]

Former Member
0 Kudos

Wonderful -- but is there any way to show all accounts...even those that have no transactions in 2009?

Thanks,

Mike

Former Member
0 Kudos

Here u r.


SELECT T.CardCode 'Acct#',T.CardFName,U.SlpName 'Sales Rep',T.CardName 'Company', T.Address,T.City,
T.ZipCode,T.State1,
 [1] as [Jan],
 [2] as [Feb],
 [3] as [Mar],
 [4] as [Apr],
 [5] as [May],
 [6] as [Jun],
 [7] as [Jul],
 [8] as [Aug],
 [9] as [Sep],
 [10] as [Oct],
 [11] as [Nov],
 [12] as [Dec]
FROM dbo.OCRD T
LEFT JOIN (SELECT T1.CardCode as [Acct#],
T0.DocTotal as [DocTotal], 
MONTH(T0.docdate) as [month] 
FROM dbo.OCRD T1
LEFT JOIN dbo.OINV T0 ON T1.CardCode = T0.CardCode
WHERE Year(T0.docdate)=2009 AND T1.CardType = 'C'
)  S
  PIVOT  (SUM(DocTotal) FOR [month] IN 
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
ON P.[Acct#] = T.CardCode
INNER JOIN dbo.OSLP U ON U.SlpCode = T.SlpCode
WHERE T.CardType = 'C'
ORDER BY T.CardCode

Former Member
0 Kudos

Wondereful! Thank you!

This includes freight?

Does it include returns?

Thanks,

Mike

Former Member
0 Kudos

It includes Freights. However, it would be extremely complicated to include credit memo. Better using another query to just replace OINV with ORIN

Former Member
0 Kudos

Gordon -

Would like to send you a direct message - can you accept me as a Friend / Connection?

Thanks,

Mike

Former Member
0 Kudos

You can check my profile to find my contact info.

Answers (0)