Interactive sorting in tablix with column and row groupings

Question

I'm trying to define interactive sorting in a tablix. The sorting should affect the row order. Attaching image of the tablix in order to explain what I'm trying to get:

I already tried to define the column header with interactive sorting and get SubjectParentID to be sorted by expression (the same expression as described bellow). The final setting I made is to apply the sorting to all groups in the tablix -

Attaching image: enter image description here

The result wasn't current and the data got messed up in the cells.

Additional information: The columns are grouped by SubjectParentID. The rows are grouped by Username as parent and UserID as a child group.

The values are result of the following expression :

=iif(isNothing(Fields!ResReqCertID1.Value), 
     -99, 
     Sum(iif(Fields!CertStatusID.Value = 3 
             Or Fields!CertStatusID.Value = 4 
             Or Fields!CertStatusID.Value = 5, 
         1, 
         0)) - Fields!ResReqCertID1.Value)

score 0 · Answer 1 · answered Jan 07 '13 at 02:57

0

In the Text Box Properties / Interactive Sorting window, change the Groups selection to the UserID group (assuming that is the most-detailed group providing the rows in your example).

answered Jan 07 '13 at 02:57

Mike Honey

14,523
1
24
40

Unfortunately it does not work, the expression above describe the rows which are not 'directly' related to UsedID – Gil Peretz Jan 07 '13 at 08:01
Well pick whatever Group (from the pick list) which contains your rows. – Mike Honey Jan 07 '13 at 10:25
Already did, without any success. Any more suggestions you might have ? – Gil Peretz Jan 07 '13 at 11:48

score 0 · Answer 2 · answered Sep 08 '22 at 07:35

This can't be done, interactive sorting cannot sort row groups by column values calculated within that group.

If you have 4 static columns you could hard code them. Then having no need for column groups interactive sorting should work as expected.

You could also define a static number, say 10, of dynamic columns, and use pivoting in the thebsql query to get columns 1-10 and their labels. Then set up 10 columns with interactive sorting on the Value_1 fields, and hide them if no data exist for that column. You could then also have an 11th column which also does column grouping but doesn't do sort if there's any chance you'll ever exc3ed the 10 columns.

You could also do this: https://www.c-sharpcorner.com/article/ssrs-interactive-sorting-on-matrix-column-group/ which is somehow even more complicated than my two solutions.

But yeah, you basically can't do this. SSRS cannot be told to sort row groups on values calculated from within the context of the column group that contained the copy of text box in which the sort button was clicked.

Interactive sorting in tablix with column and row groupings

2 Answers2