Multiple report parameters for the same object - OQL
I have an OQL report that shows some time registration values for employees. An employee has an attribute WorkingStatus, which tells us if he is currently working or is a past employee. The customer wants the report to have 2 parameter drop down fields, 1 for each employee working status. If he uses the 'currently working' employee parameter, he wants to see a report for that selected employee. However, if he uses the 'past' employee parameter, he wants to see a report for the selected past employee. The report obviously doesn't work properly if he selects both, and we don't care about that situation. Is this even possible? Let me give a partial answer... I have tried the following: SELECT ... employee.Name FROM ... INNER JOIN ... xxxx/yyyy_Employee/HRM.Employee as employee WHERE ... AND ( employee/ID = $EmployeeCurrent OR employee/ID = $EmployeePast ) However, the result is an SQL query with the following WHERE-clause (if only the $EmployeeCurrent parameter was set): ("employee"."id" = ? OR "employee"."id" IN (SELECT hrm$employee"."id" FROM "hrm$employee" WHERE "hrm$employee"."workingstatus" = ?)) The SQL is, though a bit unexpected at first, IMO actually correct. The $EmployeeCurrent parameter is set, so the OQL-SQL translator knows to use an '=' equals comparison. However, the $EmployeePast parameter was not set, so it tries to get all the employees matching the constraint in a sub-SELECT. This obviously doesn't work. It results in the selected employee being shown, plus all employees matching the constraint of the other parameter. So again my question... is this even possible? Kind regards, Jonathan van Alteren - FlowFabric
Jonathan van Alteren
You might try:
AND (CASE WHEN $ReportSelector='Current' THEN (employee/ID = $EmployeeCurrent)
WHEN $ReportSelector='Past' THEN (employee/ID = $EmployeePast)
ELSE (1=1) END)
Where the $ReportSelector is a new enum with the 2 options seen here.