OBIEE Children of the level / Converting rows to strings

1 Preface
Consider the following table:


But what you really want is this:

Or even better sorted alphabetically

This document describes how you can achieve this in OBIEE against an Oracle DB

2 Stragg function
This solution is based on Tom Kite’s original String Aggregation function found here:
http://www.sqlsnippets.com/en/topic-11591.html

To implement this function in OBIEE your first have to bring the function to the database. Either in the data-schema or in your custom OBIEE function schema. If you do the later be sure that the OBIEE function schema has direct select rights granted on the data-schema tables and views. Grant an execute on the STRAGG function to public. It’s also very handy to create a public SYNONYM for the STRAGG function. (CREATE PUBLIC SYNONYM STRAGG FOR SCHEMA_NAME.STRAGG;)

2.1 The STRAGG scripts

2.1.1 The STRAGG object

create or replace type stragg_type as object
(
string varchar2(4000),

static function ODCIAggregateInitialize
( sctx in out stragg_type )
return number ,

member function ODCIAggregateIterate
( self in
out stragg_type ,
value in varchar2
) return number ,

member
function ODCIAggregateTerminate
( self in stragg_type,
returnvalue out
varchar2,
flags in number
) return number ,

member function
ODCIAggregateMerge
( self in out stragg_type,
ctx2 in stragg_type
)
return number
);
/

2.1.2 The STRAGG type body

create or replace type body stragg_type
is

static
function ODCIAggregateInitialize
( sctx in out stragg_type )
return
number
is
begin

sctx := stragg_type( null ) ;

return
ODCIConst.Success ;

end;

member function ODCIAggregateIterate
( self in out stragg_type ,
value in varchar2
) return number
is
begin

self.string := self.string ',' value ;

return
ODCIConst.Success;

end;

member function ODCIAggregateTerminate
( self in stragg_type ,
returnvalue out varchar2 ,
flags in number
) return number
is
begin

returnValue := ltrim( self.string,
',' );

return ODCIConst.Success;

end;

member function
ODCIAggregateMerge
( self in out stragg_type ,
ctx2 in stragg_type
)
return number
is
begin

self.string := self.string
ctx2.string;

return ODCIConst.Success;

end;

end;
/

2.1.3 The STRAGG function

create or replace function stragg
( input varchar2 )
return varchar2
deterministic
parallel_enable
aggregate using stragg_type
;
/

3 OBIEE Usage
These function work both from the repository as directly from the reports.
3.1 Unsorted

EVALUATE_AGGR( 'STRAGG(%1)' as varchar(200), EMP.ENAME)

3.2 Sorted




EVALUATE( 'STRAGG(%1) OVER ( PARTITION BY (%2) ORDER BY (%1) ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) ' as varchar(200), EMP.ENAME,DEPT.DEPTNO)

3.3 Distinct
EVALUATE_AGGR( 'STRAGG( DISTINCT %1)' as varchar(200), EMP.ENAME)

Till Next Time

This article was original written for the Ciber knowledge Blog:
http://knowledge.ciber.nl/weblog/?p=125