Calculating average in SSRS Matrix Table alongside filtered columns to separate out current versus previous years

0
votes

I have a dataset, returned from a SQL query, that has the following data:

  • TYPE_CODE
  • YEAR

The data set spans the years 2014 through 2019. The TYPE_CODE has 6 different values.

How do I setup an SSRS matrix to provide the following layout and data:

enter image description here

So far I have a matrix setup (see the pic below) that has a row group (TYPE_CODE1) for the TYPE_CODE data, and two column groups (YEAR_PREV and YEAR_CURRENT) that are filtered as follows: - The second column in the matrix is the YEAR_PREV group, and is filtered to not show 2019 data (YEAR <> 2019) - The 4th column in the matrix is the YEAR_CURRENT group, and is filtered to only show 2019 data (YEAR = 2019)

-

This method correctly splits my data, with the green highlighted columns in the pic below representing what is correct:

enter image description here

What is not correct is the average column, as I cannot figure out how to setup that column to only average the columns to the left (the previous years - 2015-2018) and not include the column to the right (2019).

I have tried several different expressions to no avail, primarily trying to limit the count function to only the YEAR_PREV group, like so:

  =count(Fields!TYPE_CODE.Value, "PREV_YEAR")/4

This throws an error telling me that something along the lines of "group cannot be used in aggregate function...".

How do I calculate the average column correctly?

1
reporting-servicesssrs-tablix

1 Answers

0
votes

You need to have 2 column groups with filters applied, first one where year equals 2015-2018 and the second one equals only 2019.

In between each of these columns you need insert your average column that will reflect years 2015-2018.