Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 ssrs change sort order of parameters

Author  Topic 

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-09-16 : 16:13:26
In an SSRS 2008 r2 report, I want to be able to allow the user to sort parameter values based upon the values of lastname, firstname,student number, and school name. The default value of the sort is set to value = 1 which means what order to sort the parameters in.
However, I want to be able to change the order of which item is sorted first, second, third, and fourth. The code I have on the tablix sort is the following:

=IIF(Parameters!Sort.Value = 1, Fields!studentNumber.Value, IIF(Parameters!Sort.Value = 2, Fields!grade.Value, IIF(Parameters!Sort.Value = 4, Fields!homeroomteacher.Value, IIF(Parameters!Sort.Value = 5, Fields!ELL_Status.Value, "3"))))

The iif statement is not working probably since it only changes the what parameter is sorted first. Thus can you show me what to change so I can best accomplish my goal?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-17 : 07:58:29
Do the sort in the query
Go to Top of Page

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-09-17 : 11:05:23
the user wants an option to be able to change the sort order.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-17 : 11:29:28
quote:
Originally posted by jassie

the user wants an option to be able to change the sort order.



Fine, if the user changes the sort order, re-execute the query with the new ORDER by clause.
Go to Top of Page

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-09-17 : 14:48:23
How can I reexecute the query if the sort parameter changes? This would not be with sql,correct? This would be using the 'sort' tab on the tablix correct? Can you show me what you are referring to?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-17 : 17:35:50
First, is your dataset populated by a call to a stored procedure or a select executed in the report? Either way, if the parameters change, SSRs has to execute the query again. So the key is to parameterize the query. Eg you case statement.
Go to Top of Page
   

- Advertisement -