Forum Stats

  • 3,759,948 Users
  • 2,251,620 Discussions
  • 7,870,878 Comments

Discussions

Reports Developer: A unique break report question

I'm converting reports from Pentaho to Oracle Reports and for the most part things are going smoothly. Except I want to create a report that breaks on one column but sorts on another.

So this is what I want to get. Order by empno and break on dname:

7369 SMITH    RESEARCH        20

7499 ALLEN    SALES                30

7521 WARD                           30

7566 JONES   RESEARCH        20

7654 MARTIN SALES             30

7698 BLAKE                           30

7782 CLARK   ACCOUNTING   10

7788 SCOTT   RESEARCH       20

7839 KING      ACCOUNTING     10

7844 TURNER SALES            30

7876 ADAMS   RESEARCH      20

7900 JAMES    SALES              30

Notice how we are sorting on empno and Since Allen and Ward work in Sales I don't want the value to repeat.

In reports I created the following:

Here is my query:

select e.empno,

e.ename,

D.DNAME,

e.deptno

from

scott.dept d,

scott.emp e

where e.deptno = d.DEPTNO

order by empno, d.dname;

Date Model:

G1: EMPNO

G2: DNAME

G3: ENAME, DEPTNO

output:

7369 SMITH RESEARCH 20

7499 ALLEN SALES 30

7521 WARD SALES 30

7566 JONES RESEARCH 20

7654 MARTIN SALES 30

7698 BLAKE SALES 30

7782 CLARK ACCOUNTING 10

7788 SCOTT RESEARCH 20

7839 KING ACCOUNTING 10

7844 TURNER SALES 30

7876 ADAMS RESEARCH 20

7900 JAMES SALES 30


I want to suppress the repeating Dept (Sales) but order by EMPNO


If I move the DNAME group up it'll sort on that column.  I can't take the group sort off the column in G2 because you need at least one group order.  If I add a second column Deptno to G2 it won't work.


Is this possible?

This discussion has been closed.