locked
Fetchxml cast date RRS feed

  • Question

  • Hello,

    I'm using SSRS and fetchxml for do some reports in mscrm 2011.
    I need get the total of appointments completed group by day.
    I did that: 

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">
      <entity name="appointment">
    <attribute name="scheduledstart" aggregate='countcolumn' alias='datecount' distinct='true' />
    <filter type="and">
    <condition attribute="ownerid" operator="eq" value="@Filter_User" />
    </filter>
      </entity>
    </fetch>

    But "scheduledstart" is a DateTime field and i need only the Date.

    I want that (in SQL):

    SELECT COUNT(DISTINCT(CAST(ScheduledStart AS DATE))) AS ScheduledStart
    FROM ActivityParty
    WHERE OwnerId = '@Filter_User';

    Any ideas?

    TY ^^.


    • Edited by alexkriter Thursday, September 5, 2013 10:51 AM
    Thursday, September 5, 2013 10:01 AM

Answers

  • I just saw the answer, but I have refresh the page and flew: |

    Solution:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">
      <entity name="appointment">
    <attribute name='activityid' alias='appointment_count' aggregate='count'/> 
    <attribute name="scheduledstart" groupby='true' dategrouping='day' alias='day' />
      </entity>
    </fetch>

    Ty so much.

    • Marked as answer by alexkriter Thursday, September 5, 2013 2:48 PM
    Thursday, September 5, 2013 2:48 PM
  • Took it off because I thought your problem was with displaying the date format.

    Glad to help.

    • Marked as answer by alexkriter Friday, September 6, 2013 8:52 AM
    Thursday, September 5, 2013 3:26 PM

All replies

  • Hi

    Try like This

    SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

    Thursday, September 5, 2013 2:01 PM
  • Hi

     you can change it in field "text Box Properties

    like

    Thursday, September 5, 2013 2:32 PM
  • I just saw the answer, but I have refresh the page and flew: |

    Solution:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">
      <entity name="appointment">
    <attribute name='activityid' alias='appointment_count' aggregate='count'/> 
    <attribute name="scheduledstart" groupby='true' dategrouping='day' alias='day' />
      </entity>
    </fetch>

    Ty so much.

    • Marked as answer by alexkriter Thursday, September 5, 2013 2:48 PM
    Thursday, September 5, 2013 2:48 PM
  • Took it off because I thought your problem was with displaying the date format.

    Glad to help.

    • Marked as answer by alexkriter Friday, September 6, 2013 8:52 AM
    Thursday, September 5, 2013 3:26 PM