Example: Display the top X values

  • Article

 

Applies To: Dynamics 365 (online), Dynamics 365 (on-premises), Dynamics CRM 2016, Dynamics CRM Online

Microsoft Dynamics 365 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
Example reports
Example: Make a report context-sensitive

© 2016 Microsoft. All rights reserved. Copyright