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:

Poornima P said...

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

Poornima P said...

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

John Minkjan said...

Hi Poornima,

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

Regards John

Poornima P said...

Thank you!!!

Regards,
Poornima

Damir Vadas (aka Funky) said...

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