Sorting aggregate data in SSRS report

Question

I have what seems to be fairly simple table in an SSRS report. My data query gives me a set of product names with a price column and category column. In my table, I have shown the categories as rows and the total price of all products in that category as data.

How can I sort the categories based on the largest aggregate sum of prices?

I've tried adding a sort expression to the category row group and the column group, but neither are working correctly. The sort expression is 'Sum(Product_price)'

My guess is that it's sorting based on the largest individual produce price rather than the aggregate, or something else entirely.

Answer 1

My recommendation would be to create a subquery in your sql statement that returns the sum.

SELECT T1.*,
    (SELECT Sum(S1.YourFieldNameHere) FROM Products S1 WHERE S1.AValueToLinkWith = T1.AValueToLinkWith) AS Product_price_sort
FROM Products T1

Then use your new field in your sorting expression like any other field.

Fields!Product_price_sort.Value