locked
Grouping DateTime field in FetchXML RRS feed

  • Question

  • Is it possible for FetchXML to return the full date when you group by  date time field.

    For example, the following FetchXML should return the 11/1/09 on billingstarton rather than 1

    <?xml version="1.0" encoding="utf-16"?><fetch xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" aggregate="true">
    <entity name="contract">
    	<attribute name="billingstarton" alias="billingstarton" groupby="true" dategrouping="day" />
    	<attribute name="title" alias="title" groupby="true" />
    	<attribute name="netprice" alias="netprice" aggregate="sum" />
    	<filter type="or">
    		<condition attribute="billingstarton" operator="on" value="11/01/09" />
    	</filter>
    </entity>
    </fetch>

    Thanks

    Leo

    Tuesday, April 2, 2013 7:27 AM

Answers

  • I think the way to do this is to add additional attributes to group on the month and the year:

    <fetch>
    <entity name="contract">
    	<attribute name="billingstarton" alias="billingstartonyear" groupby="true" dategrouping="year" />
    	<attribute name="billingstarton" alias="billingstartonmonth" groupby="true" dategrouping="month" />
    	<attribute name="billingstarton" alias="billingstartonday" groupby="true" dategrouping="day" />
    	<attribute name="title" alias="title" groupby="true" />
    	<attribute name="netprice" alias="netprice" aggregate="sum" />
    	<filter type="or">
    		<condition attribute="billingstarton" operator="on" value="11/01/09" />
    	</filter>
    </entity>
    </fetch>


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Tuesday, April 2, 2013 2:36 PM
    Moderator

All replies

  • I think the way to do this is to add additional attributes to group on the month and the year:

    <fetch>
    <entity name="contract">
    	<attribute name="billingstarton" alias="billingstartonyear" groupby="true" dategrouping="year" />
    	<attribute name="billingstarton" alias="billingstartonmonth" groupby="true" dategrouping="month" />
    	<attribute name="billingstarton" alias="billingstartonday" groupby="true" dategrouping="day" />
    	<attribute name="title" alias="title" groupby="true" />
    	<attribute name="netprice" alias="netprice" aggregate="sum" />
    	<filter type="or">
    		<condition attribute="billingstarton" operator="on" value="11/01/09" />
    	</filter>
    </entity>
    </fetch>


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Tuesday, April 2, 2013 2:36 PM
    Moderator
  • Thank you, this is the way to do it. I needed help in the same situation and got it with your reply.

    Thanks a lot.

    One more thing, in order to show the full date in the report, we need to make it as an expression and manually make it like:

     

    =Fields!billingstartonyear.Value +

    "/" + Fields!billingstartonmonth.Value + "/" + Fields!billingstartonday.Value

    • Edited by SunilRah Monday, September 9, 2013 11:49 PM t
    Monday, September 9, 2013 11:46 PM
  • In the designer select the date field and in properties you can set the desired format as most of the format are already availabale.

    Regards Faisal

    Tuesday, September 10, 2013 2:23 PM