• Jack Corbett (9/12/2013)


    Rod at work (9/12/2013)


    Jack Corbett (9/12/2013)


    While it isn't ideal because it requires the report to be re-run if they change the parameter, I usually implement this with a Sort By parameter on the report itself. Then you set the sort property in the table/list on the report based on the selection.

    That's an interesting idea, which I didn't know anything about. I came across an "Interactive sort" which could be done by right clicking on the column header (not at all obvious to me that could be done). Your idea sounds like a different approach. How would a user be able to interact with that?

    It isn't really an interactive sort that you get with 2008+. It is just another report parameter that you use to sort the data when displayed. When I do this I don't use the Report Parameter as query parameter, some do and then do dynamic sorting in the query, I just use it to determine sort order for the display. For example if your report displays customers by salesperson and sales region (contrived) and some users like to see the report ordered by salesperson and others like to see it sorted by sales region. In 2008+ you could enable interactive sorting and the users can do what they like. With 2005 you can't. So assuming that the data is in a table and has no grouping, I add a sort by parameter to the report that has Salesperson, Sales Region as options. Then in the table properties I set the sort order with an expression like:

    IIF(Parameters!SortBy.Value = "Salesperson", Fields!salesperson.Value, Fields!salesRegion.Value)

    Now when the report is run the user will see the data the order selected. AS I said earlier, the downside is that if the user decides to change the order, the report has to be totally re-run, all the way back to the SQL Server, even though the data hasn't changed. But, I'm not aware of another way to do this.

    The same idea applies if you have some level of grouping, you just have to apply the sort at a different level than the table level.

    Interactive sorting is available in 2005. It works great with table with no grouping and have used in reports with one level of grouping.

    If you need multilevel sorting, viz., sort by col1 then col2 then col3, then I would use report parameter.