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?