Using Microsoft Dynamics CRM 2011 Charts to Count Records

August 7, 2012

2 comments

Microsoft Dynamics CRM 2011 Grids display the number or retrieved records for the selected view, as long as the number is lower than 5000. When the number exceeds 5000, the Grid counter shows the value +5000.
One of my customers would like to allow some users to view the total number of the Case entity records in the application. As this number is larger than 5000 records, the Grid counter is not a viable solution.

One possible solution is the Chart, which usually works in conjunction with the Grid view criteria. In order to set up a chart which counts the total number of records, follow the next steps:

  1. Create a new public/private view to display all entity records
  2. Create a new public/private vertical/horizontal column Chart 
  3. Set the Chart Series to the Count:All aggregation
  4. Set the Chart category to any field which value is common to all records. This may be a field which has no value for all the entity records. If such field does not exist, create it
  5. Save the query and close the Chart editor

The following screenshot demonstrates a chart counting 20,772 records, while the greed counter shows +5000.

image

So how does the Chart count all records while the Grid stops at 5000 records?
While the Grid query for actual data records, the chart uses FetchXML Count aggregation which returns only one data item – the total number of records. Grouping on a field which has the same value (or no value) for all records ensures only one chart category which allow stacking the total number of records in one column. Following is the Chart FetchXML definition:

       <fetch mapping="logical" aggregate="true">
          <entity name="xxx_dailyhourreport">
            <attribute groupby="true" alias="_CRMAutoGen_groupby_column_Num_0" name="xxx_name" />
            <attribute alias="_CRMAutoGen_aggregate_column_Num_0" name="xxx_dailyhourreportid" aggregate="count" />
          </entity>
       </fetch>

Unfortunately, this solution is currently limited to a max of 50K records. Thank you, Andrii, for your useful comment.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

2 comments

  1. Andrii ButenkoAugust 7, 2012 ב 11:28 AM

    In case you will have more then 50000 you will get an error. I hope that MS will solve this limit someday…

    Reply
  2. Microsoft Dynamics CRMAugust 7, 2012 ב 12:18 PM

    In your blog , i can learn a lot of meaningful knowledge , and benefit from. Some valuable information can also be collected from your article that these be of great help to me.

    Reply