We have a daily time report which needs to include all employees whether or not they clocked in for the day. The baq contains a left outer join between the empbasic and laborhed tables and returns the correct data when run from the baq designer with a parameter used to select laborhed.payrolldate.
When the baq report is executed, using an option field to select the laborhed.payrolldate, only employees that clocked in on the selected date are included. (this report worked in E9 - Progress).
I would appreciate any suggestions to make this report work in E10
Usually in sql, you need to have an or in the where condition, like:
select *
from Erp.LaborHed
inner join Erp.EmpBasic
on EmpBasic.EmpID = LaborHed.EmployeeNum
where (payrolldate = getdate()
OR PAYROLLDATE IS NULL)
and…
I’m not sure how this translates to the BAQ report but I assume that you’ll have to find a way to put the or condition in. You might have to create a calculated field that converts the null into a known value.
You can create an employee subquery, adding your parameter as a calculated field for all rows then left outer join the subquery to the laborhed table.
thanks for your reply Andrew and Ken. When adding option fields via the Baq Report Designer I haven’t found a way to designate ‘or’ for the selection criteria.
I will test the subquery option to see if I can get it to work. We are currently upgrading from 9.05 to 10 so subquerys are relatively new to me.