14 Dec’16

BISorting 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.

Written by

Team Member


Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Managing mailbox through Email Archiving

    20 March’ 2018

    Introduction: Email can fill your Outlook Inbox quickly – new messages, replies, and forwards. Before you know it, yo...

    Read more
  • Workaround to Report.SAVEASPDF in NAV 2018

    20 March’ 2018

    Objective: In NAV 2017 Emailing the PDF by running the report using Report.SAVEASPDF is now not allowed in NAV 2018. Th...

    Read more
  • Dynamics 365 Client Diagnostics

    15 March’ 2018

    Main cause of Performance Issues: Bandwidth and latency are the primary characteristics which affects the performance o...

    Read more
  • Closing Blind Shift in MPOS and CPOS in Dynamics 365 for Retail

    15 March’ 2018

    Manual For closing Blind shift in POS: Blind shifts terminate the currently ongoing shift and when logged in a new shif...

    Read more
  • Persistent Filters in the Power BI Service

    13 March’ 2018

    Introduction: The feature is finally release, and it is power BI has announced general availability of persistent filt...

    Read more