OBIEE - How to pivot a character column ?

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Presentation Service (SAW/OBIPS)

1 - About

The pivot of a character is just an easy way to split one character column in two or more columns.

By default, the pivot view take the measures (the column set with an aggregation rule) and place them in the intersection of the X and Y axis. You will then never find a column without an aggregation rule that contains a character data type.

3 - Solution

To overcome this issue, you have to:

  • set an aggregation rule such as max, min in your pivot view

  • or set on the character column this formula :
MIN(TABLE.CharacterColumn BY TABLE.XAxisColumn, TABLE.YAxisColumn)

where XAxisColumn is the row and YAxisColumn is the column

Advertising

4 - Supplement

4.1 - How to show a table of three columns ?

Replace V_NumberOfColumn by the number of column. The Number of column must be a double and not an integer. Integer = 2, Double = 2.0 in C, C++

X Axis :

MOD(RCOUNT(TABLE.CharacterColumn),V_NumberOfColumn)

Y Axis :

FLOOR(RCOUNT(TABLE.CharacterColumn) / V_NumberOfColumn)

For the table :

MIN(TABLE.Column BY MOD(RCOUNT(TABLE.CharacterColumn),V_NumberOfColumn), 
FLOOR(RCOUNT(TABLE.CharacterColumn) / V_NumberOfColumn))

4.2 - How to show an URL of an image ?

You can then set the column format as an HTML and modify the column format to add the HTML nodes as :

'<a href="' || MIN(TABLE.CharacterColumn BY MOD(RCOUNT(TABLE.CharacterColumn),3), 
FLOOR(RCOUNT(TABLE.CharacterColumn) / 3)) || ' ">' 
|| MIN(Vendor.Name BY MOD(RCOUNT(TABLE.Column),3), FLOOR(RCOUNT(TABLE.Column) / 3)) 
|| '</a>'

5 - Support

If you try to pivot the column with the filter option, you will fire this error :

The filter function requires at least one measure attribute in its first argument. 
Select a measure or deselect all text to insert a new filter expression

And even if you set a aggregation rule, it doesn't work because the function wait a number data type.

Advertising

6 - Reference