Quote from Administrator on November 25, 2024, 11:52 am

Have you ever wanted to see what is the value of all my open Marketing documents, summarized? This is a fantastic little query that will summarize all open Marketing related documents (Quote, Sales, Delivery, AR Invoice, AR Credit, PO, GRPO, AP, AP Credit) It's a great way to help gauge your companies performance over time.

This query uses the Union All statement which is a very powerful statement and a good one to learn.

Simply Copy the SQL below and Paste into your SAP Business One Query Generator.

Enjoy!!

SELECT 'Quotes' "Document", T0."DocDate", SUM(T0."DocTotal"), SUM(T0."GrosProfit"), SUM(T0."DiscSum") FROM OQUT T0 WHERE T0."DocStatus" = 'O' GROUP BY 'Quotes', T0."DocDate"
UNION ALL
SELECT 'Sales Orders' "Document", T0."DocDate", SUM(T0."DocTotal"), SUM(T0."GrosProfit"), SUM(T0."DiscSum") FROM ORDR T0 WHERE T0."DocStatus" = 'O' GROUP BY 'Quotes', T0."DocDate"
UNION ALL
SELECT 'Deliveries' "Document", T0."DocDate", SUM(T0."DocTotal"), SUM(T0."GrosProfit"), SUM(T0."DiscSum") FROM ODLN T0 WHERE T0."DocStatus" = 'O' GROUP BY 'Quotes', T0."DocDate"
UNION ALL
SELECT 'AR Invoices' "Document", T0."DocDate", SUM(T0."DocTotal"), SUM(T0."GrosProfit"), SUM(T0."DiscSum") FROM OINV T0 WHERE T0."DocStatus" = 'O' GROUP BY 'Quotes', T0."DocDate"
UNION ALL
SELECT 'AR Down Payments' "Document", T0."DocDate", SUM(T0."DocTotal"), SUM(T0."GrosProfit"), SUM(T0."DiscSum") FROM ODPI T0 WHERE T0."DocStatus" = 'O' GROUP BY 'Quotes', T0."DocDate"
UNION ALL
SELECT 'AR Crtedits' "Document", T0."DocDate", SUM(T0."DocTotal"), SUM(T0."GrosProfit"), SUM(T0."DiscSum") FROM ORIN T0 WHERE T0."DocStatus" = 'O' GROUP BY 'Quotes', T0."DocDate"
UNION ALL
SELECT 'Purchase Orders' "Document", T0."DocDate", SUM(T0."DocTotal"), SUM(T0."GrosProfit"), SUM(T0."DiscSum") FROM OPOR T0 WHERE T0."DocStatus" = 'O' GROUP BY 'Quotes', T0."DocDate"
UNION ALL
SELECT 'Goods Receipt POs' "Document", T0."DocDate", SUM(T0."DocTotal"), SUM(T0."GrosProfit"), SUM(T0."DiscSum") FROM OPDN T0 WHERE T0."DocStatus" = 'O' GROUP BY 'Quotes', T0."DocDate"
UNION ALL
SELECT 'AP Invoices' "Document", T0."DocDate", SUM(T0."DocTotal"), SUM(T0."GrosProfit"), SUM(T0."DiscSum") FROM OPCH T0 WHERE T0."DocStatus" = 'O' GROUP BY 'Quotes', T0."DocDate"
UNION ALL
SELECT 'AP Credits' "Document", T0."DocDate", SUM(T0."DocTotal"), SUM(T0."GrosProfit"), SUM(T0."DiscSum") FROM ORPC T0 WHERE T0."DocStatus" = 'O' GROUP BY 'Quotes', T0."DocDate"
UNION ALL
SELECT 'AP Down Payments' "Document", T0."DocDate", SUM(T0."DocTotal"), SUM(T0."GrosProfit"), SUM(T0."DiscSum") FROM ODPO T0 WHERE T0."DocStatus" = 'O' GROUP BY 'Quotes', T0."DocDate"