sorting a report by subreport values

Question

I have a subreport that provides items and their value, with a sum of the value at the bottom. I would like to sort my main report, which is the owner of the items, by the total sum of the item value.

I have a main report that supplies the user with a persons name and contact information. I have built a sub report that takes the person's ID and displays the name and value of items associated with them. The subreport has a sum of the items. I would like to sort this report so that the highest total value is at the top. I'm new to working with sub reports in SSRS so any help would be appreciated!

this is my report -> type and balance are the sub report. I know that these are already sorted, but that is just coincidence and I have about a thousand rows that need to be sorted by total balance

Alan Schofield Alan Schofield · Accepted Answer · 2019-01-21T19:06:28

I assume that the subreport is in a tablix of some kind?

If so then the usual process is to include the value you want to sort by in the dataset that drives the tablix.

So the dataset for tablix containing the subreport might look something like

SELECT * 
FROM (
      SELECT personID, SUM(transactionvalues) as SortValue
          GROUP BY personID
     ) q
ORDER BY q.SortValue DESC

Now you can just sort your tablix by the SortValue

sorting a report by subreport values

2 Answers