Question Status

Verified
Julian Madrid asked a question on 26 Jun 2012 10:06 AM

Hi.

In CRM2011, when I build a chart (Bar) to a dashboard from a table with more than 55,000 records I get the following error message: "Maximum limit is exceeded record. Reduce the number of records"

The FetchXML of chart is as follows. How I can fix this problem without affecting the performance.

 

Thanks.

 --------------------------------------------

<visualization>
  <visualizationid>{xxxxx-xxxxx-xxxxx-xxxxx-xxxxx}</visualizationid>
  <name>EstadoP</name>
  <primaryentitytypecode>contact</primaryentitytypecode>
  <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="contact">
            <attribute groupby="true" alias="_CRMAutoGen_groupby_column_Num_0" name="new_estadoparticipante" />
            <attribute alias="_CRMAutoGen_aggregate_column_Num_0" name="fullname" aggregate="count" />
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category alias="_CRMAutoGen_groupby_column_Num_0">
          <measurecollection>
            <measure alias="_CRMAutoGen_aggregate_column_Num_0" />
          </measurecollection>
        </category>
      </categorycollection>
    </datadefinition>
  </datadescription>
  <presentationdescription>
    <Chart Palette="None" PaletteCustomColors="149,189,66; 197,56,52; 55,118,193; 117,82,160; 49,171,204; 255,136,35; 168,203,104; 209,98,96; 97,142,206; 142,116,178; 93,186,215; 255,155,83">
      <Series>
        <Series ChartType="Bar" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40">
          <SmartLabelStyle Enabled="True" />
        </Series>
      </Series>
      <ChartAreas>
        <ChartArea BorderColor="White" BorderDashStyle="Solid">
          <AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
            <MajorGrid LineColor="239, 242, 246" />
            <MajorTickMark LineColor="165, 172, 181" />
            <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
          </AxisY>
          <AxisX LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
            <MajorTickMark LineColor="165, 172, 181" />
            <MajorGrid LineColor="Transparent" />
            <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
          </AxisX>
        </ChartArea>
      </ChartAreas>
      <Titles>
        <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59"></Title>
      </Titles>
    </Chart>
  </presentationdescription>
  <isdefault>false</isdefault>
</visualization>

-----------------------------------------------------

 

Reply
Roger Collins responded on 26 Jun 2012 12:30 PM

on premise or crm online? Iif on-premise you can change a setting on the server to increase limit.

Reply
Julian Madrid responded on 26 Jun 2012 1:58 PM

Thaks for reply.

Its on-premise. How change this settings?

Thx.

Reply
Dejavu M responded on 20 Sep 2012 1:48 PM

Hello ,  we are getting an error message " The maximum record Limit is exceeded. Reduce the  number of records. RoIIup error Occurred for the goal < gola name>  " When try to access several goal records

we are using CRM 2011 online . Any idea what can be the reason & how to resolve this error

Reply
Verified Answer
Rodrigo Cristelli Lugarinho responded on 20 Sep 2012 2:22 PM

Hello Julian.

Please take a look at this link below:

community.dynamics.com/.../aumentar-el-l-237-mite-de-registros-para-las-queryaggregation.aspx

Also, note that modifying the database directly is not supported. You need to take this risk if you want to change the default from database.

Note that increasing this limit may impact the performance of the queries in CRM 2011, so you should test it out and ensure your CRM and SQL Server are up to the task.

Reply
Alen George responded on 21 Sep 2012 12:31 AM

You might be running into the issue described at :

blog.clickdimensions.com/.../how-many-members-can-you-add-to-a-marketing-list-in-crm-2011.html

"In Microsoft Dynamics CRM 2011, there is a limit of 50,000 records for any aggregate query. You may have bumped into this default limitation when viewing a chart on a list view or dashboard when the query behind the chart would normally return more than 50,000 records:"

You can

1. reduce the number of records

OR

2. increase the limit set by default.

Cheers

Alengeo

Reply
RasmusBr responded on 1 Jul 2013 11:28 AM

Hello Dejavu M

Did you ever solve this problem - I think that I'm having the same problem. I can't even pull the data?

Reply
Selwyn Fernandes responded on 11 Sep 2013 7:08 AM

It works for me by increasing the limit of AggregateQueryRecordLimit.

However, as the data is continuously growing, I need to know how to reduce the number of records being fetched.

Reply
Kimberly D. responded on 8 Jan 2015 8:13 AM

Selwyn,

Where do we go to increase the limit of AggregateQueryRecordLimit?

Thanks!

Reply
Selwyn Fernandes responded on 8 Jan 2015 9:01 AM

To increase the limit, you’ll need to modify the Database: MSCRM_CONFIG

Table: Deployment Properties

Column: AggregateQueryRecordLimit

Query to check current limit:-

USE MSCRM_CONFIG

GO

SELECT ColumnName

    , IntColumn

 FROM DeploymentProperties

WHERE ColumnName = 'AggregateQueryRecordLimit'

Query to modify the limit to, say 75000 rows:-

UPDATE DeploymentProperties

  SET IntColumn = 75000

WHERE ColumnName = 'AggregateQueryRecordLimit'

A word of Caution: The increase in limit may cause performance issues

Reply
Kimberly D. responded on 8 Jan 2015 10:07 AM

Selwyn, Can this be edited if we're not on premise? Thanks again for your help!

Reply
Selwyn Fernandes responded on 8 Jan 2015 11:08 PM

Hi Kimberly,

The above statements apply for On-Premise only. They cannot be used for CRM Online.

However, you can edit the same in CRM Online using powershell. Refer to the following links on how to use Powershell to edit the Advanced Settings.

msdn.microsoft.com/.../2a4061cd-e6b4-4672-8c44-20a27c523718

msdn.microsoft.com/.../gg334634.aspx

Reply
Adam Vero responded on 9 Jan 2015 5:49 AM

I'm pretty sure you can't edit this limit for CRM Online when you actually try to do it, even though it is listed as both an Organization and Deployment level advanced setting. I think they set the "IsWritable" property to 0 for lots of these settings so that the performance of one customer is not affected by another.

Reply
Verified Answer
Rodrigo Cristelli Lugarinho responded on 20 Sep 2012 2:22 PM

Hello Julian.

Please take a look at this link below:

community.dynamics.com/.../aumentar-el-l-237-mite-de-registros-para-las-queryaggregation.aspx

Also, note that modifying the database directly is not supported. You need to take this risk if you want to change the default from database.

Note that increasing this limit may impact the performance of the queries in CRM 2011, so you should test it out and ensure your CRM and SQL Server are up to the task.

Reply