Extra Pages

Friday, December 7, 2007

OBIEE Pivot table with carriage return

One of my customers wanted some data presented in a pivot table with a maximum of 5 columns. If more data is available then a sort of carriage return should be given and continue on next line. (A sort of DTP)

First I concatenated all the data into 1 cell:




Then I determined the ColomnNumber and RowNumber for each element.









And finally put it in a pivot table:


Don't forget to set the aggregate level for the measure to first!

5 comments:

  1. Hi,

    How do I get the difference of two columns by using pivot table or table?

    Ex.
    Sales
    Month Nov Dec Difference
    Regioon1 xx xx x
    Regioon2 xx xx x
    Regioon3 xx xx x

    Any suggestions????

    ReplyDelete
  2. Hi,
    How do I get the difference of two columns by using pivot table or table?

    Ex.Sales by month
    Month --- Nov Dec -- Difference
    Region1--- xx xx-------x
    Region2--- xx xx-------x
    Region3--- xx xx-------x

    Any suggestions????

    ReplyDelete
  3. Hi Poornima,

    In the column sector of the pivot table click on a column, select new calculated column.

    Regards John

    ReplyDelete
  4. Thank you!!!

    Regards,
    Poornima

    ReplyDelete
  5. Hi!
    I'm pretty new in OBI (the matter of fact I'm just an "adhock" replacement in crises for OBI) and have one simple question.

    How to retrieve column name of the only table in Answer query. I need experssion like:
    CASE WHEN "column_name"='a_column1" THEN 1
    WHEN "column_name"='a_column2" THEN 2
    WHEN "column_name"='a_column3" THEN 3
    ELSE 999
    END

    ( iwant to get number under columns for statistical referencing). You can see picture of what I need on:
    http://6535870950293754132-a-1802744773732722657-s-sites.googlegroups.com/site/vadasovi/Home/row_problem.png?attachauth=ANoY7coC14i_DkyKAE2MGVcuMuh96AttFDOtUtNj4nqRDLNds3cqSCd6WI4aS5WaFm7QyCjFelJ9GrCmjD7SooTSPeK4sz2xfmvPGCbgA31vThwHpHteYYE9Zv7VjOehWEqedBu9geR7EAY0U1H8zfwEkibCteB-6Z19azvfwLd_8JjJ8ZdOLfevIznyod5FC-mNqIJh-YslKKyom5wlxlJjqBgfs7b3nA%3D%3D&attredirects=0

    Thank you in advance,
    Damir Vadas
    http://damir-vadas.blogspot.com

    ReplyDelete

Note: Only a member of this blog may post a comment.