Not sure if I can ask this questions in this forum
I have a query in SAP B1, it works perfectly, I want to use this query and create a view based on this query to do some analysis in Power BI
But the result I am getting in the SQL Server Management Studio is missing the Total, but if I run the query in SAP it works without any problem
Below is the query in SAP and a picture of the View in SQL Server Management Studio, can someone please help me out?
SELECT T0.[DocNum] as'Prodn.Order #', T0.[U_CustName], T0.[DueDate] as 'Build Date', T0.[ItemCode], T1.[ItemName] as 'Product Description', sum(T0.[PlannedQty]) as 'Qty', sum(T0.[CmpltQty]) as 'Completed Qty', T4.LineTotal as 'Total', T0.[CloseDate] as 'Completed Date' FROM OWOR T0 left join OITM T1 on T0.[ItemCode]=T1.[ItemCode] INNER JOIN WOR1 T2 ON T0.DocEntry = T2.DocEntry left join ORDR T3 on T3.[DocNum] = T0.[OriginNum] left JOIN RDR1 T4 ON T4.DocEntry = T3.DocEntry AND T4.ItemCode=T0.ItemCode and T4.[U_Prod_Order_No] = T0.[DocNum] WHERE T2.[ItemCode] like '%Labour%' and T0.[DueDate] between [%0] and [%1] and T0.[Status] IN ('R','L') GROUP BY T0.[DocNum], T0.[U_CustName], T0.[PostDate], T0.[ItemCode], T1.[ItemName],T0.[DueDate],T4.LineTotal, T0.[CloseDate]