Report builder 3.0 last category's values

Question

I need to get NOT the query's last value by the last category's values (in fact their avg). So for clarity's sake, here's an example of my dataset in the query designer:

Year | Period | File no | Ratio value

[...]

2015 | _12__| 2340 | 0.72

2015 | _13__| 2351 | 0.75

2015 | _13__| 2456 | 0.84

2016 | _01__| 2867 | 0.80

2016 | _02__| 5463 | 1.10

2016 | _02__| 3457 | 0.90

So, as an end-result, in my report, I only want the average of the last period's values to appear. In the example given, it would be Period 2 and the average of 1.10 and 0.90 so a result of 1.00. But I dont know how to get that!

If I use Last(Field!Ratio.Value), I get 0.90. If I use Last(Fields!Periode.Value), I get no changes at all. I can't remove File no in Query designer since it messes up all my ratio values, so I inherently need to keep it.

So if anyone has any idea how to filter out everything except for the last period's values, it would be very much appreciated. Thanks!

Does your Period field contain those underscores in your actual query? You can concatenate year and period to get the latest rows and perform the average on them. Also, there is no a category field in your dataset. — alejandro zuleta
Hi Alejandro, negative, sorry for this. the underscores in the example are solely to make my table look clearer. The periods looks like this "01" and go from 01 to 13 for each year. I have tried to create a calculated field, I called it YearPeriod, = Fields!Year.Value+Fields!Period.Value, but I still have the same issue cause it only changed the label of my Period category from 01 to 201601 and 02 to 201602 — Max S.

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-08-30T16:49:51

Create a calculated field named YearPeriod using:

=CINT(CSTR(Fields!Year.Value)&CSTR(Fields!Period.Value))

To get the average of the latest year-period values use this expression somewhere in your tablix:

=AVG(
  IIF(
  MAX(Fields!YearPeriod.Value)=Fields!YearPeriod.Value,
  Fields!Ratio.Value,
  Nothing
  )
)

Note it will calculte the average of Ratio values which are within the group scope of the cell where you put the expression.

It is not tested but should work, let me know if it works for you.

Report builder 3.0 last category's values

1 Answers