Sorting by parameter in SSRS

If you want to sort dataset/tablix using parameter follow these steps:

  1. Create two parameters: SortBy and Sort Order

devenv_2016-09-15_13-27-22

  • In SortBy you have to list all columns you want to sort by, eg.:

devenv_2016-09-15_13-31-38

Label is what user will choose, Value is value in your dataset you want to sort by. You can set default value in “Default values” tab.

  • In SortOrder you have to put two values:

devenv_2016-09-15_13-34-34

That values will determine order of sorting. You can set default value in “Default values” tab.

2. Set sorting values

Go to Tablix -> Properties -> Sorting and add two sorting expressions. Because you cannot set custom sorting order, you need to put two sorting expressions:

=IIF(Parameters!SortOrder.Value=”Asc”,Fields(Parameters!SortBy.Value).Value,0) where you set order as A-Z

=IIF(Parameters!SortOrder.Value=”Desc”,Fields(Parameters!SortBy.Value).Value,0) where you set order as Z-A

devenv_2016-09-15_13-37-16

That`s all 😉

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s