Solved

Selecting the top ten records returned in ORACLE Discoverer for Quality Defect Pareto Chart

Posted on 2008-10-21
5
1,353 Views
Last Modified: 2011-10-19
In ORACLE Discoverer I have the following three columns that I am retrieving from queries in my business area

PLAN      |CELL      |CAUSE CODE
===========================
THERE      |CABLE      |DEFECTIVE
THERE      |CABLE      |DEFECTIVE
THERE      |CABLE      |MISSING
THERE      |CABLE      |DEFECTIVE
THERE      |CABLE      |MISSING
THERE      |CABLE      |BROKE
THERE      |CABLE      |DEFECTIVE
THERE      |CABLE      |MISSING
THERE      |CABLE      |DEFECTIVE
THERE      |CABLE      |BROKE
THERE      |CABLE      |DEFECTIVE
THERE      |CABLE      |DEFECTIVE
THERE      |CABLE      |MISSING
THERE      |CABLE      |DEFECTIVE
THERE      |CABLE      |MISSING

In Discoverer I want to use the data above and GROUP by PLAN and CELL with a COUNT on CAUSE CODE to produce the following

PLAN      |CELL      |CAUSE CODE      |COUNT      |RANK
===========================================================
THERE      |CABLE      |DEFECTIVE            |8      |1
THERE      |CABLE      |MISSING            |5      |2

The data will then be used for a pareto chart (I have a much larger data set and will actually be limiting to the top ten instead of the top two).  All of the items not in the top ten (top two in the example above) will be excluded from the dataset.

I could create views in Administrator specifically for the PARETO charts but I would have to think that there is a way to do the top x in Discoverer like in MS SQL.

If I use a COUNT on the Cause Code everthing in my dataset is correct except that I am missing the RANK which I need to use as a condition to only return the top x rows and to order the results.  I have been playing around with functions in conditions and calculations to get this working but have not had any success.
0
Comment
Question by:DRSLT
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:mohammadzahid
ID: 22773664
I have not tested but I think this sql statement will work with minor adjustment:

SELECT ROWNUM AS rank, PLAN, cell, cause_code, b_count
FROM (SELECT PLAN, cell, cause_code, count(cause_code) b_count
FROM TEST
GROUP BY PLAN, cell, cause_code
ORDER BY cause_code desc)
WHERE ROWNUM < 3

0
 
LVL 27

Expert Comment

by:sujith80
ID: 22773854
This query should do.
select PLAN, CELL, CAUSE_CODE, COUNT, rownum RANK
from (
select PLAN, CELL, CAUSE_CODE, count(*) count
from tbl1
group by PLAN, CELL, CAUSE_CODE
order by count(*) desc)
where rownum <= 10;

Open in new window

0
 
LVL 1

Author Comment

by:DRSLT
ID: 22778601
Thank you for the suggestions.  However, my business area is set up with queries/views for the whole data set.  I want to be able to do the group/count/sort inside of Discoverer Plus or Desktop.  I believe that the suggestions thus far would require that I create a new folder inside of the business area.  The difficulty with that, unlike my example, there are multiple PLANs and CELLs which I have set up as page items for my workbook.  I need to report the top ten based on the selected PLAN and CELL so I believe I need to do all of the logic in PLUS or Desktop.

I am attaching a picture of what I get now.  I want to limit to the top ten (with greatest 'Ims Cause Code2 COUNT being top 1) based on the selected criteria in the page items.
pareto.png
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22783426
I am not familiar with the tool you are using here.
If you need any changes to the query let know.
0
 
LVL 1

Accepted Solution

by:
DRSLT earned 0 total points
ID: 22830089
Admins:

Please close this question.  I have not received any answers regarding how to accomplish this in Discoverer for ORACLE.  I have a workaround in place that provides some of the results that I need.

Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question