Sorting with SQL Order By clause

[traceytr]

I'm brand new to SSRS, and I am using a SQL query as the dataset for my report. I have an Order By clause in the SQL which seems to have no affect on the order the records are shown in the preview. I've tried adding a group to the report to order it that way, and again it seems to have no affect. Is there something else I'm supposed to do for sorting in SQL Server Reporting Services? I've checked the forums, and I haven't seen this addressed. Thanks.

Tracey

 

[unclerico]

can you post your query along with some sample data for both what you are getting and what you should be getting??

 

[traceytr]

Here’s a condensed version of the SQL.

SELECT selectedSampleGroup, loanNo, loanAmount, costCenter, region,
convert(char(10),noteDate, 101) AS Note_Date,
convert(char(10),renewalDate, 101) AS Renewal_Date,
<at least 50 more fields listed>,
dateEnteredInDB
FROM OnlyOneTable
WHERE (MONTH(@ReportMonthYear) = MONTH(LoanInformationOriginal.dateEnteredInDB)) AND (YEAR(LoanInformationOriginal.dateEnteredInDB) = YEAR(@ReportMonthYear))
order by selectedSampleGroup, loanNo

When I run this, I’m asked to provide a value for @ReportMonthYear. I enter 03/01/2006, and the query generates the recordset correctly ordered by the fields selectedSampleGroup, loanNo.

When I preview the report, the records are not ordered by these fields. I have also tried grouping on these fields, and I get the same incorrect results. I thought this would be the easy part. Thanks.

 

[traceytr]

Sorry I cannot post sample data. Our test data for this is a copy of the real stuff.

 

[unclerico]

the only thing that I can think of off the bat would be that you have some other sorting going on in the designer which would override the sort in your query...does this make sense??

 

[traceytr]

It does make sense, but I have not added any other sorting in the report designer that I'm aware of. I have tried unsuccessfully to sort by adding some grouping, but when that was unsuccessful I rolled back to the earlier version of the report without the grouping. hmmmm... This is a puzzle. Thanks.

 

[xlbo]

how is the data displayed in your report ?? in a list, a table, a matrix ??? You will need to add a sort to the control that is displaying the data

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[henslecd]

What is the data type of the order by fields? This can play a significant role in determining order. I.e. if the data type is varchar, 10000000 comes before 5, 200, 4000. But if the data type is int, the order would be 5,200,4000,10000000.

 

[traceytr]

When I right click on the body of the report, there are a couple of menu items which say 'Select table1' and 'Select List1'. I selected List1, and sure enough in the Properties there was an item called Grouping. I opened the Grouping and Sorting properties there, and it was sorted on loan number only, not the sort I was trying to get by selectedSampleGroup, loanNo. When I changed the sorting here, the report sorts correctly.

I did not intentionally create List1 or set up this Grouping and Sorting on loanNo property in List1, but I must have inadvertently done it at some point...

I started off using the Report Wizard to build the bare bones of the report, and I did use a shared Data Source.

Thanks. I appreciate all of your help.

Tracey