Oracle Communities

Oracle Analytics and AI

Child Item

Rows to Column in OBIEE

3312604

Hi All,

I have a requirement where i have to convert my rows into columns(different obviously )

So my data is

Id Region City

2 NAM NYC

2 NAM Washington

I have to display like this:

Id Region City1 City 2

2 NAM NYC Wash..

is it possible to do it in OBI? i don't have access to OBI backend or repository

Find more posts tagged with

Accepted answers

All comments

3312604

i wrote some logic which i found in the community but no luck since i am getting an error

evaluate_aggr('sum(%1)', Location"."Location Level 4")

John_K

The obvious answer would be to use a pivot view. However I assume that this is required for something more complex.

Other than that you are into the realms of "manual" pivoting, which only works if you know how many cities you have. So you would have a case formula in each column:

Case when "location"."location level 4" = 'NYC' then "fact"."value" End

etc etc...

3312604

is their no other way?

like use of evaluate aggr functions or so?

i am new to OBI so not sure whether this will work

John_K

Not sure why you'd want to evaluate_aggr? This is generally used to allow you to use aggregate functions at the database level (i.e. not available in OBIEE) - so it pushes what you write in there down to the database level and applies a group by.

3312604

so i found that using listagg you can get those multiple rows in a single row with comma separated value.

i am trying to achieve that!!

is that possible?

John_K

Listagg just puts the values into a single column though - not separate columns. That makes them somewhat restrictive. If you want that, you might be able to use listagg within your analytic (I don't have access to a system to try it) but you first need to have evaluate/evaluate_aggr enabled - if you don't have access to the OBI backend then you aren't going to be able to do that.

3312604

hahah
i did it

CAST(EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)',"Location"."Location Level 4",',',"Location"."Location Level 4") as CHAR(50))