How to fetch more than 5000 rows in JSON using fetch xml

[praneshJ]

Hello Experts,

I have a report on one of the web pages, on the report I have provided the selections to end users so that they can select the KPIs and can see the desired data they want to see on the report.

On the web page I have ajax call which fetches the data from CDS table using web template and form a JSON and return it in the response.

Now when user is selecting the KPIs from the selections as shown below ,

for the given date range and combination of selections, data in the corresponding CDS table has more than 6300+ rows

but the JSON is returning only 5000 nodes in the response.

anything beyond 5000 nodes are not getting returned, hence the further calculation going wrong.

Please advice how to return more than 5000 nodes in the JSON response, any possible workaround for this is really appreciable.

Regards,

Pranesh 

[justinburch]

Hi @praneshJ,

You'll need to utilize the Paging Cookie. Colin Vermander has a great breakdown here: Dynamics 365 portal: Use liquid fetchxml with paging cookie – Colin Vermander (wordpress.com)

[praneshJ]

Hello @justinburch,

Thanks for your reply, the article given is helpful for displaying the data on the web-page,
But I want to perform calculations on the data and display summarised data on the web page.
so I want to return more than 5000 rows in JSON response,

what are the best possible ways to retrieve the more than 5000 rows from CDS tables.

Regards,

Pranesh 

[OliverRodrigues]

hi @praneshJ I have the feeling you might need to reach out to Microsoft via Support Ticket for this one. 

[justinburch]

Hi @praneshJ,

You would setup your JSON retrieval to take advantage of the paging cookie and iterate through the results.

1. Use your API to query for the results, return that there's a paging cookie in your JSON response
2. Store the results and, through the API, query for the next pageset
3. In the same code block, after successful retrieval, do calculations

Note that needing to also do calculations would put effort client-side that might not be ideal and these iterative queries will most likely impact user experience. You might want to shift the server-side - currently, the way to do this securely would be to create a Web App that can validate the Oauth token against your portal. You might be able to leverage Azure Functions and Logic Apps to do the same, but I haven't personally seen anything around this. Either way, you'd have this app do the queries and calculations and return the results.

[OOlashyn]

Hi @praneshJ,

Checkout reply above from @justinburch if you have some extensive calculation. If you want to do the simpler calculation (like sum, count, average) you can benefit from fetchxml aggregation (the system will aggregate results for you and return calculated values). Just create a web template to return aggregated results. Note that fetchxml aggregation has a performance limit of 50k records.

[praneshJ]

Hello @OOlashyn ,

Thank you for your reply, I have gone through given fetchxml aggregation link and found that there are certain aggregation functions but the code given for the reference is in C# language.

My question is How can I write the C# code in my web Template? where I am using the logic to fetch the records from the entity, please refer my web template syntax, 

{% assign StartDate = request.params.StartDate %} 
{% assign EndDate = request.params.EndDate %} 


{% fetchxml feed %}
  <fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
  <entity name='entity name'>
     <attribute name='Col1' />
	 <attribute name='Col2' />
	 
   <link-entity name='Entity name2' from='abc' to='Col1' alias='test_alias'>
	<attribute name='marks' />
	<attribute name='Stdate' />
	<attribute name='enddate' />
	<attribute name='stdId' />
	<attribute name='name' />
		
		<filter type='and'>
		<condition attribute='cr8e7_activitydate' operator='ge' value='{{StartDate}}' /> 
		<condition attribute='cr8e7_activitydate' operator='le' value='{{EndDate}}' />   
		</filter>
	</link-entity>
  </entity>
</fetch>
{% endfetchxml %}   
{
  
    "stddata": [
    
    {% for item in feed.results.entities %} 
      {       
        "Id": "{{ item['test_alias.id']}}",
		"Name": "{{ item['test_alias.name']}}"       
      }{% unless forloop.last %},{% endunless %}
    {% endfor %}
  ]
  }
  

 Thanks for you time, I really appreciate your help.

Regards,

Pranesh 

[OOlashyn · Accepted solution]

Hi @praneshJ,

You don't need and cannot write C# code in the web template. Just see what fetchxml query they are performing (see attached image). Also, you can use FetchXmlBuilder from XrmToolBox to generate and test fetchxml queries with aggregation. For example, you want to count the number of entity 2 with distinct names 

{% assign StartDate = request.params.StartDate %} 
{% assign EndDate = request.params.EndDate %} 

{% fetchxml feed %}
  <fetch version='1.0' output-format='xml-platform' mapping='logical' aggregate="true" >
  <entity name='entity name'>
   <link-entity name='Entity name2' from='abc' to='Col1' alias='test_alias'>
	<attribute name='name' alias='count_name' aggregate='countcolumn' distinct='true'/>
        <filter type='and'>
		<condition attribute='cr8e7_activitydate' operator='ge' value='{{StartDate}}' /> 
		<condition attribute='cr8e7_activitydate' operator='le' value='{{EndDate}}' />   
		</filter>
	</link-entity>
  </entity>
</fetch>
{% endfetchxml %}   
{
  
    "stddata": [
    
    {% for item in feed.results.entities %} 
      {       
        "Number_Of_Distinct_Names": "{{ item['test_alias.count_name']}}"  
      }{% unless forloop.last %},{% endunless %}
    {% endfor %}
  ]
  }

[praneshJ]

Hello @OOlashyn ,

Thank you for your reply,

Its working for me, I am but confused about group by in fetchxml, I have written below code and its throwing an error "Exception has been thrown by target invocation"

what went wrong in my code I am not getting, please refer code,

{% assign StartDate = request.params.StartDate %} 
{% assign EndDate = request.params.EndDate %} 

{% fetchxml feed %}
 
<fetch version='1.0' output-format='xml-platform' mapping='logical' aggregate="true" >
<entity name='cr8e7_stc_studentactivity'>
          <attribute name='cr8e7_attendance' alias='count_attendance' aggregate='countcolumn' />
          <attribute name='cr8e7_activitydate' alias='activityDate' groupby='true' />
          
      <filter type='and'>
            <condition attribute='cr8e7_activitydate' operator='ge' value='{{StartDate}}' /> 
            <condition attribute='cr8e7_activitydate' operator='le' value='{{EndDate}}' />
		  </filter>
    
    </entity> 
</fetch>
{% endfetchxml %}   
{
    "testdata": [ 
     {% for item in feed.results.entities %}
      {
        "no_of_attendance": "{{ item.cr8e7_attendance }}"
      }{% unless forloop.last %},{% endunless %}
    {% endfor %}
  ]
  }
  

 and Please provide me one example, where I can show group by data over 4 columns and return in json.

I really appreciate your time.

Regards,

Pranesh

[OOlashyn · Accepted solution]

Hi @praneshJ ,

Your code is throwing an error because you are trying to access the field by its name and not an alias. When you performing grouping you need to use the alias, so instead item.cr8e7_attendance you need to write item.count_attendance. 

Below you can find an example of grouping by year. Each result will represent a year and corresponding number of attendance.

{% assign StartDate = request.params.StartDate %} 
{% assign EndDate = request.params.EndDate %} 

{% fetchxml feed %}
 
<fetch version='1.0' output-format='xml-platform' mapping='logical' aggregate="true" >
<entity name='cr8e7_stc_studentactivity'>
          <attribute name='cr8e7_attendance' alias='count_attendance' aggregate='countcolumn' />
          <attribute name='cr8e7_activitydate' groupby='true' dategrouping='year' alias='year' />
          
      <filter type='and'>
            <condition attribute='cr8e7_activitydate' operator='ge' value='{{StartDate}}' /> 
            <condition attribute='cr8e7_activitydate' operator='le' value='{{EndDate}}' /> 
 </filter>
    
    </entity> 
</fetch>
{% endfetchxml %}   
{
    "testdata": [ 
     {% for item in feed.results.entities %}
      {
        "no_of_attendance": "{{ item.count_attendance}}",
        "year": "{{ item.year}}",
      }{% unless forloop.last %},{% endunless %}
    {% endfor %}
  ]
  }

Can you elaborate a bit would do you mean grouping by data over 4 columns? You want to have 4 distinct groups?