How to get the data from the Fetch XML on the basis of Month in SSRS Report?

Question

I have to fetch the data using FETCH XML where parameter will be year and the field in my CRM is date time field. So is there any way to fetch the data according to month.

For Example : Launch Date : 12/02/2016 Parameter for Report : 2016

Then I have to get the count according to month so as per example February count will be 1.

I have attached the Image for more understanding.

Answer 1

It's not possible, as far as I know, to filter a query using only the year so a possible workaround is to create a start date (01/01/YYYY) and a end date (31/12/YYYY) and use the "On or after" and "On or before" operators. In order to get the count per month, you can use the dategrouping paramenter.

The following is an example (contacts created by month in 2016):

<fetch aggregate="true" >
  <entity name="contact" >
    <attribute name="createdon" alias="month" groupby="true" dategrouping="month" />
    <attribute name="contactid" alias="count" aggregate="count" />
    <filter>
      <condition attribute="createdon" operator="on-or-after" value="01/01/2016" />
      <condition attribute="createdon" operator="on-or-before" value="12/31/2016" />
    </filter>
  </entity>
</fetch>