Use an Aggregate function in Sort Expression

Question

I have a report which uses a dataset returned from a stored procedure. There are two key columns: Name and Value

I am using this dataset for two tablixes. The first is just a straightforward tablix displaying the data.

The second groups the data based on a Name column. I need to order this data based on the Sum of Value column

However I get the following error:

[rsAggregateInDataRowSortExpression] A sort expression for the tablix 'table1' includes an aggregate function. Aggregate functions cannot be used in data row sort expressions.

Is there another way I can show the data grouped by name and still order it by Sum(Value)?

score 20 · Answer 1 · answered Jan 18 '12 at 13:15

20

Instead of sorting on the tablix you need to sort against the row group. Remove the sort on the tablix and then go to the row group properties and put the same sort expression under the sorting section there, this should then work.

answered Jan 18 '12 at 13:15

cg1

201
2
3

score 1 · Accepted Answer · answered Jun 27 '11 at 14:45

1

OK, I just had to add an extra column for the the Sum value to my query and then use that. Not ideal, but it works

answered Jun 27 '11 at 14:45

openshac

4,966
5
46
77

Use an Aggregate function in Sort Expression

2 Answers2

Linked