Tip #380: Sort the chart by aggregate

Dynamics CRM TipperAnother mini truck stop. It seems that Adam “I bought the truckstop” Vero has become a regular despite his tendency of giving the answer first, asking the question later.

Our own Joel “Pretend I know nothing” Lindström asks:

Scenario: You have a chart of number of opportunities by opp type. How do you sort/order by the totals?

All I see in xml/SDK/crmchartguy is sorting by field values, nothing about sorting by the aggregate.

Boom! That’s the sound of the supersonic barrier broken by the Adam’s answer:

To do this using the UI (or at least using that as a means of getting started), add a “Top X” rule. If you choose a value for “X” larger than the expected number of categories, then this will simply sort by the aggregate values without filtering any out.

To sort by number of opportunities in each category in a ranked chart style like this, the XML will look something like this (the “count=”20” is what you would see if you use a Top X chart as the basis for this, remove if unwanted):

<datadescription>
  <datadefinition>
    <fetchcollection>
      <fetch mapping="logical" aggregate="true" 
             count="20">
        <entity name="opportunity">
          <attribute groupby="true" alias="OppType" 
              name="xxx_OpportunityType" />
          <attribute alias="OppCount" 
              name="opportunityid" aggregate="count" />
          <order alias="OppCount" descending="true" />

Hope this helps

t.j. – You bet it does!

One thought on “Tip #380: Sort the chart by aggregate

  1. AdamV says:

    Thanks for the mention Mr Jarr!

    It’s also worth noting that you can sort on an aggregate even if it is not shown in the chart. For example if you are using conditional formatting to split the records into two categories before plotting them, you can still use an overall total to make sure the rank is correct.

    In my post “Conditional formatting” in Dynamics CRM Leaderboard Charts I use this technique to sort the chart by the overall total revenue, while the two series are for “My Opportunities” and “All Other Opportunities” separately.
    If you sort by either of these series then the “My opps” series will not be in the right place relative to the others (it will be at one end or the other).

Leave a Reply

Your email address will not be published. Required fields are marked *