Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Grouping DateTime field in FetchXML RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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