Crystal报表关联双表后发票数据重复，如何正确汇总未结订单？

我有两张核心数据表/视图：

• vw_Invoice_Summary_01（对应INVOICE SUMMARY）：存储发票汇总数据，已基于此创建按BUYER ID（客户唯一标识）分组的绩效报表，总销售额、总成本、总利润及利润率等计算字段数据准确。
• ksl_vw_rep_CustomerOrdersOutstanding（对应OUTSTANDING ORDERS）：存储客户未结订单数据，同样包含BUYER ID字段。

问题描述 #

尝试通过BUYER ID关联两张表，为报表添加客户未结订单（内部简称OIH）信息时，出现以下问题：

• 插入未结订单表的任意字段后，发票汇总页出现重复记录，所有总计值被错误放大；
• 虽然未结订单数量（公式计算值：Quantity - Quantity_Shipped）显示正确，但主报表的发票汇总逻辑完全失效；
• 移除未结订单表的字段后，报表立即恢复正常；
• 多次调整表关联关系（如尝试不同连接类型）均无法解决问题。

我的核心需求：保留发票表客户汇总数据的准确性，同时展示对应客户的未结订单总和。

Crystal生成的SQL语句 #

SELECT "vw_Invoice_Summary_01"."Invoice_Date", "vw_Invoice_Summary_01"."Goods_Value", "vw_Invoice_Summary_01"."Charges", "vw_Invoice_Summary_01"."Total_Costs", "vw_Invoice_Summary_01"."CostedCharges", "vw_Invoice_Summary_01"."Tax_Base_Total", "vw_Invoice_Summary_01"."Charges_Costs", "vw_Invoice_Summary_01"."Buyer_ID", "vw_Invoice_Summary_01"."Company_Name", "vw_Invoice_Summary_01"."Invoice_Reference", "ksl_vw_rep_CustomerOrdersOutstanding"."Quantity", "ksl_vw_rep_CustomerOrdersOutstanding"."Quantity_Shipped"
 FROM   "CS_Interex_Test_KSL"."dbo"."vw_Invoice_Summary_01" "vw_Invoice_Summary_01" LEFT OUTER JOIN "CS_Interex_Test_KSL"."dbo"."ksl_vw_rep_CustomerOrdersOutstanding" "ksl_vw_rep_CustomerOrdersOutstanding" ON "vw_Invoice_Summary_01"."Buyer_ID"="ksl_vw_rep_CustomerOrdersOutstanding"."Buyer_ID"
 WHERE  ("vw_Invoice_Summary_01"."Invoice_Date">={ts '2024-01-01 00:00:00'} AND "vw_Invoice_Summary_01"."Invoice_Date"<{ts '2024-02-01 00:00:00'})
 ORDER BY "vw_Invoice_Summary_01"."Buyer_ID"

导致问题的公式字段代码 #

{ksl_vw_rep_CustomerOrdersOutstanding.Quantity} - {ksl_vw_rep_CustomerOrdersOutstanding.Quantity_Shipped}

内容的提问来源于stack exchange，提问作者Craig Simpson