如何修改SSRS矩阵报告以显示总数的平均值？

Question

我正在使用SSRS 2016构建一个涉及矩阵的报表对象。我希望我的行在最右边求和，但我的列需要是给定组中的值的平均值。我想在SSRS中做到这一点，而不是在TSQL中进行聚合。到目前为止，我的报告是这样的：

​

预期的结果是第一列数据的平均值为2，接下来的两列数据的平均值为1。

驱动报表的SQL如下所示：

DECLARE @year int = 2021
DECLARE @hpcode varchar(200) = 'BB'

/*Internal variables*/
DECLARE @month int = 1
DECLARE @monthend int = 12
DECLARE @table table (MEMB_KEYID UNIQUEIDENTIFIER, MEMBID varchar(50), RLSHIP varchar(2),
RELATIONSHIP varchar(50), HPCODE VARCHAR(10), OPT VARCHAR(25), EMPGROUP VARCHAR(40), 
COMPANY_ID VARCHAR(10), MONTHDATE date)

/*If current year is selected, only grab up to current month*/
IF (YEAR(GETDATE()) = @year)
   BEGIN
          SET @monthend = MONTH(GETDATE())
   END

WHILE @month<=@monthend
BEGIN
   INSERT INTO @table (MEMB_KEYID, MEMBID, RLSHIP, RELATIONSHIP, HPCODE, OPT, EMPGROUP, COMPANY_ID, MONTHDATE)
   SELECT MHP.MEMB_KEYID, MMV.MembID, mmv.rlship,
   CASE 
   when mmv.RLSHIP = '18' then  'Subscriber'
   when mmv.RLSHIP in ('01','53') then 'Spouse'
   else 'Child/Other Dependent' end as RELATIONSHIP,
   MHP.HPCODE, MHP.OPT, EMPGROUP, MHP.COMPANY_ID, DATEFROMPARTS(@year,@month,1)
   FROM MEMB_HPHISTS MHP
   INNER JOIN MEMB_MASTER_V MMV on MMV.MEMB_KEYID = MHP.MEMB_KEYID

   WHERE ISNULL(MHP.OPTHRUDT, DATEFROMPARTS(@year,@month,1) ) >= DATEFROMPARTS(@year,@month,1)
          and MHP.OPFROMDT <= EOMONTH(DATEFROMPARTS(@year,@month,1))
   AND MHP.HPCODE in (@hpcode)
   and mmv.MembID NOT LIKE 'TEMP%'
   

   /*Increment @month*/
   SET @month = @month+1
END
 /*Grab data*/
 SELECT Memb_KEYID, MembID, RLSHIP, RELATIONSHIP, HPCode, OPT, EMPGROUP, COMPANY_ID, MonthDate 
 from @Table

我的报告布局是

当我尝试运行报告时，抛出了一个错误：

The value expression for the text box txtOPTAverageCount has a scope parameter that is not valid for an aggregate function.  

如何完成这种类型的摘要？

Answer 1

如果不看报表设计，就很难判断，但假设您有一个按日期分组的行组，让我们还假设这个行组称为grpDate。

最后，我将假设主数据单元格有一个类似=COUNT(Fields!MembID.Value)的表达式

您需要做的是获得每个行组的总和，并对结果进行平均，如下所示……

=AVG(COUNT(Fields!MembID.Value, "grpDate"))

请注意，行组名称必须用引号引起来，并且区分大小写