locked
FetchXML is only returning 500 records RRS feed

  • Question

  • I have a SSRS report.  I have taken the FetchXML from Advanced Find.

    My Tablix is only displaying 500 records.It's driving me nuts, any ideas?

    The Tablix is doing a count on the number of records created per Hour.  If it were per Day I would be fine because I can group in the FetchXML (I have no idea why MS included Date Grouping in FetchXML but not DateTime Grouping as that would be the most efficient method of getting the data).

    Wednesday, February 20, 2013 11:10 AM

Answers

  • Hi,

    Try add the attribute count to your fetch node, like that:

    <fetch distinct="false" count="5000" page="1" no-lock="false" mapping="logical">
    Hope this helps.
    If i answered your question, please mark the response as an answer and also vote as helpful.


    Pedro Azevedo Crm Specialist 4.0\2011

    Wednesday, February 20, 2013 1:13 PM

All replies

  • Hi,

    Is only 500 records or 5000 records. There is now a limitation os FetchXml only returns 5000 records. To enable more records:

    Add DWORD key called TurnOffFetchThrottling and set value to 1 to HKLM\Software\Microsoft\MSCRM

    Hope this helps.
    If i answered your question, please mark the response as an answer and also vote as helpful.


    Pedro Azevedo Crm Specialist 4.0\2011

    Wednesday, February 20, 2013 12:42 PM
  • It's only 500 which is a strange round number, hence my confusion.

    I am running inside BIDS 2008 so haven't tried it within CRM yet..

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="false">
      <entity name="new_entity">
        <attribute name="new_entityid" />
        <attribute name="createdon" />
        <order attribute="createdon" descending="true" />
        <filter><condition attribute="createdon" value="@StartDateFormatted" operator="on"/></filter>
      </entity>
    </fetch>


    Wednesday, February 20, 2013 12:52 PM
  • Hi,

    Try add the attribute count to your fetch node, like that:

    <fetch distinct="false" count="5000" page="1" no-lock="false" mapping="logical">
    Hope this helps.
    If i answered your question, please mark the response as an answer and also vote as helpful.


    Pedro Azevedo Crm Specialist 4.0\2011

    Wednesday, February 20, 2013 1:13 PM
  • That has certainly get me beyond 500 records, thanks.

    I have done quite a lot of reports and never had to put in the count before unless I've been looking to show a Top 10 or something like that.

    Now I've hit the 5000 record limit, is there any way to beat that in BIDS.  Ultimately my table is showing 24 rows (one for each hour) and a total number of records created within each hour.  Seems strange that I can't seem to do that.

    Wednesday, February 20, 2013 1:38 PM
  • Hi,

    I search but i can't find any option for that, you have to do that in every report. But check my first option if change anything, check here the KB.

    PS: If you consider that i answer to your question, can you mark as a answer. Thanks.

    Hope this helps.
    If i answered your question, please mark the response as an answer and also vote as helpful.


    Pedro Azevedo Crm Specialist 4.0\2011

    Wednesday, February 20, 2013 2:10 PM
  • It's CRM Online so I guess I'll have to do something else for this. report

    G

    Wednesday, February 20, 2013 2:28 PM
  • Sorry that I'm late to the party....

    VS SSRS will only retrieve ONLY 500 Rows to save on development time.

    Move the RDL to CRM and try it and it will work.

    This frustrates me every so often when I have date parameters that vary

    the results returned but never more than 500 total.

    Try it and see if this works for you (you probably already have done that!)

    Best of luck!

    Tuesday, November 28, 2017 4:10 PM