Sample: Display the Top X Values

  • Article

[Applies to: Microsoft Dynamics CRM 2011]

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.

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 are authoring a FetchXML based report, the corresponding fetch-xml query is:

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

See Also

Concepts

Create Custom Reports Using Business Intelligence Development Studio

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