Distinct Listagg values

user13550855

Hi,

I am trying to create a column which returns aggregated values separated by comma. I am using EVALUATE_AGGR and LISTAGG function.

To elaborate, I am using Sample App Lite RPD. I created a report with Month, Brand, Product Types and Revenue columns. Product Types column has following formula:

cast(EVALUATE_AGGR('LISTAGG(%1,%2) within group ( order by %1 ASC)',  "Products"."P2  Product Type",',') as char(1000))

The result is:

P4 Brand	CAST ( EVALUATE_AGGR('LISTAGG(%1,%2) within group ( order by %1 ASC)', P2 Product Type, ',') AS CHARACTER ( 30 ))	1- Revenue	T01 Per Name Week	T02 Per Name Month
BizTech	Accessories,Accessories,Access	193,394	2012 Week 15	2012 / 04
FunPod	Camera,Camera,Camera,Camera,Ca	139,505	2012 Week 15	2012 / 04

If  you notice, the product type values are repeated in second column. Any thoughts on how to get unique values?

thanks in advance

Gianni Ceresa

It's Oracle SQL, you are bypassing OBIEE by using EVALUATE_AGGR, so you need to solve that from a SQL point of view.