Solved

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

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

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now