I have a BAQ in which I would like not return a record if only one of the OpCode fields is returned.
Referencing the below picture, I would like the line to not display if there are only values in PK Est and PK Remain. Otherwise I want the record to display whether or not PK Est and PK Remain have values or are null.
I’m coming up short on figuring this out. Is it possible?
Take your current query and turn it into a CTE. Then create a second query in the BAQ that is TopLevel. Bring in the first query as your table and filter as needed.
Another way to do that is to use a windowing function.
sum(
case where OpCode in ('AM','PK','AS','AE') then 1
else 0
end
) over (partition by JobOp.JobNum)
I can’t remember if the case statement and “In” play nice together, but you get the idea. You’ll get a column with a count of the values you want in the list. Then just filter by any rows that have 0. Try with subquery criteria, but if that doesn’t work, just make it a subquery, then bring it back in and filter on the table value.
This worked! I had to figure out using a CTE but now I have another tool in my toolbox. Thanks much to both of you!
Glad to hear. Yes, CTEs are a very powerful tool to help build out complex queries. You can even use them in the RDL query of SSRS reports.