Using SUBSTR/ REGEXPR in report BI Report Filter LOV..?

Hi All,

I have a requirement to show department number with Department names in the report filter like this.

SELECT e.department_id ||'|'|| d.department_name FROM employees e, departments d WHERE d.department_id = e.department_id;

Report Filters need to show like This.

10|Administration

I need to pass this filter output to another filter. So, I Need to Extract the department_no/ Department_name from the first filter.

I used the below logic to extract the valid inputs to another Filter.

substr(department_id||'|'||department_name, 1, instr(department_id||'|'||department_name, '_', 1,1)-1)