nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

How to group in BI Analytics

User13140423-Oracle

Hi,

I have a subject area with stores , each stores are in some cities, countries. How can I create an analysis that will look like below:

Name of the store | country1: list of cities comma separated; country2: list of cities comma separated; country3: list of cities comma separated

I have access also to DV. I know how to do this in SQL using GROUP by and LISTAGG , but no idea how to do this in BI Analytics.

Thank you,

Ana

Find more posts tagged with

Answers

Accepted answers

All comments

Gianni Ceresa

Hi,

Your need is to concatenate text, and for that you need to use LISTAGG itself. You call it by using EVALUATE (or EVALUATE_AGGR).

That's how it is done because there isn't a "natural" (universal) was to aggregate text.

User13140423-Oracle

I tried to use EVALUATE_AGGR but in my request is not enough form what I tried. Can you provide the code? I need 2 levels of grouping : one by store and one by country and with EVALUATE i managed to do just one group, not 2.

Gianni Ceresa

It does depends on everything else going on in your analysis, in what you model is built on.

One of the first results of a random Google search:

https://datacadamia.com/dat/obiee/obis/logical_sql/evaluate

https://jethinabraham.com/2017/08/29/string-aggregation-in-obiee/

There are a number of blog posts covering it…

User13140423-Oracle

Thank you,

THese are working for me, but I am trying to do 2 groups. For example, suppose that in the example from your last link, we have also Countries for each employee and I need to get smth like this:

For each department, I need a column like this : US: list of employees, DE: list of employees.

Can you help me with this? evaluate_Aggr works with just one level of grouping, not 2 levels.

I was thinking maybe is doable with dataflows ?

Gianni Ceresa

The example in https://jethinabraham.com/2017/08/29/string-aggregation-in-obiee/ does exactly that: it does a LISTAGG on a the names by department.

And it does definitely work as I just gave it a try in OAS 7.6:

If you add more columns, the LISTAGG will be done by each combination of all the values of all the other attribute columns. That's just standard SQL behaviour.

User13140423-Oracle

Hi,

Can you add inside the column with Product Category another grouping? This is what I am looking for.

Thank you,

Ana

Soumit Roy

In Oracle Analytics, you can achieve this type of output by using a combination of hierarchical columns, aggregation, and custom formula fields, similar to how you would use GROUP BY and LISTAGG in SQL. Here’s a step-by-step guide to creating this analysis:

Method 1: Using Oracle Analytics Classic Analysis

Create a New Analysis: Open Oracle Analytics and create a new analysis based on your subject area.
Add Columns:
- Drag the Store Name column from your subject area into the analysis.
- Drag the Country column.
- Drag the City column.
Create Custom Formula:
- In Oracle Analytics, you cannot directly use SQL functions like LISTAGG, but you can use the Aggregation options in the column properties.
- For this analysis, you can try to apply a custom aggregation to achieve a similar result to LISTAGG. However, Oracle Analytics doesn’t directly support comma-separated lists in the visual designer, so we’ll need to use an alternative approach.
Pivot View:
- Add a Pivot Table View to the analysis. This will allow you to group by store and aggregate cities under each country.
- In the Rows section, drag Store Name.
- In the Columns section, drag Country.
- In the Measures section, drag City.
Customize Aggregation for Cities:
- Click on the City column in the Measures section of the Pivot Table.
- Choose Custom Aggregation options, but this may not create a comma-separated list. You might end up with counts or similar non-ideal aggregation.

Since Oracle Analytics Classic doesn’t directly support LISTAGG, you may need to try an alternative like using Oracle Data Visualization (DV), where you can use the Data Flow feature with custom SQL.

Method 2: Using Oracle Data Visualization (DV) with Data Flow and SQL

Create a Data Flow: Open Oracle Data Visualization and create a new data flow.
Select Your Data Source: Choose the subject area with your Store Name, Country, and City columns.
Add a Custom SQL Step:
- In the data flow, add a Custom SQL step.
- sqlCopy codeSELECT "Store Name", "Country", LISTAGG("City", ', ') WITHIN GROUP (ORDER BY "City") AS "Cities"FROM YOUR_TABLEGROUP BY "Store Name", "Country"
Finalize the Data Flow:
- Add any further transformations if needed, then save and run the data flow.
- This will produce a dataset with Store Name, Country, and a Cities column containing a comma-separated list of cities for each country.
Use the Dataset in an Analysis:
- Once the data flow completes, you can use the resulting dataset in a new visualization or analysis.
- You can create a Pivot Table view or a Table view to arrange the data, grouping by Store Name and displaying each country with its comma-separated list of cities.

Final Output

cssCopy codeStore Name | Country1: Cities; Country2: Cities; ...

This method leverages Oracle DV’s ability to create custom SQL-based transformations, giving you control similar to GROUP BY and LISTAGG in SQL.

Gianni Ceresa

The above answer sounds like a piece of GenAI output, don't blindly trust it… (or not at all, it isn't the best GenAI output you could get)

Can you add inside the column with Product Category another grouping? This is what I am looking for.

What do you mean by "another grouping"? Are you saying that you want to concatenate all the "country: list of cities" you get for each store to have a single row, and 2 columns for each store?

That kind of output isn't very useful in analytics as it will be a big piece of text, you could just use a narrative view and job done.

It sounds like there are missing pieces here on what you fully need to achieve.

A dataflow isn't going to help you much as you still don't have access to SQL because you are working on a subject area.

You can definitely solve it in your database by generating that piece of text there (a SQL expression, a view, an opaque view etc.), and bring it into your RPD as an attribute for the store and then display it where you need.

User13140423-Oracle

indeed the answer above is AI generated, it is not working.

Yes, I need a report with all the stores, and for every store one column like this: Country1: City1, City 2 ;Country2: City1 , City 2

Report should look like this, cities and countries should be ordered!!

Store1 | Country1: City1, City 2; Country2 : City1 , City 2

Store2 | Country1: City1, City 2; Country2 : City1 , City 2

Please note that I have a data set : Store, country, city.

I would be very thankful if someone could provide a solution on this .

Thank you,

Ana