ssrs 2008 r2 pass paramter value to a sort

Question

ssrs 2008 r2 pass paramter value to a sort

wendy elizabeth

SSCoach

Points: 16915
More actions
May 22, 2014 at 9:54 am

#308372

In a new SSRS 2008 r2 report, I am going to be using sql within a dataset and not a stored
procedure since will I need to pass multiple values to each parameter.
One of the parameters that I will be working with is allowing the user to be able to pick what items
they want to sort on. The sort will have 3 items that will be always be sorted on first which are:
year, and school_number. The users will be able to pick additional items to sort on like grade, and teacher.
The following is the sql that I have so far:
select year, school_number, grade,teacher,room,course_number, course_name
from dbo.school
order by year, school_number, several parameter values.
Thus I am wondering if you would modify the sql above so that I can allow the user to sort on the additional parameters in any order like grade,teacher,room,course_number, course_name?
If any part of my request is not possible, I am wondering if you would show me what is possible in the sql?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

In SSRS, you can pass the parameter value to an expression in your tablix (table / matrix) to sort the data. If you pass it to an expression, you won't need to pass it to the sql statement. You would just let ssrs do the sort for you.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

wendy elizabeth SSCoach Points: 16915 More actions · Answer 2

I have the following 2 questions:

1. Can you show me how to pass the parameter value in the expression to sort in the SSRS report?

2. Can you show me the sql to pass the parameter values in a stored procedure?

The report will run faster in the stored procedure as compared to the ssrs report.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Here's an example on Parameter values, expressions and sorting.

http://stackoverflow.com/questions/9381726/sort-table-by-parameter-reporting-services-2008

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

wendy elizabeth SSCoach Points: 16915 More actions · Answer 4

This is a good start! However can you tell me how to have 2 values that do not change and then the other sort values any can selected in any order?

pietlinden SSC Guru Points: 63428 More actions · Answer 5

Woudn't you specify that in your sort list?

Sort by:

column1

column2

SortParameter1

etc?

Then SortParameter1 could be a value list of the remaining column names. (Just build a function to determine the sort order based on the parameter selection.) Same old same old: IIF(Parameters!MyParam="X","X,Y","Y,X")

Hmm... I can read, if I try really hard... that's pretty much what Jason said!

MMartin1 One Orange Chip Points: 27774 More actions · Answer 6

Not sure if you missed it but the sort expression is applied to the table (not the individual columns in case you were thinking that). You set here the column(s) to order by in the desired sequence. The example in the link is a basic and good illustration.

----------------------------------------------------