Many of us are not aware of this LISTAGG function in BO universe since this is an ORACLE 11g function and should work well in BO when you use ORACLE DB.

List Aggregate (LISTAGG)

LISTAGG is a built-in function that enables us to perform string aggregation natively. String aggregation is a popular technique, and there are several methods available to perform the aggregation in BO universe designer.

The LISTAGG function has the following syntax structure:

LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

LISTAGG is an aggregate function that can optionally be used as an analytic (OVER() clause) and below are mandatory elements for LISTAGG function:

• The object or expression to be aggregated (LISTAGG(Object Name))
• The WITHIN GROUP keywords
• The ORDER BY clause within the grouping.

LISTAGG object Creation

You can create LISTAGG object universe designer as like other objects and below is the example and I have just used Comma for each state to separate them in the report. You can use any operators based on your requirement.

By default LISTAGG objects goes to Measure object as property but you can change to Dimension, since LISTAGG is a string aggregation and no need to keep that object in Measure.

LISTAGG function is the fastest technique for string aggregation and has the additional benefit of being a simple built-in function.

Note: LISTAGG function only supports Deski report and won’t work properly in Webi reports (Webi report must require the order by clause object in the group by clause which is not working) . To achieve the String aggregation in Webi report, you can follow below methods.

You have a sales report based on State and City as mentioned below.

You want to analyze the sum of sales amount for each state along with city. You can achieve the desired using LISTAGG in deski report whereas same cannot perform well in webi report.  User want to see the sales report as mentioned below.

Please follow the below steps to achieve the above report in webi.

• Create the report using State_Name, City_Name and sales_Amount objects and order the State Name in ascending order.

• Create Combined_City object to concatenate all the cities for each State.
Combined_City =[City_Name]+”;”+Previous(Self;([State_Name]))

• Create another object to find the length of the Combined_City object.
Combined_City_Length=Length([Combined_City])

• Create Rank object to calculate the levels between the state and cities.
State_Rank =Rank([Combined_City_Length];[City_Name];([State_Name]))

• Create one City filter object.
City_List=NoFilter([Combined_City])

• Apply report filter where State_Rank=1

• Insert City_list object into the report structure and hide City_Name. your report is ready.

Please try the string aggregation in your report and pass on your comments.