Home » Developer & Programmer » Reports & Discoverer » Reports based on data (Report Builder 10.1.2.0.2)
Reports based on data [message #583437] Tue, 30 April 2013 15:34 Go to next message
jay_cooldude
Messages: 50
Registered: March 2013
Location: Hyd
Member
Hi,
I have a doubt that can we make a report on sum of the salary record wise and grouped by dept number?
like as shown BELOW
Dept No        Dept Name
10             Accounting      
Employee ID    Name       Salary      Sum Salary
7782           CLARK       2450           2450
7934           MILLER      1300           3750
7839           KING        5000           8750
Dept No        Dept Name
20             Research      
Employee ID    Name       Salary      Sum Salary
7369           SMITH       800            800 
7788           SCOTT      3000           3800 
7902           FORD       3000           6800
Dept No        Dept Name
30             Sales      
Employee ID    Name       Salary      Sum Salary
7844          TUNER       1500           1500
7499          ALLEN       1600           3100
7521          WARD        1250           4350
7900          JAMES        950           5300


emp table
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------

 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

dept table

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------------

 DEPTNO                                    NOT NULL NUMBER
 DNAME                                              VARCHAR2(15)
 LOC                                                VARCHAR2(15)



if yes .Please suggest the solution .Thanks in advance.
Re: Reports based on data [message #583438 is a reply to message #583437] Tue, 30 April 2013 15:58 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Analytic form of the SUM function might be your choice:
SQL> break on deptno on dname
SQL> select
  2    d.deptno,
  3    d.dname,
  4    e.empno,
  5    e.ename,
  6    e.sal,
  7    sum(e.sal) over (partition by d.deptno order by e.empno) rsal
  8  from emp e,
  9       dept d
 10  where e.deptno = d.deptno
 11  order by d.deptno, e.empno;

    DEPTNO DNAME               EMPNO ENAME             SAL       RSAL
---------- -------------- ---------- ---------- ---------- ----------
        10 ACCOUNTING           7782 CLARK            3250       3250
                                7839 KING             5800       9050
                                7934 MILLER           2100      11150
        20 RESEARCH             7369 SMITH            1600       1600
                                7566 JONES            3775       5375
                                7788 SCOTT            3800       9175
                                7876 ADAMS            1900      11075
                                7902 FORD             3800      14875
        30 SALES                7499 ALLEN            2400       2400
                                7521 WARD             2050       4450
                                7654 MARTIN           2050       6500
                                7698 BLAKE            3650      10150
                                7844 TURNER           2300      12450
                                7900 JAMES            1750      14200

14 rows selected.

SQL>

DEPTNO and DNAME should form their own group; layout is the "Group above".
Re: Reports based on data [message #583439 is a reply to message #583438] Tue, 30 April 2013 16:31 Go to previous messageGo to next message
jay_cooldude
Messages: 50
Registered: March 2013
Location: Hyd
Member
Hi,
I express sincere thanks for giving the solution . Can make use of summary or formula column to get the same results?
thanks

Regards
Jay

[Updated on: Tue, 30 April 2013 17:32]

Report message to a moderator

Re: Reports based on data [message #583446 is a reply to message #583439] Wed, 01 May 2013 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just for fun, to get the wanted output with only SQL:
SQL> set heading off
SQL> col no     format a14
SQL> col name   format a14
SQL> col sal    format a14
SQL> col sumsal format a14
SQL> select case
  2           when grouping(e.empno) = 1
  3             then 'Dept No
  4  '||to_char(e.deptno)||'
  5  Employee Id'
  6             else to_char(e.empno)
  7         end no,
  8         case
  9           when grouping(e.empno) = 1
 10             then 'Dept Name
 11  '||initcap(d.dname)||'
 12  Name' 
 13           else e.ename
 14         end name,
 15         case
 16           when grouping(e.empno) = 1
 17             then '
 18  '||'
 19  Salary'
 20           else to_char(e.sal)
 21         end sal,
 22         case
 23           when grouping(e.empno) = 1
 24             then '
 25  '||'
 26  Sum Salary'
 27           else to_char(sum(e.sal) over (partition by e.deptno order by e.empno))
 28         end sumsal
 29  from emp e, dept d
 30  where d.deptno = e.deptno 
 31  group by rollup ((e.deptno,d.dname),(e.empno,e.ename,e.sal))
 32  having grouping_id(e.deptno,e.empno) != 3
 33  order by e.deptno, grouping(e.empno) desc, empno
 34  /
Dept No        Dept Name
10             Accounting
Employee Id    Name           Salary         Sum Salary
7782           CLARK          2450           2450
7839           KING           5000           7450
7934           MILLER         1300           8750
Dept No        Dept Name
20             Research
Employee Id    Name           Salary         Sum Salary
7369           SMITH          800            800
7566           JONES          2975           3775
7788           SCOTT          3000           6775
7876           ADAMS          1100           7875
7902           FORD           3000           10875
Dept No        Dept Name
30             Sales
Employee Id    Name           Salary         Sum Salary
7499           ALLEN          1600           1600
7521           WARD           1250           2850
7654           MARTIN         1250           4100
7698           BLAKE          2850           6950
7844           TURNER         1500           8450
7900           JAMES          950            9400
Re: Reports based on data [message #583452 is a reply to message #583439] Wed, 01 May 2013 02:42 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
jay_cooldude wrote on Tue, 30 April 2013 22:31
Hi,
I express sincere thanks for giving the solution . Can make use of summary or formula column to get the same results?
thanks

Regards
Jay


Almost certainly, but if you can do it straight sql you should, the report will run faster that way.
Previous Topic: Oracle payables Tds expensewise report issue
Next Topic: How to set part of string in bold?
Goto Forum:
  


Current Time: Thu Mar 28 15:33:55 CDT 2024