SSRS: sorting rows in group by how many detail rows they contain

Question

I have an SSRS tablix that has three levels of grouping. In the innermost group, I need to sort the rows by how many detail rows they contain. I've researched and tried many custom sort orders on the innermost grouping. I'm working with PHI so I cannot show you my data but here is an idea:

Person A
  Monday
    Meal 1
      Orange
      Apple
    Meal 2
      Carrot
    Meal 3
      Waffle
      Bacon
      Milkshake

I need them to sort in order "Meal 3, Meal 1, Meal 2" beneath "Monday"

The underlying datasource is a sql query with a row for each food item. It is complicated, repeatedly unioned, and repeatedly aggregated. I didn't write it, I'm just being asked to sort the resulting report in new ways. I don't want to touch the query because it works and validating a new query is not feasible in the time alotted.

I need a sorting solution in SSRS that will also accommodate doing further odd sorts on the same set (meaning something I can nest other unorthodox sorts into that would fall lower down the sorted list).

I'm using Visual Studio 2010 to build the SSRS report, which I think rules out a number of possibilities, but that's something I didn't choose and can't change.

Lucky Lucky · Accepted Answer · 2018-05-03T18:32:30

Look for row groups in the bottom panel (go to view and check on grouping if it's not there). Right click the group for Meal and go to group properties. Go to sorting and click add. For your expression =COUNT(Fields!Food.Value)

Due to the grouping, the count will return the count of all foods in that group for each meal. It should sort it by this count. I believe the correct order in this case would be Z to A (which SSRS will sort numerically high to low).

SSRS: sorting rows in group by how many detail rows they contain

1 Answers