How to return top X using a paramter


I am trying to develop a BAQ where the user chooses a parameter number that will be used to return the top X rows from the BAQ.

Is there a way to use a parameter for that?

I know I can hard set it under the subquery options tab.

Any help is much appreciated!



Include the ROW_NUMBER as a field, and add a criteria of ROW_NUM <= parameter

Start with what @knash did here:

and replace the

Hmmmm … unless I’m missing something, you don’t have to go through the trouble of adding the subquery with row number.

Just enter the parameter in the SubQuery Options, like:


It requires a Sort By column to be set on the subquery, but no other special considerations


the generated query phrase is:



Maybe because mine it a bit more complicated but it doesn’t do what yours does.

Here is a screenshot of my Subquery options for the innerSubQuery :

Here is a sort on the innerSubQuery like you said:

When I look at the query generated on the General tab, I get:

Nowhere on the query does it mention the parameter @TopRows that I created.

What am I doing wrong? BTW I am version 10.1.500.23 if that matters.

Usually, having a sort in a subquery generates an error.

And since you have a subquery, I’d suggest adding the calc field of
ROW_NUMBER () OVER( ORDER BY Calculated.TotalSales)
Then in the query that uses CMITopRows, add a table criteria to limit it to the parameter.

@shobdy - I tried what you did and ist seems to work okay in a subquery.

To enable the Offset and Fetch fields, I had to check the “With Ties” box. After entering the Offset and Fetch fields, I unchecked the “With Ties”.

I’m even using a calculated field as the Sort in the sub Query.


The resulting query phrase is


1 Like

Hey @ckrusen,

I can’t thank you enough.

I found my problem to be I didn’t put a zero in the offset field.

Once I did that, it started working fine.

I assumed with it blank, it would be zero. My bad!!!



1 Like