Purchase analysis

[former_member232853]

Hi there All,

Is there any possible way for me to see the the purchase analysis in my Bp's currency? i want to see information exactly like the purchase analysis shows it? Just if my BP is Dealing in USD it must show USD not automatically convert it to Rand? i have seen some of the posts and queries about this question but with those queries it changes the report quite a lot and gives a lot lesser information.

Thank You

[Johan_H · Accepted solution]

Hi Ross,

Do you get the report converted to system / local currency even though you have not checked the Display Amounts in System Currency check box ?

Come to think of it, the Purchase analysis report gives you totals at the bottom, which would be impossible if the rows can be in multiple currencies.

You are probably going to have to look into a query or Crystal Reports as well. The good news is, in that case it is just a matter of writing the query so that it shows you everything you need.

Regards,

Johan

[former_member232853 · Accepted solution]

Thanks Johan, i have a query but i have to have 3 different reports for PO's, GRV,s and Invoices and does not give me  option for monthly and quarterly reports as well as does not give me total of all Goods Recipets it gives me totals of each one. Please see my query below and if you know how to help create all the parameters or structure the query in order to get a similar view to the Standard Purchase analysis.

SELECT [Type] as DocType, [Code] as CustomerCode, [CustName] as CustName, [Currency] as Currency, [Balance] as 'Total Open Balance', 

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 'GR' as 'Type', 'Goods Reciept' as 'DocType', T0.[CardCode] as Code,T0.[CardName] as CustName,

sum(T0.[DocTotal]) as Total,sum(T0.[DocTotalFC]) as TotalForeign, 

month(T0.[DocDate]) as month, T1.OrderBalFC as Balance, T0.DocCur as Currency  

FROM OPDN T0

left outer join OCRD T1 on T0.cardname = T1.cardname

left outer Join Ocrg T2 on T1.GroupCode = T2.GroupCode

WHERE year(T0.[DocDate]) = 2016 and T2.[Groupname] like '%Import%' and T0.cardname like '%%'

GROUP BY T0.[CardName],T0.[DocDate],T0.[CardCode],T1.OrderBalFC, T0.DocCur

Union All

(SELECT 'GR' as 'Type', 'goods Return' as 'DocType', T0.[CardCode] as Code,T0.[CardName] as CustName,

sum(T0.[DocTotal]) * -1 as Total,sum(T0.[DocTotalFC]) * -1 as TotalForeign,

month(T0.[DocDate]) as month, T1.OrderBalFC * -1 as Balance, T0.DocCur as Currency 

FROM ORPD T0 

left outer join OCRD T1 on T0.cardname = T1.cardname

left outer Join Ocrg T2 on T1.GroupCode = T2.GroupCode

WHERE year(T0.[DocDate]) = 2016 and T2.[Groupname] like '%Import%' and T0.cardname like '%%'

GROUP BY T0.[CardName],T0.[DocDate],T0.[CardCode],T1.OrderBalFC, T0.[DocCur]))S

Pivot

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

[Johan_H · Accepted solution]

Hi Ross,

So that I understand what you have and what you need, this query gives us the monthly report for Goods Receipts P/O, where the only parameter is the year:

• Of this report you need the quarterly version and yearly version ?
• You said: "create all the parameters", do you mean that you want to be able to run this report (and to the Q an Y versions) with more limiting parameters (for example by BP code, or BP group) ?
• Instead of a single row per Goods Receipt P/O, you want a single row per BP ?

Regards,

Johan

[former_member232853 · Accepted solution]

Yes That is Correct, i would like to have it as close to the Standard SAP one with as many of the same parameters as possible. when the user runs the query i would like it to look like the selection criteria in the standard one too or as close as possible.

Thank you

[Johan_H · Accepted solution]

Hi Ross,

Completely replicating the analysis report with all possible parameters using a query would be many hours of work, would become very confusing to use for users, and would probably require many a fix and/or consultation before the results would be completely as expected.

If that is what you want, then you should contact your SAP partner, look for a third party reporting addon, or (and this you could do at any rate) suggest to SAP that they develop the analysis report to allow the user to show results in BP currency.

Short of that, because the query we are starting out with is pretty good, I would recommend that we come up with a few separate queries for the most common uses in your company. You can simplify their use by grouping them under a single query group, and by thinking up a naming convention that will make it clear to even TMSUITC which one to pick for which purpose.

So just to get you started, here is the report for GRPOs / quarters, and then I 'll add other versions later:

SELECT [Type] as DocType, [Code] as CustomerCode, [CustName] as CustName, [Currency] as Currency, [Balance] as 'Total Open Balance'

,isnull([1],0)  + isnull([2],0) + isnull([3],0) as Q1

,isnull([4],0) + isnull([5],0) + isnull([6],0) as Q2

,isnull([7],0) + isnull([8],0) + isnull([9],0) as Q3

,isnull([10],0) + isnull([11],0) + isnull([12],0) as Q4

from

(SELECT 'GR' as 'Type', 'Goods Reciept' as 'DocType', T0.[CardCode] as Code,T0.[CardName] as CustName

       ,sum(T0.[DocTotal]) as Total,sum(T0.[DocTotalFC]) as TotalForeign, month(T0.[DocDate]) as 'month'

    ,T1.OrderBalFC as Balance, T0.DocCur as Currency 

FROM OPDN T0

left outer join OCRD T1 on T0.cardname = T1.cardname

left outer Join Ocrg T2 on T1.GroupCode = T2.GroupCode

WHERE year(T0.[DocDate]) = 2016 and T2.[Groupname] like '%Import%' and T0.cardname like '%%'

GROUP BY T0.[CardName],T0.[DocDate],T0.[CardCode],T1.OrderBalFC, T0.DocCur

Union All

(SELECT 'GR' as 'Type', 'goods Return' as 'DocType', T0.[CardCode] as Code,T0.[CardName] as CustName

       ,sum(T0.[DocTotal]) * -1 as Total,sum(T0.[DocTotalFC]) * -1 as TotalForeign, month(T0.[DocDate]) as 'month'

    ,T1.OrderBalFC * -1 as Balance, T0.DocCur as Currency

FROM ORPD T0

left outer join OCRD T1 on T0.cardname = T1.cardname

left outer Join Ocrg T2 on T1.GroupCode = T2.GroupCode

WHERE year(T0.[DocDate]) = 2016 and T2.[Groupname] like '%Import%' and T0.cardname like '%%'

GROUP BY T0.[CardName],T0.[DocDate],T0.[CardCode],T1.OrderBalFC, T0.[DocCur]))S

Pivot

(sum(TotalForeign) For

Month

IN (

[1],[2],[3],[4] ,[5],[6],[7],[8],[9],[10],[11],[12]

))P

Regards,

Johan

[former_member232853 · Accepted solution]

Thank you for your help Johan

[Johan_H · Accepted solution]

Single total, with date parameters:

/*select * from opdn x */

DECLARE @FROM DATETIME = /* x.DocDate */ '[%0]'

DECLARE @TO DATETIME = /* x.DocDate */ '[%1]'

SELECT [Type] as DocType, [Code] as CustomerCode, [CustName] as CustName, [Currency] as Currency, [Balance] as 'Total Open Balance'

,S.TotalForeign

from

(SELECT 'GR' as 'Type'

    ,T1.[CardCode] as Code

    ,T1.[CardName] as CustName

    ,(T0.[DocTotal]) as Total

    ,(T0.[DocTotalFC]) as TotalForeign

    ,month(T0.[DocDate]) as 'month'

    ,(T1.[OrderBalFC]) as Balance

    ,T0.DocCur as Currency 

FROM OPDN T0

left outer join OCRD T1 on T0.cardcode = T1.cardcode

WHERE T0.[DocDate] BETWEEN @FROM AND @TO

Union All

SELECT 'RET' as 'Type'

    ,T1.[CardCode] as Code

    ,T1.[CardName] as CustName

    ,(T0.[DocTotal]) as Total

    ,(T0.[DocTotalFC]) as TotalForeign

    ,month(T0.[DocDate]) as 'month'

    ,(T1.[OrderBalFC]) as Balance

    ,T0.DocCur as Currency 

FROM ORPD T0

left outer join OCRD T1 on T0.cardcode = T1.cardcode

WHERE T0.[DocDate] BETWEEN @FROM AND @TO)S

GROUP BY [Type] , [Code], [CustName], [Currency], [Balance],S.TotalForeign

ORDER BY CustomerCode, DocType