Many times while developing any report we come across a situation where we need to SORT the TABLE columns or the columns in the GROUP dynamically. There are many well known ways to achieve this. However, here I will demonstrate a not-so-known way -
Assuming that the sorting is to be done on 3 columns – Col1,Col2 & Col3. But the ORDER is undecided until runtime. Now, what we can do is -
- Define 3 Report parameters. Say - @SortCol1Name,@SortCol2Name & @SortCol3Name of STRING type.
- Pass the name of the columns which need to sorted in the required order. Say -
- @SortCol1Name = “ColumnX”,@SortCol2Name = “ColumnY” and @SortCol3Name = “ColumnZ”
- OR @SortCol1Name = “ColumnY”,@SortCol2Name = “ColumnZ” and @SortCol3Name = “ColumnX”
- OR any preferred order
- In the Sorting Option of the Properties dialog box of Table or Group, set the sort expression as under -
This finally gets converted to –Fields!ColumnX.Value,Fields!ColumnY.Value & Fields!ColumnZ.Value if we have the passed the following values for the parameters - @SortCol1Name = “ColumnX”,@SortCol2Name = “ColumnY” and @SortCol3Name = “ColumnZ”
With this trick, we can easily set the sort expression dynamically. However, there is a limitation that we can not set the Sort Direction dynamically. I could not find out a way for it. If you have any trick to get it done with this, please leave your suggestion as a comment to this post.