We have developed OTBI analysis where we are using multiple subject areas. And we are getting historical rating for our employee lets say from last three years, the result include Year and Rating which result in values for each year and create three different rows for each employee as shown below.
| Emp | Emp ID | Current Rating | History Yr | History Rating |
|---|
| ABC | 123 | 4 | 2016 | 3 |
| ABC | 123 | 4 | 2017 | 4 |
| ABC | 123 | 4 | 2018 | 4 |
What our expectation is
| Emp | Emp ID | Current Rating | 2016_Rating | 2017_Rating | 2018_Rating |
|---|
| ABC | 123 | 4 | 3 | 4 | 4 |
I tried logical sql and use union but its not allowing to use set operation because some of the columns in our report are hirerarchical/we have selection steps.
Please suggest a way (apart from SQL BI Report) to achieve the same result using OTBI analysis.