Sales Analysis report for AR Invoice

[former_member229757]

HI Experts

How to generate a query Sales analysis report for AR Invoice.(Document no wise)

My query is not matching with existing AR invoice Standard analysis report.

SELECT t0.DocEntry, t0.DocDate, t0.cardcode, t0.CardName, (T0.[GrosProfit]/ NULLIF((T0.[DocTotal] - T0.[VatSum]), 0) * 100) As 'GrossProfit%', convert(int,t0.u_udf_quant) as Quant,(Select slpname from oslp where t0.SlpCode=slpcode) as Employee, t0.GrosProfit, (t0.DocTotal-t0.VatSum) as value FROM ORDR t0 WHERE t0.DocDate BETWEEN '06-01-2017' and '06-12-2017' AND t0.CANCELED='N' ORDER BY (T0.[GrosProfit] / NULLIF((T0.[DocTotal] - T0.[VatSum]), 0) * 100) DESC

Thanks

Vinoth

[MD1]

please try...

FOR SAP HANA

SELECT a."CardCode", a."CardName", a."ShipToCode" as "Consignee",V."CityS",a."DocDate", a."DocNum",a."U_UNE_EXIN" AS "Export Invoice NO",
a."NumAtCard" as "PO Number", b."ItemCode" as "Product Code",
c."ItemName" as "Product Name",d."ItmsGrpNam" AS "ItemGroupName", c."SalUnitMsr", b."Quantity" as "No of Pack",
b."NumPerMsr" as "Items per Unit", b."InvQty" as "Qty (TAB/CAP)",b."U_UNE_PKSZ",
b."GrossBuyPr",
b."Currency",
b."Rate",
b."Price",
( b."Price" * b."Rate" ) as "Sale Price",
(b."LineTotal"+ b."DistribSum") as "Total",

CASE
WHEN ((a."DocTotal" - a."VatSum") - a."TotalExpns") = 0.000 THEN 0.000
ELSE ((a."GrosProfit") / ((a."DocTotal" - a."VatSum") - a."TotalExpns")) * 100
END AS "Gross Prof %"
FROM OINV a
INNER JOIN INV1 b ON a."DocEntry" = b."DocEntry"
INNER JOIN OITM c ON b."ItemCode" = c."ItemCode"
INNER JOIN OITB d ON c."ItmsGrpCod" = d."ItmsGrpCod"
INNER JOIN OSLP e ON a."SlpCode" = e."SlpCode"
INNER JOIN OWHS l ON b."WhsCode" = l."WhsCode"
INNER JOIN INV12 V ON A."DocEntry" = V."DocEntry"
INNER JOIN OCRD M ON M."CardCode" = A."CardCode"
INNER JOIN OCRG N ON M."GroupCode" = N."GroupCode"
WHERE a."DocType" = 'I'
and a."DocDate">='20170401' and a."DocDate" <='20171128'
----and N."GroupName" ='[%2]'

UNION ALL
SELECT f."CardCode", f."CardName", f."ShipToCode" as "Consignee",P."CityS",f."DocDate",f."DocNum", f."U_UNE_EXIN" AS "Export Invoice NO",
f."NumAtCard" as "BP Ref", g."ItemCode" as "Product Code",
h."ItemName"as "Product Name", i."ItmsGrpNam" AS "ItemGroupName", h."SalUnitMsr", g."Quantity" as "No of Pack",
g."NumPerMsr" as "Items per Unit", g."InvQty" as "Qty (TAB/CAP)",g."U_UNE_PKSZ",
g."GrossBuyPr",
g."Currency",
g."Rate",
g."Price",
(g."Price" *g."Rate")as "Sale Price",
(g."LineTotal"+ g."DistribSum") as "Total" ,

CASE
WHEN ((f."DocTotal" - f."VatSum") - f."TotalExpns") = 0.000 THEN 0.000
ELSE ((f."GrosProfit") / ((f."DocTotal" - f."VatSum") - f."TotalExpns")) * 100
END AS "Gross Prof %"

FROM ORIN f
INNER JOIN RIN1 g ON f."DocEntry" = g."DocEntry"
INNER JOIN OITM h ON g."ItemCode" = h."ItemCode"
INNER JOIN OITB i ON h."ItmsGrpCod" = i."ItmsGrpCod"
INNER JOIN OSLP j ON f."SlpCode" = j."SlpCode"
INNER JOIN OWHS k ON g."WhsCode" = k."WhsCode"
INNER JOIN INV12 P ON F."DocEntry" = P."DocEntry"
INNER JOIN OCRD N ON N."CardCode" = F."CardCode"
INNER JOIN OCRG M ON N."GroupCode" = M."GroupCode"
WHERE f."DocType" = 'I'
and f."DocDate">='20170401' and f."DocDate" <='20171128'
----and M."GroupName" ='[%2]'