模式计算是从所有数据中提取，而不是从子集中提取

Question

我正在SSRS report Builder 2016中创建一个报告。

我有一组数据，其中包含一组机构以及每个机构每月的账单金额(TotalBilled(。每个机构的TotalBilled可以有多个值。我想找到每个机构的模式TotalBilled值。

我在查询中计算模式值，但它来自所有机构的所有TotalBilled，而不仅仅是与我在参数中设置的机构相关的TotalBilled。

这是我的数据集：

Select 
a.AgencyID
a.startdate,
a.enddate,
[TotalBilled],
(SELECT TOP 1 WITH TIES TotalBilled
From    invoiceLine I
WHERE   TotalBilled <> 0
GROUP BY TotalBilled
ORDER BY Count(invl.TotalBilled) DESC ) AS ModeTotalBilled
from Agency a
left join invoiceLine invl
on a.InvoiceID = invl.InvoiceID
where (a.agencyid in (@agency))
and (a.startdate >= @startdate and a.enddate <= @enddate)

我试图加入该机构的模式查询，但在运行时出错

(SELECT TOP 1 WITH TIES TotalBilled
From    invoiceLine I
WHERE   TotalBilled <> 0
AND     a.InvoiceID = I.InvoiceID
GROUP BY TotalBilled
ORDER BY Count(invl.TotalBilled) DESC ) AS ModeTotalBilled

错误：

"无法读取数据集DataSet1的下一个数据行。(rs读取下一个数据行错误(

报表处理过程中发生错误。(rs处理中止(">

更改模式值计算的正确方法是什么，使其受到我的代理参数的影响，而不仅仅是从整个数据库中提取？

Answer 1

这就是您想要的吗？

SELECT a.AgencyID, a.startdate, a.enddate,
(SELECT TOP (1) i.TotalBilled
FROM invoiceLine i
WHERE i.TotalBilled <> 0 AND a.InvoiceID = i.InvoiceID
GROUP BY i.TotalBilled
ORDER BY COUNT(i.TotalBilled) DESC
) as ModeTotalBilled
FROM Agency a
WHERE a.agencyid = @agency AND
a.startdate >= @startdate AND
a.enddate <= @enddate;

我不明白您为什么要在外部查询中加入invoice。您应该限定所有列名，尤其是在使用相关子查询时。

CCD_ 2在子查询中没有意义。如果有多个值符合模式，那么所要做的就是返回一个错误。