How to make Report With Column in Column (Dynamic Columns report) using OBIEE? sample data & report

Question

Hi Guys,

I am really new to OBIEE, Need below report from current data based I have Attached Excel Based Data as well as report required from Same.

Current Data In Excel ...

Sr. NoEmployee NameDept NameNo. of Days
Working DaysLeave
1MaheshAccount Dept504052rajeshAccount Dept6654002004ShamAccount Dept302205MaheshFinance Dept1008006RajeshFinance Dept800650207RamFinance Dept90080018MaheshAdmin Dept100080025

Sr.no.

OBIEE Report Required :

Is it possible make such report, Basically we have transpose the Dept Column from database, Please help me on this.

Currently RPD file is very simple with one table in all layers (Physical, Business, Presentation layer) ,

Do we need to do any changes in RPD for such report, Or do we have any options for same.like image of report.. Basically no. of columns in final report may increase dynamically if dept increases in base tables.

Please help..............

Gianni Ceresa · Answer

Hi,

That's just a pivot. In your analysis select your "employee name", "department", "no of days", "worked days" and "leave" columns and in the result tag use a pivot view and done.

ef767cc8-0e54-4c89-9a86-3428c27bbae8 wrote:Currently RPD file is very simple with one table in all layers (Physical, Business, Presentation layer) ,

That's an issue ...

OBIEE works on models, if you don't model things you can't expect OBIEE to do any magic.

You must have a star schema in the business model, with a logical fact table and a logical dimension table. In the fact you put all the columns which have an aggregate rule on them (your "no of days", "worked days" and "leave"), in the dimension you must have attributes describing measures (your "employee name" and "department").

[Deleted User] · Answer

Employee and Department are dimensions (or dimensional attributes - it really depends on what your source data is) whereas "No of days", "Worked days" and "Leave" are measures.

With a model like that you can create your analysis. Note "analysis" since you asked about OBI here and not "report" since that would be BI Publisher.

You have to use a pivot table and drag the dimensional attribute "employees" into the "rows" and the dimensional attribute "department" WITH the measure label into the "columns" like this: