Limit BAQ results based on field

I have a BAQ that basically just dumps one table (IStatTrn) for use in an SSRS report. The problem is that for some transactions there are 2 different transaction types, 10 and 16. When this occurs I only want the row with the transaction type 16 returned. Everything else about the row is identical except this transaction type, SeqNum, and SysRowID. Is this a possibility?

Do you need to see the transaction type?

Unfortunately yes but only the one, 16 if it’s there and 10 if it’s not.

So, what I would do, is a windowing function.

Row_Number() Over (partition by field1,field2,etc order by TransactionType dec)

That should make all of the duplicate rows have a calculated field, and if there is a 10 and a 16, the 10 will have a 2. Then in the subquery criteria, you can filter that calculated field to only show the rows with a 1.

5 Likes

I marked this as the solution because it would have definitely worked for what the original question was. However, for anyone that’s curious, I actually discovered that there were some settings for the IStat in company configuration that had somehow been changed that were causing the issue to begin with. Thanks for the help guys.

1 Like

Glad you got it figured out. Understanding the intended functionality first is always a good idea, and one that we can often forget to do when it seems easier just to customize around it.