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.
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,