Sorting row group by report items calculation

Question

I have a calculation in my report reportitems!Textbox42.Value - reportitems!Textbox39.value

How can I sort a row group by this calculation, report items are not accepted in a sort.

I have tried making a calculated field based on the above expression, but get an error that FieldValue are denying reportitems

You will have to repeat the expressions of the 2 textboxes and calculate their difference in your sort expression. — Wolfgang Kais
@WolfgangKais this makes perfect sense..... however I have a visibility expression on one row group on the report to hide any rows where the difference is greater than 0. This seems to affect the ordering of the remaining rows. It appears the sort is working on the total difference and not the difference on the remaining rows. — R_Avery_17
If the expressions contain aggregates that operate on different group levels, you might need to adjust the Scope parameter for some of the aggregate functions (that are meant to be applied to a containing group level). Can you update your question and add details on the expressions and the placement of the textboxes? — Wolfgang Kais

Strawberryshrub Strawberryshrub · Accepted Answer · 2018-09-26T10:57:42

You cant use ReportItems!Test.Value in a calculated field, thats not possible as well as aggregate functions.

You have to write your calculation in your sorting expression of the tablix. For example:

'Expression of Textbox1 in Tablix1
=Fields!Sales.Value - Fields!Income.Value

'Expression of Textbox2 in Tablix1
=Fields!Sales.Value / Fields!Income.Value

'The next two expressions are the same
=ReportItems!Textbox1 * ReportItems!Textbox2  'Label1
=(Fields!Sales.Value - Fields!Income.Value) * (Fields!Sales.Value / Fields!Income.Value)  'Label2

Go to your tablix under Tablix Properties > Sorting > Add > Expression and write in the expression from Label2. This way you can sort after your expression.

If you have some groupings in your tablix you can also go to your tablix under Row Groups > Group Properties (the drop down) > Sorting > Add > Expression.

Sorting row group by report items calculation

1 Answers