Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-21
5
Medium Priority
?
1,373 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

670 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