I've read about the "Top N" filter not supporting aggregated data, so I suspect I may not be able to achieve what I'm trying to, however I'm hopeful someone may recommend an alternative approach to my problem?
I am showing the Top and Bottom performers over various periods of time. For example, the Top & Bottom performer in the last week, the last month, the last quarter and last year. I have one report with 8 different tables on it - 2 tables (Top & Bottom) for each time period.
I thus also have 4 different datasets in my report, each with a different "on-or-after" FetchXML date parameter to select the appropriate set of data for the time period being reported on.
Within my report, I calculate the performance achievement of each department and sort the results in descending order. This all works just fine.
I now need to show only the Top 5 departments in each table, and this is where I come unstuck.
If I use the Filter criteria on the Department field within the Row Group, I don't get the right result. From what I've read it looks like the filter is applied to the original dataset, and not the resulting aggregated list of Departments after I've calculated each one's performance.
I then tried adding a filter on the Tablix properties itself, again using filtering on the Top 5 Departments, but this then gave me a very strange results, showing only 1 Department.
