Sample: Display the top X values

  • Article

 

Applies To: Dynamics CRM 2015

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 items, use dynamic SQL. Assign the SQL query string that includes the user-specified parameter to a variable. This resolves the user-specified parameter. The query string is then 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, this is the corresponding FetchXML query.

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

See Also

Create a new report using SQL Server Data Tools
Sample reports
Sample: Make a report context-sensitive

© 2016 Microsoft Corporation. All rights reserved. Copyright