Crystal Reports子报表：如何添加分组汇总列并拆分分组

方法一：通过SQL语句添加分组汇总行 #

直接修改原有SQL，用UNION ALL合并明细行与分组汇总行，实现分组拆分和单独显示总和：

-- 明细数据行
SELECT  
    'DETAIL' AS RowType,
    SUM(SorDetail.MPrice) OVER (PARTITION BY SorDetail.SalesOrder, [CusSorDetailMerch+].InvoiceGrouping) AS GroupTotal,
    SorDetail.SalesOrder,
    SorDetail.SalesOrderLine,
    SorDetail.MStockCode, 
    SorDetail.MStockDes, 
    [CusSorDetailMerch+].InvoiceGrouping, 
    SorDetail.MOrderQty, 
    SorDetail.MPrice, 
    [CusSorDetailMerch+].SerialNumber 
FROM     
    SorDetail 
INNER JOIN
    [CusSorDetailMerch+] ON SorDetail.SalesOrder = [CusSorDetailMerch+].SalesOrder 
                         AND SorDetail.SalesOrderLine = [CusSorDetailMerch+].SalesOrderInitLine
WHERE        
    SorDetail.SalesOrder = '000000000' + '{?SalesOrder}' 
    AND SorDetail.SalesOrderInitLine = '{?Line}'

UNION ALL

-- 分组汇总行
SELECT  
    'SUMMARY' AS RowType,
    SUM(SorDetail.MPrice) AS GroupTotal,
    SorDetail.SalesOrder,
    NULL AS SalesOrderLine,
    NULL AS MStockCode, 
    '分组汇总' AS MStockDes, 
    [CusSorDetailMerch+].InvoiceGrouping, 
    NULL AS MOrderQty, 
    NULL AS MPrice, 
    NULL AS SerialNumber 
FROM     
    SorDetail 
INNER JOIN
    [CusSorDetailMerch+] ON SorDetail.SalesOrder = [CusSorDetailMerch+].SalesOrder 
                         AND SorDetail.SalesOrderLine = [CusSorDetailMerch+].SalesOrderInitLine
WHERE        
    SorDetail.SalesOrder = '000000000' + '{?SalesOrder}' 
    AND SorDetail.SalesOrderInitLine = '{?Line}'
GROUP BY SorDetail.SalesOrder, [CusSorDetailMerch+].InvoiceGrouping

ORDER BY 
    [CusSorDetailMerch+].InvoiceGrouping,
    CASE RowType WHEN 'DETAIL' THEN 1 ELSE 2 END

说明 #

• 新增RowType字段区分明细行和汇总行，排序时确保明细在前、汇总在后
• 汇总行仅保留分组标识和总和，其他字段设为NULL或自定义文本（如分组汇总）
• 执行后每个分组的明细数据后会自动追加该行的总和行，实现分组拆分效果

方法二：利用Crystal Reports自带分组功能（推荐） #

无需修改SQL，直接通过报表设计器实现分组和汇总，更灵活易维护：

1. 创建分组：右键报表空白区域 → 插入 → 组，选择[CusSorDetailMerch+].InvoiceGrouping作为分组字段，完成分组设置
2. 布局调整：将现有字段拖至「详细资料」节，组页眉可添加分组标题（如InvoiceGrouping字段）
3. 添加组汇总：右键MPrice字段 → 插入 → 汇总，选择「求和」，汇总位置选择「组尾」，每个分组末尾会自动生成总和行
4. 拆分分组：右键「组尾」节 → 格式化节，可勾选「在后面新建页」实现分组分页，或调整节间距增加分组间空白
5. 优化显示：可对组尾的汇总字段设置格式（如加粗、变色），增强辨识度

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