Sorting based on Date does not work in CRM based SSRS report

Fetch XML based SSRS reports which are deployed in Microsoft Dynamics CRM online.

Case: In Report, you want to sort the Tablix date as per the date field in descending order. (In the below example “Date” column). When you deploy the report and run the report, it is observed that date order is not as expected.

Resolution / Work around Steps:

1. Login to CRM and verify the default formatting for Dates. (Settings -> Administartion -> System Settings -> Formats.

   Check how the Short date is displayed. (By Default todays date will be displayed) Here the format is MM-dd-YYYY. So we know that the data is stored in what format of Date.

2. Also as a best practice, we should check if the date field is empty and handle the same and format the date field. I used below expression to achieve this.

   =IIF(ISNOTHING(Fields!cf_calibrationdate.Value) Or Fields!cf_calibrationdate.Value = "", " ", Format(Cdate(Fields!cf_calibrationdate.Value), "MM-dd-yyyy"))





3. Now Go to Report designer in SQL Server Date Tool and Go to Tablix Properties and Go to Sorting.

   You need to add the sorting options through expressions. Now the trick is since it’s a date, we will sorting using the value field as below.

   

You can run the report in preview window and verify if the Tablix data is sorted as per the date field.