SSRS 14 - Matrix Column - Get Value Where Month = MAX(Month)

Question

I've a Matrix to show the values over the months. But the last column I want to show the Varianve between the current month and the previous month. I've this dataset (Months):

Servername  Month   Year    Reference   Value   Previous_Value
SV1            8    2017       80        11          Null
SV1            9    2017       80        13           11
SV1           10    2017       80        18           13
SV1           11    2017       80        21           18
SV1           12    2017       80        12           21
SV1            1    2018       80        18           12

Basically, I want to build a expression that allows me to get the value from MAX(Month) and MAX(Year). I try this:

=IIF(Fields!Month.Value = max(Fields!Month.Value, "Months") and Fields!Year.Value = max(Fields!Year.Value, "Months"),Fields!Previous_Value.Value,0)

But when I run the report I'm getting 0 to all of my machines... And my final matrix are:

**Servername    8   9   10  11  12  1   Previous_Value**
      SV1       11  13  18  21  12  18       12

How can I do this?

Thanks!

Alan Schofield Alan Schofield · Accepted Answer · 2018-01-31T16:59:00

Your expression is saying if Month = MAX(Fields!Month.Value) which is 12 and Year = Max(Fields!Year.Value) which is 2018 then show Previous_Value.

As non of your rows match this then it won't work.

I've not tested this but try aggregating the month and year and comparing to that. This is easier to do in SQL by adding a new column

SELECT *, ([Year] *100) + [Month] as YearMonthKey  FROM myTable

Now your expression can just check YearMonthKey against MAX(Fields!YearMonthKey.Value)

SSRS 14 - Matrix Column - Get Value Where Month = MAX(Month)

1 Answers