Customized Sorting Using Parameter

Let’s give some customize option to our end user to sort the report output as per their choice.

Let’s go through with the following steps:

Step1. Create a new parameter and list out all the field names under Available Values property of the parameter. Specify field names under Label as you want to show to your end user and keep the value same as in your database field by comma separator and sorting order. Refer Image-1

 

Image-1

Step2. Now we need to write our query in a dynamic fashion in dataset. So open your dataset properties and under query window, specify your query.

Refer Image-2

 

Image-2

Step3. Now put following expression at the end of your dataset query. Refer Image-3

=<Your Dataset query>+IIF(Parameters!p_sortby.Value="None"," ","Order by "+Parameters!p_sortby.Value)

 

Image-3

Downloadsample Report

Sorting using Multi-value parameter

Let’s give an option to our end user to sort the report output as per their selected fields. Not by report design.

To keep the sorting feature more rich and relevant, let’s go through with the following steps:

Step1. Create a new parameter (enable Allow Multiple Values) and list out all the field names under Available Values property of the parameter. Specify field names under Label as you want to show to your end user and keep the value same as in your dataset field name. Keep the order of the fields in which you want to sort the report output. Refer Image-1

 

Image-1

Step2. Now open property window of your table’s group and select Sorting. Refer Image-2

 

Image-2

Step3. Now put following expression under Sort by and also set required Order for each of fields that are available in your parameter list. Refer Image-3

=IIF(join(Parameters!p_sortby.Value,",").Contains("EmployeeName"),Fields!EmployeeName.Value,Nothing)

 

Image-3

Downloadsample Report