CODEWITHSUNDEEP
sortingrdlcreporting-services
Adam
Adam

Reputation: 5

Why does my SQL Server Reporting Service (SSRS) Report 'appear' to re-sort the data?

The SSRS report in question is a client report file (.rdlc) and is created by 1) Querying the database and applying the returned populated System.Data.DataTable object to the report; 2) Calling the Export method on the report object in-order to create a final PDF file.

Problem: The query sorts the data in one way, and the report sorts it in another!

I know that an SSRS report does not support sorting, as that's up to the query, hence my confusion!

I have used debug breaks to view the correctly sorted DataTable turn into an incorrectly sorted PDF. (Aaarrgghh)

The report simply lists products, their stock quantities, and their category e.g. Red Wine, White Wine, etc.

... and interestingly the PDF is sorted in what appears to be alphabetical order based on the first alphanumeric column of data, which is the third physical column called "Category".

I am using C#.NET 4 and Visual Studio 2010.

Upvotes: 0

Views: 6547

Answers (3)

tunnelview
tunnelview

Reputation: 47

SSRS can be demanding, when we include an SQL query in the report, even if we have sorted the data in descending or ascending(by default), you will not see the data sorted in the report. This is true in my experience for a paginated report,where the data is sorted in the query and when I ran the query in SSMS, SQL server provides the data in a sorted manner but the same query doesn't sort the data in my SSRS report hence I got confused. You need to right click on the tablix, click on properties, choose the fourth option 'sorting' and choose the column, "Sort By" and "Order" you want to sort it by. If you like to add multiple sorting options, you can click on add and add multiple sorting options. Click on preview to check if the change has come to effect and then deploy or redeploy the report.

Upvotes: 0

da Bich
da Bich

Reputation: 536

by the way.. in case anyone else gets here and this isn't the solution.. I am using a report designer in VS (not SSRS..but it seems very similar) there is also the option to sort within your group. Ie. in the Row Groups window (or column groups).. right click on the group, and select "Group Properties" There is a 'sorting' section there as well, which might alter your expected results..

Also, I wonder if a dataset query is sorted.. but then a group is specified, the order might then get altered just due to the grouping? I haven't tested that - but just wondering..since the group might actually aggregate over the sorted values in the dataset...

Upvotes: 0

Chris Latta
Chris Latta

Reputation: 20560

Your problem is your belief that "an SSRS report does not support sorting" - simply not true. You can sort data within the report. This is done through the properties of the Table, not with the Dataset properties as one might think.

Click anywhere on the table, right-click the top-left box and select Properties. This will display the properties for the entire table with the fourth tab being "Sorting". You should find that this section has been filled in for your report.

If this section is not filled in, then the output will be in the order of the results of the query for the dataset. However, it can be overridden using the table properties so that the one query can support different reports with different sorting and grouping.

Upvotes: 4

Related Questions