table order by date column

Question

I am using the BIDS extension in Visual Studio 2008 to build Reports for Dynamics CRM 2011 On-Premise. I am using FetchXML based reports. I have the following problem : I have a table with 4 columns. One of this columns is filled out with the Starting Date of the appointment Entity in CRM. The date is in the format of : "dd/MM/yyyy hh:mm". I need to sort all the table data based on this column in the ascending order. I tried every type of sorting with FormatDateTime, Format, Sorting the whole table or sorting the Group of data, but I can't get them to sort correctly because only the first value is considered, only days....

This means that it considers as a previous date for example 01/04/2013 instead of 06/12/2012 because the first value "01" is lower then "06".

Can anyone help me to find the exact function to use to sort this table correctly ?

Answer 1

Is that possible to get the data sorted already by using T-SQL (ORDER BY [Starting Date])?

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 2

Hi,

Please use this expression in Sorting option of your tablix's property:

=Day(Fields!sartdate.Value)

as

I hope it will work.

Thanks

Avanish T

Answer 3

Hi Uri,

Sorry but what do you mean ? As I said, I am using FetchXML for the reports. If your suggestion can be done also in the FetchXML case then please provide me more details on how to do it.

Thank you

Answer 4

Hi,

Hope the date field which you are getting from the data set is Date Type only if so direct field (i.e column under data set) sorting expression on table control should work for you.

If this is not working in your case, then try this

try adding below expressions one by one under table properties > sorting options.

=year(Fields!sdate.Value)

=month(Fields!sdate.Value)

=day(Fields!sdate.Value)

 

This should solve your issue if not please feel free to revert back, thank you..

---

Cheers, Arun Gangumalla Please mark as helpful or answered if it resolves your issue to help others in finding solutions easily.

Answer 5

Hi Avanish,

I tried your suggestion but still that is not sorting correctly the dates. Here is the result that I get using your suggestion :

Thank you,

Ermal

Answer 6

Hi Arun,

Tried that but didn't work. I still get messed up results. I am also open to formatting the field differently, by removing the time and leaving only the date if that makes it simpler.

Thank you

Answer 7

Hi Arun,

Tried that but didn't work. I still get messed up results. I am also open to formatting the field differently, by removing the time and leaving only the date if that makes it simpler.

Thank you

Answer 8

Hi,

Ok then please give a try with same fields under table properties > sorting, but with format expressions and check out if it solves your issue.

=cdate(Fields!sDate.Value).tostring("dd/MM/yyyy")

if the date field which you are getting from data set is of type String then try with only =cdate(Fields!sDate.Value)

 

Please feel free to revert back if any issues still, thank you

---

- Arun Gangumalla, Please mark as helpful or answered if it resolves your issue to help others in finding solutions easily.

Answer 9

Hi,

You can sort on the following expression

Mid(X,7,4) + Mid(X,4,2) + Left(X,2) + Mid(X,12,2) + Right(X,2)

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 10

Hi,

Is the date in the format of 'DD/MM/yyyy' or 'MM/DD/YYYY'?

Thanks

Avanish

Answer 11

Hello,

Based on your description, the date is in the format of "dd/MM/yyyy hh:mm". In order to meet your requirement, you can format the data type of the field values to “MM/dd/yyyy hh:mm” before apply the sort function in Tablix property. Please try the following expression for sort value:
=Foramt(Fields!data.Value,”MM/dd/yyyy hh:mm”).

Regards,
Fanny Liu

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Fanny Liu
TechNet Community Support

Answer 12

Thank you Arun,

Both your suggestions did the trick. First I changed the type of the field in Date Type instead of General and then used just =cdate(Fields!sDate.Value) in the sorting properties of the table. That sorted out the dates perfectly, including the hours in case there were 2 dates in the same day.

Thank you all for your suggestions.

Ermal