ssrs 2008 r2 parameter for sorting

Question

ssrs 2008 r2 parameter for sorting

wendy elizabeth

SSCoach

Points: 16915
More actions
September 12, 2014 at 3:15 pm

#296099

In an ssrs 2008 r2 report, I have a report where a user wants to sort a report based upon the following parameters:
1. lastName,
2. firstName,
3. studentNumber,
4. [School Name].
The above is the default order of how the user wants to be able to sort the report.
The following is the definition and the columns that are to appear in the report:
studentNumber varchar(15)
,lastName varchar(40)
,firstName varchar(35)
,[School Name] varchar(40)
,[School of Assignment] CHAR(3)
,grade varchar(4)
, Description varchar(50)
,[Beginning Date] smalldatetime
The above columns are all contained in one tablix.
The user wants to be able to sort the report by changing the parameters that I just listed
above.
Thus can you show me code and/or point me to a url that would show me how to allow the user to sort
the report in any order based upon the 4 parameters listed above?

Viewing 9 posts - 1 through 8 (of 8 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Some variation of this should work:

http://social.technet.microsoft.com/Forums/sqlserver/en-US/7fff2ba4-a9f5-426b-a022-91772e886728/parameterized-sorting-in-ssrs-2008?forum=sqlreportingservices

the fact that you're sorting by multiple columns might complicate things.

wendy elizabeth SSCoach Points: 16915 More actions · Answer 2

Do you have cod e examples I can see so I can see how to use multiple sort values

pietlinden SSC Guru Points: 63503 More actions · Answer 3

let your fingers do the walking...

http://www.sqlservercentral.com/blogs/juggling_with_sql/2011/12/26/dynamic-sorting-in-ssrs/

Tried it - works a charm. The author even covers ASC/DESC sorts.

This is how I did it:

1. Create your four parameters. I did something completely unoriginal like: Sort1, Sort2...

2. Create a dataset of SortColumns. (SELECT Col1Name UNION ALL SELEcT Col2Name...)

3. In the Tablix, Go to Sorting, and then sort, click FX button and enter

=Fields(Parameters!Sort1.Value).Value Do that for each of the parameters.

This will sort fine if you're sorting in ascending order... Might get a bit more interesting if you wanted to sort one in descending order.

pietlinden SSC Guru Points: 63503 More actions · Answer 4

or this... http://sqlblog.com/blogs/stacia_misner/archive/2011/03/16/34197.aspx

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 5

Doesn't SSRS allow active user sorting on the column. I could swear I have done that before. Interactive sorting on the Text box. Have you played with that at all?

***SQL born on date Spring 2013:-)

pietlinden SSC Guru Points: 63503 More actions · Answer 6

Yes, you can... Must be getting myopic... can't see the obvious answer right in front of me. The hassle with sorting with parameters is that it appears that you can't make them optional.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 7

I think you might want to define the user requirement in a bit more detail, or have the user provide it to you.

Are they looking to:

1. sort the output before it is rendered

2. sort after the output/report is rendered

Option 1: A drop down parameter is added where they select the column they want to sort on and then an additional one maybe giving the option of DESC or ASC. Using this method would involve expressions as the example "pietlinden" provided.

Option 2: Is (again as already mentioned) using the tablix properties on each column enabling sorting. This would be the easiest to implement and maintain. As well gives the user more control over sorting of multiple columns.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Tavis Reddick SSC Eights! Points: 817 More actions · Answer 8

While some of the examples linked to surely work, their presentation is a bit messy.

I knocked up this example based on some kind of solution I half remembered:

DECLARE @sortfruit BIT, @sortamount BIT;

/*test cases*/

--DECLARE @sortfruit BIT = 1, @sortamount BIT;

--DECLARE @sortfruit BIT = 0, @sortamount BIT;

--DECLARE @sortfruit BIT, @sortamount BIT = 0;

--DECLARE @sortfruit BIT = 0, @sortamount BIT = 1;

WITH TestTable AS (

SELECT 1 AS Id, 'apple' AS Fruit, 12 AS Amount

UNION

SELECT 2 AS Id, 'banana' AS Fruit, 6 AS Amount

UNION

SELECT 3 AS Id, 'banana' AS Fruit, 4 AS Amount

UNION

SELECT 4 AS Id, 'cherry' AS Fruit, 25 AS Amount

)

SELECT *

FROM TestTable

ORDER BY CASE WHEN @sortfruit = 1 THEN Fruit END ASC

, CASE WHEN @sortfruit = 0 THEN Fruit END DESC

, CASE WHEN @sortamount = 1 THEN Amount END ASC

, CASE WHEN @sortamount = 0 THEN Amount END DESC;

which in production we would turn into a stored procedure. The parameters would be of BIT type, with NULL meaning no sort, 1 ascending, 0 descending.

This example is limited in the sense that you cannot change the column order though.