Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 212 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Create a list of unique Departments (each department has varying number of rows for its mngmnt)
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.
Best Answer
-
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 :)
2
Answers
-
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?
0 -
Siraj (@User_SI7HK )
There is an Org chart plugin for Oracle Analytics Cloud in , 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.0 -
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).
0 -
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.0 -
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)
0 -
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.1 -
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
0 -
Thanks Gianni, TOPN("your table"."lead_rank", 1 by "your table"."department") approach worked, the example you provided was very helpful, thanks again.
2
