Sample: Limit the number of items displayed in a chart

Applies To: Dynamics CRM 2013

Chart reports are limited to 15 data points. The dataset must limit the result set to the top 15 rows for the charted value. Additionally, a 16th "other" data point value is optional. This value represents an aggregation of all other rows in the domain. The 16th value is always sorted to the bottom of the result set. This adds a level of complexity to reports that contains drill-through functionality that is appropriate for some reports.

Example

The following is a SQL query example that generates top 15 data points plus a 16th other data point for a chart:

INSERT INTO @AcctTopTbl SELECT Top 15 accountid FROM FilteredAccount ORDER BY Revenue Desc

SELECT AcctTop.accountid, AcctTop.name, AcctTop.Revenue, 1 As SortOrder FROM (SELECT Top 15 accountid, name, Revenue FROM FilteredAccount ORDER BY Revenue Desc) AS AcctTop
UNION
SELECT Null As accountid, 'Other' As name, Sum(Revenue) As Revenue, 2 As SortOrder FROM FilteredAccount 
WHERE accountid NOT IN (Select accountid FROM @AcctTopTbl)
ORDER BY SortOrder Asc, Revenue Desc

Example

The following example demonstrates how to limit a number of data points shown in the table by using the FetchXML query. You have to provide two data sets with one FetchXML query per dataset. The results from the first dataset query are passed through the multi-valued parameters to the second dataset to filter the results of the query. Dataset1 retrieves the top 15 records ordered by revenue and Dataset2 retrieves the “TotalRevenue” aggregating all accounts except the accounts from DataSet1.

DataSet1:
<fetch count="15">
  <entity name="account" >
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="revenue" />
    <order attribute="revenue" descending="true" />
  </entity>
</fetch>

DataSet2:
<fetch aggregate="true">
 <entity name="account">
    <attribute name="revenue" aggregate="sum" alias="TotalRevenue" />
    <filter>
       <condition attribute="accountid" operator="not-in" value="@TopAccountIds"/>
     </filter>
  </entity>
</fetch>

Report Parameter:

<ReportParameter Name=" TopAccountIds ">
      ...
      <MultiValue>true</MultiValue>
      <Hidden>true</Hidden>
      <DefaultValue>
            <DataSetReference>
                  <DataSetName>DataSet1</DataSetName>
                  <ValueField>accountid</ValueField>
            </DataSetReference>
      </DefaultValue>
</ReportParameter>

See Also

Sample reports
Sample: Display the top X values
Report Writers Guide for Microsoft Dynamics CRM 2013

© 2016 Microsoft Corporation. All rights reserved. Copyright