If you have an outer join in a BAQ table and use a parameter filter criteria, does it filter correctly? It seems to still be pulling all data even when I put in a parameter criteria. Is this due to the outer join that pulls in null values also which overrides the criteria? I can’t seem to get the filter to work in an outer joined table.
Tough to answer without seeing the SQL. What does the query phrase say?
Ditto to needing more info. But if you are dealing with nulls, you may need to wrap some of it in a subquery.
There is an outer join to the Project table to the LaborDtl that has a filter criteria on the Project table.
That will remove any Project records that don’t match the criteria. But it won’t have any effect on the LaborDtl table.
If you want to do something like “Exclude LaborDtl records where the associated project is inactive” you want to use a SubQuery Criteria.
Those will be take effect after the query results are generated and apply to the resulting dataset rather than a specific table.
2 Likes
Edit: this wasn’t the problem, so removing my answer
For example, not all jobs on the LaborDtl record have Projects associated to them so I pull in all Jobs as an outer join so it pulls in all jobs regardless of an associated Project or not. I then want to allow prompting the users to put in a filter by Project ID on the Project table but it seems to be pulling in all LaborDtl records ignoring the filter criteria by Project ID that was entered. I’m not sure if this is due to my criteria set up or because of the outer join from LabortDtl > Project.
Try putting the criteria in you subquery criteria (it’s a tab on the bottom) instead of on the table. (edit: what @Waffqle_De_LaCroix said)
Can you post a picture of all of the tables and joins?
It works fine if I use it as a Table criteria constant value but when I use it as a specified parameter, it doesn’t work.
You want your selection done after the query runs, sort of a filter of total data.
Putting it on the table filters the table data first. Which is not what you want to do. You want your dataset to return just the items selected.
Make the Parameter Optional by checking the skip condition if empty
Top level Query
SubQuery Criteria
