Solved

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

Posted on 2008-10-21
5
1,359 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

730 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