Distinct Listagg values

Question

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

BizTechAccessories,Accessories,Access193,3942012 Week 152012 / 04FunPodCamera,Camera,Camera,Camera,Ca139,5052012 Week 152012 / 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 · Answer

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