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

Create a list of unique Departments (each department has varying number of rows for its mngmnt)

Analytics_User

Hi, we are asked to prepare a list of departments which are stored in a table in multiple rows for its leadership(s). Some departments have VP(s), Director(s), Mgr(s), some have Director(s), Mgr(s), where as some department are run by Mgr(s) only. These leadership positions could one or multiple in a department.

We just need a report that lists all departments (one record for each department) with highest level of leadership (any one if there are multiple at the top). We have spent lot of time building expression but not getting the results. Any tip is highly appreciated.

Find more posts tagged with

Accepted answers

Gianni Ceresa

An example of what I mean…
If I want to display only 1 product by LOB, and I want the product with the higher Product Number.

TOPN("P0 Product Number", 1 by "P3 LOB") displays 1 because it does display the ranking of the row, because you only return 1 row per department, it will always be 1. Doesn't matter much in the end, you don't need that column, hide it (but keep it in the view to make sure the granularity is at the right level).

But the result is exactly what I wanted: a single row for each LOB, and display the product with the higher product number in it.

The TOPN column being pointless, you can hide it.

Job done :)

All comments

Mostafa Morsy-Oracle

@User_SI7HK

This is more SQL Question which need your DBA engagement please if you can give us a sample here to the tables rows we may be able to assist or route to correct community

I am assuming DB is Oracle DB is that correct?

Steve F-2

Siraj (@User_SI7HK )

There is an Org chart plugin for Oracle Analytics Cloud in https://www.oracle.com/business-analytics/data-visualization/extensions/ , but as @Mostafa Morsy-Oracle mentioned, please tell the community which product you are using, so we may direct you to the correct community forum.

Analytics_User

Thanks for responding, we have OAS7.0, OBIA7.6.4, EBS12.2 and we don't want to go BI Publisher route for this report as all the attributes are in one subject area. We have added a derived column as lead_rank populating 4 for VP, 3 for Director, 2 for Mgr and 1 for null. Now we just want to filter one record for each department (listed multiple times for its leaderships) with highest lead_rank, (i.e. for each department get the first record with highest lead_rank value).

Gianni Ceresa

Thanks to that column "lead_rank", you can use that to do the job. For example something like TOPN("your table"."lead_rank", 1 by "your table"."department") would do the job. You could also use RANK (by department) and then filter on that.

Analytics_User

Thanks Gianni, I tried the solution you suggested, the function allowed in OAS report is TopN(expr, integer) or BottomN(expr, integer) and does not loop though records for each department. So what it does is returns all rank 1 records and drops the department where lead_rank is not 1 (i.e. departments headed by lower level leads i.e. Directors or Managers). We want to list all departments uniquely (whether they are headed by VP, Director, Mgr or null)

Gianni Ceresa

In my example I posted an example of TOPN using by, by using the "by" piece, you change the level on which the function is executed. And TOPN doesn't really care what is the higher number, it just rank by that and filter the number of rows based on your parameter.

Gianni Ceresa

An example of what I mean…
If I want to display only 1 product by LOB, and I want the product with the higher Product Number.

But the result is exactly what I wanted: a single row for each LOB, and display the product with the higher product number in it.

The TOPN column being pointless, you can hide it.

Job done :)

Mostafa Morsy-Oracle

@User_SI7HK

you can use physical query to build the report As

SELECT department_id, last_name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary) RANK
FROM employees WHERE department_id = 60
ORDER BY RANK, last_name;

You can match the query to fulfill your requirements

or you can use Gianni option for just drag and drop columns and pre-defined TopN Function

Analytics_User

Thanks Gianni, TOPN("your table"."lead_rank", 1 by "your table"."department") approach worked, the example you provided was very helpful, thanks again.