Interactive Sorting and Matrix

A customer requested the ability to interactively sort a matrix on rows and columns. When they click the sort indicator in a row, the matrix should sort columns based on that row. For example, if the first rows is clicked and we are in an descending sort mode, the following screenshot shows the desired behavior.

When they click on a column sort, all row groups would sort within the column clicked. Sounds simple? I’ll be quick to point out that interactive sort is currently not supported directly inside a matrix with dynamic row groups and column groups. The only supported scenario for interactive sort with matrix is a sort indicator in the matrix corner which sorts based on overall column aggregates when a dynamic row group is present. That case is supported because the interactive sort doesn’t reach across from the column to the row axis of the matrix; it just sorts column groups relative to each other based on the overall aggregate of an entire column group instance. Robert Bruckner explains this in the discussion forum and provided a sample report.

So, column sort is out of the question unless you get really innovative and post back to the report by following the approach I presented in my Remembering Report Parameters blog and change the actual query once you know the row and column coordinates. As far as sorting on rows, I got this working somewhat by making the following changes.

I set the Choose What To Sort option to the name of the column group, Sort By to the data field (measure), and Apply This Sorting to the name of the dataset. I said this works “somewhat” because a subsequent sort on another row doesn’t work. The user must refresh the report to go back to unsorted state before they click on another row.

It will be nice if tablix tells you upfront that this isn’t supported so folks don’t trash time thinking that they didn’t get the options right.