Element Entries transpose rows to columns

Akash Kiran Jana

Hello Folks,

We are trying to create a OTBI report to show element entry values for multiple input values in a single row. The input values need to be represented as a column. We have achieved this development using a Pivot table layout, but we wanted to re-think our solution design and explore if there are any option to leverage the FILTER function or any other delivered OTBI functions to achieve this in a Table layout. For the given rows below, we want to create two columns named Clawback and Payment Number and show the values in a single row.

I have tried to use the following functions to solution this, but either the wrong value was displayed or nothing was showing up.

1. FILTER(MAX("Element Entry Value"."Displayed Input Value Meaning" BY "Element"."Element Name","Worker"."Assignment ID","Input Value"."Input Value Name") USING ("Input Value"."Input Value Name" = 'Clawback'))
2. FILTER(MAX("Element Entry Value"."Displayed Input Value Meaning") USING ("Input Value"."Input Value Name" = 'Clawback'))
3. Case when "Input Value"."Input Value Name" = 'Clawback' then MAX("Element Entry Value"."Displayed Input Value Meaning") else null end

Since Filter function works with measure column, I had wrapped it with MAX function.

I have reviewed the underlying SQL generated for the logical SQL generated in each case but in none of the cases, the filter of the input value name = Clawback was applied. However, if we used a delivered measure column as shown below, although I am not getting the intended result but the underlying SQL generated is being filtered for the Input Value Name as one would expect.

FILTER(MAX("Element Entry Value"."Displayed Input Value Meaning" BY "Element"."Element Name","Worker"."Assignment ID","Input Value"."Input Value Name") USING ("Input Value"."Input Value Name" = 'Clawback'))

We are using the Payroll Element Entries Real Time subject area.

Any ideas how we can solution this using a Filter function or any other function that may help achieve this?