How data can be sorted on date field in SSRS report in Dynamics CRM?

Introduction

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.

Understanding Concepts

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

dataset-fields

  • If you run the simple query (FetchXml of Open Opportunity view) in Query Designer, you will see data in below way.

query-designer-output

  • 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:

tablix-sorting-configuration

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s