CODEWITHSUNDEEP
t-sqlreporting-services
SidC
SidC

Reputation: 3213

How to Modify an SSRS Matrix Report to Display Averages of Totals?

I am using SSRS 2016 to build a report object involving a matrix. I want my rows to sum at the far right, but my columns need to be an average of the values in a given group. I want to do this in SSRS as opposed to aggregating in TSQL. My report, thus far, looks like this: summary report

The intended result is for the first column of data to average to 2 and the next 2 columns to average to 1.

The SQL that drives the report is as follows:

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

My report layout is here When I try to run the report, an error is thrown:

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

How do I accomplish this type of summary?

Upvotes: 1

Views: 299

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21703

Without seeing the report design it's hard to tell but assuming that you have a row group that groups by date, let's also assume that this row group is called grpDate.

Finally I will assume the the main data cells have an expression something like =COUNT(Fields!MembID.Value)

What you need to do is get the sum of each row group and average the results, like this...

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

Note that the row group name must be enclosed in quotes and is case sensitive

Upvotes: 1

Related Questions