Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Rows to Column in OBIEE

Received Response
430
Views
7
Comments
3312604
3312604 Rank 2 - Community Beginner
edited Aug 13, 2024 5:59PM in Oracle Analytics Cloud and Server

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

Answers

  • 3312604
    3312604 Rank 2 - Community Beginner

    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
    John_K Rank 5 - Community Champion

    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
    3312604 Rank 2 - Community Beginner

    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
    John_K Rank 5 - Community Champion

    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
    3312604 Rank 2 - Community Beginner

    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
    John_K Rank 5 - Community Champion

    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
    3312604 Rank 2 - Community Beginner

    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))