Data Not Getting Ordered BY in my Report

Question

I have a dataset which gets populated using a query with a OrderBy Statement, i then am trying to use that dataset for display on my report but the data is not getting Ordered by Correctly.

The Query Used to Populate the DataSet

SELECT        Month, TotalWeek, Date, [SumOfRM Lbs Saved], [SumOfRM Savings $]
FROM            RMSavingsByDate
ORDER BY Date

as you can see the date starts off from 1/2004 on my report and not 3/1932 how would i go by fixing this issue any help would be greatly appreciated.

Edit

Below is the SQL view that i am using to populate the data set

SELECT     TOP (100) PERCENT CAST(DATEPART(mm, Date) AS VarChar) + '/ ' + CAST(DATEPART(yyyy, Date) AS VarChar) AS Month, DATEPART(wk, Date) AS TotalWeek, Date, 
                      SUM([RM Lbs Saved]) AS [SumOfRM Lbs Saved], SUM([RM Savings $]) AS [SumOfRM Savings $]
FROM         dbo.[Net Weight Tracking Query]
GROUP BY Date
ORDER BY Date

Answer 1

I recently encountered a similar issue even though my query was returning the data in proper order. If your report is based on a .rdlc report, and your report has data grouping, The SORTING section will supersede whatever your querying data pulls down.

If so, go to your report and look at the "Row Groups" area of the report and then right-click for "Group Properties". The "General" setting shows the "Group On" choices. From there, confirm the grouping, then look at the "Sorting" tab.

The sorting apparently occurs within each group, so whatever your data had come to the report with is now being sorted per the report requirements.

If your data grouping is based on the month plus year, and you have data for 3/1932, that would appear when the report gets to month 3. What you MAY need to do is adjust the report to order by YEAR AND MONTH, so the 1932 entries come in first, then by proper month within each year.