Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Fetch XML paging cookies and SSRS

  • 1.  Fetch XML paging cookies and SSRS

    TOP CONTRIBUTOR
    Posted Jun 22, 2016 04:24 AM

    Hi,

    We are using CRM Online and I'm trying to design reports on activities around opportunities.  We want to track quote follow up. It's not something I think we can get in an advanced view and then export to Excel, plus I want to set out the report in a way that's presented nicely.

    I'm using Fetch XML builder which is great at helping me find the fields I need and link various tables together.  I'm using the Fetch XML query code it generates in Visual Studio 2012.  The only draw back is that I'm limited to 5000 activity records.

    CRM has returned a paging cookie I can see in Fetch XML Builder but I've no idea how to use this to any effect and page the remaining records so they all show up in the same report.

    Does anyone know if you can page more than 5000 records in SSRS, and if so how to do it?  I've googled this for hours without much success and the information from Microsoft gives solutions written in C# which I don't understand at all.

    I'm out of options for writing this report otherwise I think unless I need to use Power BI instead?  I just struggle with the Option Sets in that.

    Thank you in advance.

    ------------------------------
    Ann Munro
    CRM Manager
    A-Safe
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Fetch XML paging cookies and SSRS

    D365UG/CRMUG ALL STAR
    Posted Jun 22, 2016 10:33 AM

    I don't believe you'll be able to use paging in a SSRS report. 

    Is there come way you can use the aggregate functions of FetchXML to summarize the activity data so you don't need to return 5,000+ records? This could would but that even have a limit of being able to aggregate only up to 50,000 records. 

    ------------------------------
    Jason Lattimer
    Dynamics CRM MVP
    PowerObjects HCL
    Greenville WI

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Fetch XML paging cookies and SSRS

    TOP CONTRIBUTOR
    Posted Jun 22, 2016 11:43 AM

    Thanks Jason, I'll give that a try.  At least I know I can't do it with the paging.

    ------------------------------
    Ann Munro
    CRM Manager
    A-Safe

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Fetch XML paging cookies and SSRS

    TOP CONTRIBUTOR
    Posted Jun 23, 2016 08:54 AM

    I found this article regarding the issue. Cheers!

    When you perform an aggregation, the maximum number that will participate in the aggregation will be 50,000 records. For instance, if you do a COUNT aggregation, the maximum value you can get back from CRM is 50,000 even though that you might have more records in the system. This is a by-design behavior which is for performance reason. This can be overcome by updating "AggregateQueryRecordLimit" setting, however it is generally not recommended.

    http://www.kingswaysoft.com/blog/2013/06/18/Limitations-with-CRM-FetchXML

    ------------------------------
    Rex Tan
    Programmer
    The Davey Tree Expert Company
    Kent OH

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role