How to return top X using a paramter

[shobdy (Shawn Hobdy)]

Hello,

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!

Thanks,

Shawn

[ckrusen (Calvin Krusen)]

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

[Mark_Wonsil]

Start with what @knash did here:

BAQ return only 1 row per part number

The query below is also a good place to start. This will bring back the most recent entered transactions in the system by partnum. You can always make the b table more to what you are looking for by adding the TranType criteria there. Select a.PartNum, a.TranType, a.TranDate, a.TranQty, b._RowNum From Dbo.PartTran a Inner Join ( -- Use Left Join if the records missing from Table B are still required Select SysRowID, ROW_NUMBER(…

and replace the

@knash

‘b._RowNum <= @topParameter’

[ckrusen (Calvin Krusen)]

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

edit

the generated query phrase is:

[shobdy (Shawn Hobdy)]

Hello,

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.

[ckrusen (Calvin Krusen)]

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.

[ckrusen (Calvin Krusen)]

@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

[shobdy (Shawn Hobdy)]

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!!!

Thanks,

Shawn