Sample: Display the Top X Values
[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.