Introduction
I wrote a blog on “Dynamic Sorting” in SSRS sometime back. Even though I successfully achieved sorting the data dynamically, I failed to find out a way of applying the “Sort Direction” dynamically as the Sort Direction did not accept an expression to manage it.
Recently, I came across a trick which solves this very easily purely in SSRS. Let’s jump on to the implementation now without wasting much time.
Implementation
Continuing with the same example of sorting 3 columns – Col1,Col2 & Col3 in any preferred order, we can specify the Sort Direction dynamically in the following way -
1. Define 3 additional parameters - @Col1SortDirection,@Col2SortDirection & @Col3SortDirection of STRING type.
2. While setting the Sort Options for Tablix or Group, we have to define sorting for double the number the sorting columns as under -
The Sort By expressions will be defined as under -
a. IIF(Parameters!Col1SortDirection.Value = "Ascending",Fields(Parameters!SortCol1Name.Value).Value,Nothing)
b. IIF(Parameters!Col1SortDirection.Value = "Descending",Fields(Parameters!SortCol1Name.Value).Value,Nothing)
c. IIF(Parameters!Col2SortDirection.Value = "Ascending",Fields(Parameters!SortCol2Name.Value).Value,Nothing)
d. IIF(Parameters!Col2SortDirection.Value = "Descending",Fields(Parameters!SortCol2Name.Value).Value,Nothing)
e. IIF(Parameters!Col3SortDirection.Value = "Ascending",Fields(Parameters!SortCol3Name.Value).Value,Nothing)
f. IIF(Parameters!Col3SortDirection.Value = "Descending",Fields(Parameters!SortCol3Name.Value).Value,Nothing)
Note: we can use any CONSTANT value to replace Nothing used in above expression.
3. Pass the required Sort Direction, say - @Col1SortDirection = “Ascending”, @Col2SortDirection = “Descending” & @Col3SortDirection = “Ascending” or in any preferred value.
4. Pass the values for other parameters as explained in my last post.
Conclusion
With this we can now easily control the dynamic sorting with full flexibility of specifying the Column Names & the Sort Direction both dynamically using pure SSRS.
Hope, this will help you a lot!
I wrote a blog on “Dynamic Sorting” in SSRS sometime back. Even though I successfully achieved sorting the data dynamically, I failed to find out a way of applying the “Sort Direction” dynamically as the Sort Direction did not accept an expression to manage it.
Recently, I came across a trick which solves this very easily purely in SSRS. Let’s jump on to the implementation now without wasting much time.
Implementation
Continuing with the same example of sorting 3 columns – Col1,Col2 & Col3 in any preferred order, we can specify the Sort Direction dynamically in the following way -
1. Define 3 additional parameters - @Col1SortDirection,@Col2SortDirection & @Col3SortDirection of STRING type.
2. While setting the Sort Options for Tablix or Group, we have to define sorting for double the number the sorting columns as under -
The Sort By expressions will be defined as under -
a. IIF(Parameters!Col1SortDirection.Value = "Ascending",Fields(Parameters!SortCol1Name.Value).Value,Nothing)
b. IIF(Parameters!Col1SortDirection.Value = "Descending",Fields(Parameters!SortCol1Name.Value).Value,Nothing)
c. IIF(Parameters!Col2SortDirection.Value = "Ascending",Fields(Parameters!SortCol2Name.Value).Value,Nothing)
d. IIF(Parameters!Col2SortDirection.Value = "Descending",Fields(Parameters!SortCol2Name.Value).Value,Nothing)
e. IIF(Parameters!Col3SortDirection.Value = "Ascending",Fields(Parameters!SortCol3Name.Value).Value,Nothing)
f. IIF(Parameters!Col3SortDirection.Value = "Descending",Fields(Parameters!SortCol3Name.Value).Value,Nothing)
Note: we can use any CONSTANT value to replace Nothing used in above expression.
3. Pass the required Sort Direction, say - @Col1SortDirection = “Ascending”, @Col2SortDirection = “Descending” & @Col3SortDirection = “Ascending” or in any preferred value.
4. Pass the values for other parameters as explained in my last post.
Conclusion
With this we can now easily control the dynamic sorting with full flexibility of specifying the Column Names & the Sort Direction both dynamically using pure SSRS.
Hope, this will help you a lot!
