如何将多个作业成本数据汇总至单条合同记录展示

问题根因 #

ContractDetails合同明细表与JobDetails作业明细表左连接时，一个合同对应多条作业记录，每一条作业都会生成独立的查询行，导致同一个合同出现多条重复数据。

解决方案 #

方案1：优化SQL查询（优先推荐） #

先对作业维度的指标按合同维度预聚合，再关联合同表，从根源避免行膨胀，修改后的查询语句如下：

SELECT 
c.Contract
,c.Department
,c.Contract + ' ' + c.Description as ContractDescription
,c.ContractStatus
,c.ContractAmt
,c.BilledAmt
,job_agg.RecentInvoice
,job_agg.RecentJobCost
,job_agg.TotalActualCost
,job_agg.JobNumberList
,c.udProjFolderRecd
,c.udBillStatus
FROM ContractDetails c
LEFT JOIN (
    SELECT 
        j.JCCo,
        j.Contract,
        MAX(ap.Mth) AS RecentInvoice,
        MAX(jc.Mth) AS RecentJobCost,
        SUM(jc.ActualCost) AS TotalActualCost,
        STRING_AGG(DISTINCT j.Job, ',') AS JobNumberList
    FROM JobDetails j
    LEFT JOIN APInvoice ap ON j.JCCo = ap.JCCo AND j.Job = ap.Job
    LEFT JOIN JobCost jc ON j.JCCo = jc.JCCo AND j.Job = jc.Job
    GROUP BY j.JCCo, j.Contract
) job_agg ON job_agg.JCCo = c.JCCo AND job_agg.Contract = c.Contract
WHERE c.JCCo = (@Company)
AND c.ContractStatus IN (@ContractStatus) 
AND c.Department IN (@Department)

该方案查询结果天然为一行对应一个合同，无需额外在报表层做聚合处理，数据准确性和查询性能都更优。

方案2：调整SSRS报表配置（无需修改SQL） #

如果不方便改动现有查询逻辑，可直接在SSRS层做聚合处理：

1. 选中报表表格控件，新增行组，分组依据选择Contract合同编号字段
2. 删除原有的明细行，仅保留组头行
3. 对应字段调整为聚合计算：
   • 日期类字段（最近发票日期、最近作业成本日期）使用MAX()函数聚合
   • 金额类字段（总成本、总开票金额）使用SUM()函数聚合
   • 作业编号如需展示所有关联值，使用JOIN(LOOKUPSET(Fields!Contract.Value, Fields!Contract.Value, Fields!JobNumber.Value, "你的数据集名称"), ",")实现拼接
     该方案适合临时快速调整，缺点是大数据量下报表加载速度慢于SQL层优化方案。

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