shobdy
(Shawn Hobdy)
October 26, 2020, 3:55pm
1
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)
October 26, 2020, 4:25pm
2
Include the ROW_NUMBER as a field, and add a criteria of ROW_NUM <= parameter
Start with what @knash did here:
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
ckrusen
(Calvin Krusen)
October 26, 2020, 4:46pm
4
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)
October 26, 2020, 5:17pm
5
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)
October 26, 2020, 5:23pm
6
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)
October 26, 2020, 5:37pm
7
@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
shobdy
(Shawn Hobdy)
October 26, 2020, 6:34pm
8
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
1 Like