Skip to Content
avatar image
Ashraf Sharaf

SAP Business One Standard Sales Analysis Report

Mar 09 at 08:36 AM | 120 Views

I have had a requirement to match the numbers generated by the standard sales analysis report in a hana query, i have done that a lot before but this time i just can't match it. There is always a slight difference missing which is the header discount in OINV.

Usually when i need to group on item level i just get the Price from OINM and the quantity from the invoice rows and it works perfectly, in this system there are items that are basically service items so they don't appear in OINM as there was no inventory movement on them.

So i was wondering how does the standard sales analysis report calculate the price of an item after the header discount? if i do that in a query and group by item code the header DiscSum will just get duplicated because of the row numbers, and if i get max disc sum then i can't group by item code.

Also the system has cancellation invoices so i have to subtract those from the query.

Here is my query that matches the analysis report without item grouping: 

SELECT F0."CardName", sum("Total") AS "Total"
FROM
(
SELECT
 
T0."CardName", T0."DocNum",
CASE WHEN T0."CANCELED" = 'C'  THEN -(SUM("LineTotal")-MAX("DiscSum")) else 
CASE WHEN T0."CANCELED" <> 'C'  THEN (SUM("LineTotal")-MAX("DiscSum")) end end as "Total"
 
FROM OINV T0 
JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
GROUP BY T0."CardName", T0."DocNum", T0."CANCELED"
 
 
UNION ALL 
 
SELECT 
T0."CardName", T0."DocNum",
CASE WHEN T0."CANCELED" = 'C'  THEN (SUM("LineTotal")-MAX("DiscSum")) else 
CASE WHEN T0."CANCELED" <> 'C'  THEN -(SUM("LineTotal")-MAX("DiscSum")) end end as "Total"
 
FROM ORIN T0 
JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry"
JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
GROUP BY T0."CardName", T0."DocNum", T0."CANCELED"
) F0 
GROUP BY F0."CardName"

Any help would be appreciated.

Thanks,
Ashraf

SAP Business One
SAP Business One Extensibility
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Hrvoje Dolenec
    Mar 09 at 01:15 PM

    If you have header discount in OINV, this percentage is applied to all rows in INV1. This is how I got the exact result as system Sales Analysis (not showing full query):

    SUM(CASE WHEN T1."CANCELED" ='C' THEN T0."LineTotal"*-1 ELSE T0."LineTotal" END)*(1-(IFNULL(T1."DiscPrcnt", 0)/100))

    AS "Net revenue"

    FROM INV1 T0 FULL OUTER JOIN OINV T1 ON T0."DocEntry" = T1."DocEntry" FULL OUTER JOIN OITM T2 ON T0."ItemCode" = T2."ItemCode"

    UNION ALL

    SUM(CASE WHEN T5."CANCELED" ='C' THEN T4."LineTotal" ELSE T4."LineTotal"*-1 END)*(1-(IFNULL(T5."DiscPrcnt",0)/100))

    FROM RIN1 T4 FULL OUTER JOIN ORIN T5 ON T4."DocEntry" = T5."DocEntry" FULL OUTER JOIN OITM T6 ON T4."ItemCode" = T6."ItemCode"

    Using full join is useful if you want to get the same results as Sales Analysis by custumers (using inner join shows the same results as SA by items). Also, you need to add correction invoice and correction invoice reversal for exact results.

    Add comment
    10|10000 characters needed characters exceeded