Sales analysis report - in query form?

[Former Member]

Good afternoon. Looking for a bit if help/advice.

I'm not a complete novice to SAP but what I need is confusing me slightly. I understand the sales analysis report uses many different table as part of its calculations. A company I've recently began working for, who has had SAP for a while, has used sales analysis reports alongside some bespoke reports that are reporting different data.

Here is their report:

SELECT T0.[DocNum] AS 'Invoice No.',  CONVERT(VARCHAR(10),T0.[DocDate], 103) AS 'Invoice Date', T0.[NumAtCard] AS 'Cust Order No.', T4.[ItemCode], T5.[U_MSKU] AS 'Manuf SKU', T4.[Dscription] AS 'Description', T6.[ItmsGrpNam] AS 'Item Group', T4.[Quantity], T4.[Price] AS 'Net Unit Price', T4.[LineTotal] AS 'Net/Line Total', T0.[U_ClientNam] AS 'Client Name', T2.[Name] AS 'Division', T1.[Name] AS 'Business Unit', T3.[StreetS] AS 'Ship-to Street', T3.[CityS] AS 'Ship-to City', T3.[ZipCodeS] AS 'Ship-to Postcode'

FROM OINV T0

LEFT JOIN [dbo].[@BUSINESSUNITS]  T1 ON T0.U_Bizunits = T1.Code

LEFT JOIN [dbo].[@DIVISIONS]  T2 ON T0.U_Division = T2.Code

INNER JOIN INV12 T3 ON T0.DocEntry = T3.DocEntry

INNER JOIN INV1 T4 ON T0.DocEntry = T4.DocEntry

INNER JOIN OITM T5 ON T4.ItemCode = T5.ItemCode

INNER JOIN OITB T6 ON T5.ItmsGrpCod = T6.ItmsGrpCod

WHERE T0.[DocDate] BETWEEN [%0] AND [%1]  AND T0.[CardCode] = [%2]

It just doesn't give the same totals as the sales analysis report but I can't reproduce the sales analysis report. How would I go about getting the right figures? Because what we ideally need is to filter the sales analysis results by some of our custom fields:

[U_ClientNam], [Name] etc.

How would I go about this? Can I extract parts of the sales analysis report somehow and run it as a query I can manipulate?

Thanks.

[Former Member · Accepted solution]

Hi,

You may try compare the result between this two queries first:

SELECT COUNT(T0.[DocNum])

FROM OINV T0

JOIN [dbo].[@BUSINESSUNITS]  T1 ON T0.U_Bizunits = T1.Code

JOIN [dbo].[@DIVISIONS]  T2 ON T0.U_Division = T2.Code

WHERE T0.[DocDate] BETWEEN [%0] AND [%1]  AND T0.[CardCode] = [%2]

and

SELECT COUNT(T0.[DocNum])

FROM OINV T0

WHERE T0.[DocDate] BETWEEN [%0] AND [%1]  AND T0.[CardCode] = [%2]

Thanks,

Gordon

[Former Member · Accepted solution]

First query: 5058 Invoices.

Second Query: 5138 invoices.

So plenty of invoices missed out there... (yet less invoices seems to raise the total?).

[Former Member · Accepted solution]

All those 80 invoices will not be in your query results.

[Former Member · Accepted solution]

I just don't understand how omitting the 80 invoices increases the total? if it was lower I could just subtract the difference and call it "unaccounted for" but as it's higher.. it's like I'm giving out totals by division that are somehow greater than overall spend by a customer. It seems to make no sense. How could this happen, do you know?

[Former Member · Accepted solution]

You must have duplicated lines in the result.

Try this to check the total:

SELECT T0.[DocNum] AS 'Invoice No.',  CONVERT(VARCHAR(10),T0.[DocDate], 103) AS 'Invoice Date', T0.[NumAtCard] AS 'Cust Order No.', T4.[ItemCode], T4.[Dscription] AS 'Description', T6.[ItmsGrpNam] AS 'Item Group', T4.[Quantity], T4.[Price] AS 'Net Unit Price', T4.[LineTotal] AS 'Net/Line Total', T2.[Name] AS 'Division', T1.[Name] AS 'Business Unit'

FROM OINV T0

LEFT JOIN [dbo].[@BUSINESSUNITS]  T1 ON T0.U_Bizunits = T1.Code

LEFT JOIN [dbo].[@DIVISIONS]  T2 ON T0.U_Division = T2.Code

INNER JOIN INV1 T4 ON T0.DocEntry = T4.DocEntry

INNER JOIN OITM T5 ON T4.ItemCode = T5.ItemCode

INNER JOIN OITB T6 ON T5.ItmsGrpCod = T6.ItmsGrpCod

WHERE T0.[DocDate] BETWEEN [%0] AND [%1]  AND T0.[CardCode] = [%2]

[Former Member · Accepted solution]

Okay this result was a good 10% higher still than all previous results.

[Former Member · Accepted solution]

This was basically there. Not sure if left join is same as left outer join? (I think it is) but anyway, the reason for some of the amounts becoming a bit strange seems to be because we had some large rebates put through as credit notes.. which I believe is the right document but it was very much messing up our sales analysis report by knocking an entire month down.

Thank you everyone for your help. Problem now solved!

[kothandaraman_nagarajan]

Hi,

1. You need to include AR credit memo table (ORIN and RIN1) in above query

2. Try this query:

SELECT [CustName] as CustName, isnull([1],0) as Jan, isnull([2],0) as Feb, [3] as Mar, isnull([4],0) as Apr, isnull([5],0) as May, isnull([6],0) as june, isnull([7],0) as July, isnull([8],0) as Aug, isnull([9],0) as Sept, isnull([10],0) as Oct, isnull([11],0) as Nov, isnull([12],0) as Dec

from

(SELECT T0.[CardName] as CustName, sum(T1.[LineTotal]) as Total, month(T0.[DocDate]) as month FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE year(T0.[DocDate]) = 2012 and T0.[CardCode] = 'cvet01' GROUP BY T0.[CardName],T0.[DocDate]

union all

(SELECT T0.[CardName] as CustName, -sum(T1.[LineTotal]) as Total, month(T0.[DocDate]) as month FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry WHERE year(T0.[DocDate]) = 2012 and T0.[CardCode] = 'cvet01' GROUP BY T0.[CardName],T0.[DocDate])) S

Pivot

(sum(S.total) For Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan K.

Thank you, your query really helped me.

The one thing I can find, which may be a question you can answer, is that the results are closer but still different to the totals given by the sales analysis month by month. I really need those totals to match up to sales analysis.. are there any other tables that affect the outcomes? Or does sales analysis

Basically, is a nutshell, is it possible to get results from a query that matches up to sales analysis? Is sales analysis even right? Because we need to report to one of our customers on these figures and things don't seem to be matching up.

Thanks Nagarajan.

[kothandaraman_nagarajan]

Hi,

Please check this thread, let me know if total is not correct.

Not sure about UDT and its data.

Thanks & Regards,

Nagarajan

[Former Member]

Hi, not sure if you meant to link to a thread? Based on the query you have given on this thread no totals appear to match however it is much closer than the simple query.

SELECT Div, BusUnit, isnull([1],0) as Jan, isnull([2],0) as Feb, [3] as Mar, isnull([4],0) as Apr, isnull([5],0) as May, isnull([6],0) as june, isnull([7],0) as July, isnull([8],0) as Aug, isnull([9],0) as Sept, isnull([10],0) as Oct, isnull([11],0) as Nov, isnull([12],0) as Dec

from

(SELECT T1.[LineTotal] as Total, month(T0.[DocDate])as month, T8.[Name] AS BusUnit, T9.[Name] AS Div FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN [dbo].[@BUSINESSUNITS]  T8 ON T0.U_Bizunits = T8.Code INNER JOIN [dbo].[@DIVISIONS]  T9 ON T0.U_Division = T9.Code

WHERE year(T0.[DocDate]) = 2013 and T0.[CardCode] = 'NOR0001'

union all

(SELECT -T1.[LineTotal] as Total, month(T0.[DocDate])as month, T8.[Name] AS BusUnit, T9.[Name] AS Div FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN [dbo].[@BUSINESSUNITS]  T8 ON T0.U_Bizunits = T8.Code INNER JOIN [dbo].[@DIVISIONS]  T9 ON T0.U_Division = T9.Code

WHERE year(T0.[DocDate]) = 2013 and T0.[CardCode] = 'NOR0001')) S

Pivot

(sum(S.Total) For Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Is what I've just used. Still well off the mark though.

[kothandaraman_nagarajan]

Hi,

Not sure about [dbo].[@BUSINESSUNITS] & [dbo].[@DIVISIONS] there UDT will affect query result. Because there is no such table used in system sales analysis report.

As mentioned  by me on attached thread, please take closer amount (query result) with system amount. From this find which transactions are not included in above query.

Thanks & Regards,

Nagarajan

[Former Member]

Okay I've done as you've said and without Div/BusUnit the results are almost identical to the sales analysis report.. close enough to offer to customers at least. There is a big difference between   including BusUnits and Division and not including which is in the month of may. Tens of thousands of pounds difference, although there are variances for every month.

I really don't understand how adding BusUnits and division changes the report to make the total  higher? I could imagine it could count records out.. if they were blank, but an "increase" in invoiced amount?

So while the totals are almost the same now I still essentially need business unit and division on that report..... if it's possible.

Thankyou for your help so far I can see that I'm almost there.

[kothandaraman_nagarajan]

Hi,

Without checking relationship between tables, (UDT & OINV) I am not able to advice on this part.

Thanks & Regards,

Nagarajan

[Former Member]

Basically it's a simple UDF on Marketing Documents via a code that links to a UDT with a code and a name and I'm just trying to bring the name through.