Sample: Limit the Number of Items Displayed in a Chart

[Applies to: Microsoft Dynamics CRM 2011]

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.

The following is a SQL query example that generates 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

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

Other Resources

Sample Code for Reporting
Create Reports for Microsoft Dynamics CRM Using SQL Server Reporting Services

Microsoft Dynamics CRM 2011
Send comments about this topic to Microsoft.
© 2013 Microsoft Corporation. All rights reserved.