Dynamic Sort Order of an Individual Parameter

• Hari Seldon-821789

  SSC Enthusiast

  Points: 147

  More actions

  April 1, 2013 at 1:30 pm

  #275802

  I'm creating a report in SSRS 2008 and I have a NAME_LIST parameter that's a concatenation of a name followed by an ID in parentheses and it's sorted by name.

  ex:

  Asimov, Isaac (98765)

  Seldon, Hari (12345)

  Sometimes, users have the ID, but not the name, so I'd like to give them the ability to sort that parameter list dynamically. The default sort order would be by name, but they could change it to sort by the ID values instead.

  I created another parameter called SORT_ORDER and I'm using that to pass either the name field name or the ID field name to the NAME_LIST parameter. The query for the NAME_LIST parameter now ends with: ORDER BY @SORT_ORDER

  On preview, this is generating the following error message on the query with that ORDER BY statement: "The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name."

  I am storing the column name in @SORT_ORDER - not the column position, but I guess the parser is doing the replacement prior to generating the error message.

  Any reason why this doesn't work? Should it work? Is there a better way to dynamically control the sort order of a parameter list?

  Thanks in advance,

  Hari