Interactive sorting on multiple columns SSRS

Question

Question is easy but answer doesn't seem so. I'd like to apply sorting for many columns with numeric data and sort them asc/desc.

Example: I have a table with clients. First column is a client name. Next 10 columns refer to their summarized income, GP, costs etc.. My table is grouped by client name. So for each client their income, GP (these 10 columns) is summarized in each column. Let's say I have 50 rows with unique client names with summarized values in each column.

I would like to apply interactive sorting to these 10 columns. For example - when I click 'up' arrow on 'Income' column then data on that column would sort ascending (and of course the rest of the table), when I click 'down' arrow on 'GP' column then data on that coulmn would sort descending.

Is that possible at all?

I raed many articles about interactive sorting but it always refer to groups or datailed rows, it doesnt' work for me in this case

Answer 1

If I understand you correctly. Your dataset returns something like

Client    AmountA   AmountB  AmountC... etc
Dave      10        12       12
Dave      20        30       40
Bob       5         10       15
Bob       15        20       25

And your report shows these values aggregated by Client, something like

Client    AmountA   AmountB  AmountC... etc
Dave      30        42       52
Bob       20        30       40

If this is correct then it's quite simple to get the sorting working as expected.

If we assume the rowgroup is called 'rgClient' ....

Right-click on the text box with the column header in it and choose "Text-Box properties". Click the "Interactive Sorting" tab.

Select "Enable interactive sorting on this text box"

Click the "Groups" option - we want to sort the grouped data

Select the name of your row group (e.g. rgClient)

In the Sort by, click the fx button and setthe expression to whatever the expression is in the textbox that you want to sort (e.g. =SUM(Fields!AmountA.Value) )

Repeat for each column.

Below is a screenshot of a sample I threw together that does the same with my sample data grouped by Country, and sorting by Amount. You can see my RowGroup is called CountryGroup here.

Hopefully you can see how each part relates and map this onto your own report.