This is a short and simple article which explains how we can avoid sorting issues on date field in SSRS report in Dynamics CRM Fetch Based report.
- When we write fetch based SSRS report for Dynamics CRM, for complex field type, we always get 2 attributes in SSRS dataset. E.g. Lookup, Option Set, Date and Time, Currency etc.
- From above 2 attributes, one contains the display string and other contains data value.
- In Case of Date and Time field, as per below screenshot, we got 2 attributes in SSRS dataset for Est. Close Date field which is of type Date and Time.
- If you run the simple query (FetchXml of Open Opportunity view) in Query Designer, you will see data in below way.
- As we have display data and value data in separate field, for display purpose we should use display field (estimatedcloseddate in this case) & for data manipulation we should use data field (estimatedclosedateValue in this case).
How to sort data in Tablix based on date field?
- As we have seen above how the dataset contains the data, we have to select data field in sorting expression to sort the data in required order.
- To achieve this, go to Tablix properties and Sorting section and configure as per below screenshot:
Note: The Order (A to Z / Z to A) is just to determine Ascending or Descending order. It does not mean we have to provide only string type fields in Sorting Column. SSRS understands the data type and sorts the data accordingly.