Sample: Display the top X values

Applies To: Dynamics CRM 2013

Microsoft Dynamics CRM includes several reports that display the top X items of an entity, where the user can specify the value of X.

To query for the top X items, use dynamic SQL. Assign the SQL query string that includes the user-specified parameter to a variable. This resolves the user-specified parameter. Then the query string is passed to the SQL EXEC function together with the user-specified parameter.

Example

In the following SQL example, the TopCount parameter contains the user supplied value:

Declare @SQL nVarchar (4000)
SET @SQL = '
Select  Top ' + CONVERT(nvarchar(10), @TopCount) + ' kb.kbarticleid FROM
(' + @CRM_FilteredKBArticle + ')  kb '
Exec (@SQL)

If you’re authoring a FetchXML-based report, the corresponding FetchXML query is:

<fetch count="@TopCount"> 
<entity name="kbarticle" enableprefiltering="true" prefilterparametername="CRM_FilteredKbArticle" > 
<attribute name="kbarticleid"/> 
</entity> 
</fetch>

See Also

Create custom reports using Business Intelligence Development Studio
Sample reports
Sample: Make a report context-sensitive

© 2016 Microsoft Corporation. All rights reserved. Copyright