January 28, 2015 at 1:48 am
I am a bit rusty and would be very grateful if you could help. I am using Reporting Services. I am using T-SQL. The date_ordered field mentioned below is data type varchar.
I have a simple report that gives the date_ordered value. Unfortunately, when I ORDER BY date_ordered DESC, it sorts the date by the numerical value of the day, so 31-Oct-14 appears first and 01-Apr-13 last.
I would like the report to show most recent dates first and end with the last date ordered.
I'm sure it's very obvious, but I can't see it.
With many thanks.
January 28, 2015 at 7:21 am
Change your T-SQL
ORDER BY CAST(date_ordered as date) DESC
(or cast to datetime of pre 2005)
Far away is close at hand in the images of elsewhere.
Anon.
January 28, 2015 at 7:40 am
Thanks for getting back.
Worked perfectly.
January 29, 2015 at 6:51 pm
Hi faulknerwilliam2,
Hi. I hope/pray you are doing great upon reading this reply.
As an alternative, you may sort the data directly to your RDL. To do that, please highlight the entire row of your table (the one that will show the data/ result set, do not include the table header), then right click:
Choose Tablix Properties...
Then on the dialog box, kindly choose Sorting on the left side of choices (other choices are General, Visibility, Filters).
Then on the left side there is a choice to Sort By, it is a drop down button in which you could choose your field named [date_ordered].
Then click OK button.
Note: If you will try to do this, you may want to remove the [font="Courier New"]ORDER BY[/font] clause in your T-SQL. The advantage of this is that it will sort the data right after the RDL fetched the result set, thus, it will somehow lessen the "burden" in your database I think, but I could be wrong, just an opinion. 😀
Thank you and Godspeed.
Respectfully Yours,
Mark Squall
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
January 30, 2015 at 3:45 pm
Many thanks marksquall for taking the trouble to get back.
Your suggestion worked well and it is good to know another way to solve the sorting issue I had.
February 2, 2015 at 1:20 am
marksquall (1/29/2015)
Then on the left side there is a choice to Sort By, it is a drop down button in which you could choose your field named [date_ordered].
date_ordered is a varchar column so it requires converting before sorting to prevent alphabetic sort.
Far away is close at hand in the images of elsewhere.
Anon.
February 25, 2015 at 7:32 pm
Dear faulknerwilliam2,
Hello, a pleasant day to you. 🙂
David Burrows is right, I encountered the same thing and I would like to thank David Burrows for the information. Yes, if the column [font="Courier New"][date_ordered][/font] is [font="Courier New"]VARCHAR[/font] type it would sort in alphabetical order.
As a workaround to this:
In the RDL, right click the cell where [font="Courier New"] [date_ordered][/font] is located...
Choose Expression...
In the big textbox under Set Expression for: Value, please type:
=FORMAT(CDATE(Fields!date_ordered.Value), "dd-MMM-yy")
Then click OK.
The column will now display [font="Arial Narrow"]<<Expr>>[/font] on the column to indicate that the column has an expression, conditional statement, etc.
I hope you find David Burrows (and mine too) tips useful in your future RDL development.
Thank you all and Godspeed. 🙂
Warmest regards,
Mark Squall
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply