SAP B1: Sales and Returns in one query

I'm a new in SAP B1. Have a task to show the Sales and Returns for the period by BP in one query and after to create the Crystal Report based on data. ORDR and RDR1 - Sales ORDN and RDN1 - Returns. Can you please advise how can I join them? Thanks in advance.

Solution

To receive data from both business objects (orders and returns) you should think about UNIONing them instead of JOINing. A pure SQL query (HANA flavor in this example) would look like this:

SELECT 
     "CardCode"
    ,"ItemCode"
    ,SUM("Total Sale") AS "Total Sale"
    ,SUM("Total Return") AS "Total Return"
FROM
(
    SELECT
         ORDR."CardCode"
        ,RDR1."ItemCode"
        ,RDR1."Quantity" AS "Total Sale"
        ,0 AS "Total Return"
    FROM ORDR
    JOIN RDR1
        ON ORDR."DocEntry" = RDR1."DocEntry"
    WHERE ORDR."DocDate" BETWEEN '2020-01-01' AND '2020-06-30'
    UNION ALL
    SELECT
         ORDN."CardCode"
        ,RDN1."ItemCode"
        ,0 AS "Total Sale"
        ,RDN1."Quantity" AS "Total Return"
    FROM ORDN
    JOIN RDN1
        ON ORDN."DocEntry" = RDN1."DocEntry"
    WHERE ORDN."DocDate" BETWEEN '2020-01-01' AND '2020-06-30'
)
GROUP BY
     "CardCode"
    ,"ItemCode"

To get the results into Crystal Reports (CR) there are quite some options. You may try to

create a database view from the query shown above and query that view from CR
or create a database view from the inner query above (i.e. the one in round brackets), query that from CR, and do the grouping in CR
or use CR's "Command" as data source, and build the command like you would create a database view.