Home » Developer & Programmer » Reports & Discoverer » I wants the formula column to be implemented (ORACLE 10G DB,REPORT BUILDER 10g)
I wants the formula column to be implemented [message #430997] Fri, 13 November 2009 10:21 Go to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member



Hello all,

i have written the following query .


/* Formatted on 2009/11/13 02:23 (Formatter Plus v4.8.8) */
SELECT   d.dname, e.job, SUM (e.sal),
         CASE
            WHEN TO_CHAR (e.hiredate, 'yyyy') = '1981'
               THEN 'YTD 08'
            ELSE 'YTD 09'
         END "YEAR"
    FROM emp1 e, dept1 d
   WHERE e.deptno = d.deptno
   and job in ('ANALYST','CLERK')
GROUP BY d.dname,
         e.job,
         CASE
            WHEN TO_CHAR (e.hiredate, 'yyyy') = '1981'
               THEN 'YTD 08'
            ELSE 'YTD 09'
         END
         


Here with am attaching the image .


I have been using the matrix style in that i have taken

Matrix row as Dname
Matrix column as Job level1
Matrix column as year level2
Matrix cell as sum(e.sal).

I wants the formula column right next to the year in level 2.
Since i have tried to implement G_sum_e_sal_perdept in that g_year subgroup.but i cudnt able to do that.
I have to calculate the formula column that ytd08+ytd09.,

If you wants the test cases,i will post it.

Thanks.



Re: I wants the formula column to be implemented [message #431001 is a reply to message #430997] Fri, 13 November 2009 10:47 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Test cases
Create Statements..
CREATE TABLE DEPT1
(
  DEPTNO  NUMBER(2),
  DNAME   VARCHAR2(14 BYTE),
  LOC     VARCHAR2(13 BYTE)
)
TABLESPACE SYSTEM
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          12K
            NEXT             12K
            MINEXTENTS       1
            MAXEXTENTS       249
            PCTINCREASE      50
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE UNIQUE INDEX PK_DEPT1 ON DEPT1
(DEPTNO)
LOGGING
TABLESPACE SYSTEM
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          12K
            NEXT             12K
            MINEXTENTS       1
            MAXEXTENTS       249
            PCTINCREASE      50
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE DEPT1 ADD (
  CONSTRAINT PK_DEPT1
 PRIMARY KEY
 (DEPTNO)
    USING INDEX 
    TABLESPACE SYSTEM
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          12K
                NEXT             12K
                MINEXTENTS       1
                MAXEXTENTS       249
                PCTINCREASE      50
                FREELISTS        1
                FREELIST GROUPS  1
               ));



CREATE TABLE EMP1
(
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(10 BYTE),
  JOB       VARCHAR2(9 BYTE),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7,2),
  COMM      NUMBER(7,2),
  DEPTNO    NUMBER(2)
)
TABLESPACE SYSTEM
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          12K
            NEXT             12K
            MINEXTENTS       1
            MAXEXTENTS       249
            PCTINCREASE      50
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE UNIQUE INDEX PK_EMP1 ON EMP1
(EMPNO)
LOGGING
TABLESPACE SYSTEM
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          12K
            NEXT             12K
            MINEXTENTS       1
            MAXEXTENTS       249
            PCTINCREASE      50
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE EMP1 ADD (
  CONSTRAINT PK_EMP1
 PRIMARY KEY
 (EMPNO)
    USING INDEX 
    TABLESPACE SYSTEM
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          12K
                NEXT             12K
                MINEXTENTS       1
                MAXEXTENTS       249
                PCTINCREASE      50
                FREELISTS        1
                FREELIST GROUPS  1
               ));


ALTER TABLE EMP1 ADD (
  CONSTRAINT FK_DEPTNO1 
 FOREIGN KEY (DEPTNO) 
 REFERENCES DEPT1 (DEPTNO));


Insert Statements..
INSERT INTO DEPT1 ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT1 ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT1 ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT1 ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
COMMIT;


INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  TO_Date( '12/17/1980 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 800, NULL, 20); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  TO_Date( '02/20/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1600, 300, 30); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  TO_Date( '02/22/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1250, 500, 30); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  TO_Date( '04/02/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 2975, NULL, 20); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  TO_Date( '09/28/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1250, 1400, 30); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  TO_Date( '05/01/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 2850, NULL, 30); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  TO_Date( '06/09/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 2450, NULL, 10); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  TO_Date( '04/19/1987 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 3000, NULL, 20); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  TO_Date( '11/17/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 5000, NULL, 10); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  TO_Date( '09/08/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1500, 0, 30); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  TO_Date( '05/23/1987 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1100, NULL, 20); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  TO_Date( '12/03/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 950, NULL, 30); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  TO_Date( '12/03/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 3000, NULL, 20); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  TO_Date( '01/23/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1300, NULL, 10); 
COMMIT;


Re: I wants the formula column to be implemented [message #431002 is a reply to message #430997] Fri, 13 November 2009 10:47 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Most of us dont want to download the attached document..Insted can you insert that image into this ...As My previous post...so that we can...

Sriram Smile
Re: I wants the formula column to be implemented [message #431004 is a reply to message #431002] Fri, 13 November 2009 10:52 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

ramoradba wrote on Fri, 13 November 2009 10:47
Most of us dont want to download the attached document..Insted can you insert that image into this ...As My previous post...so that we can...



I cant find any option like that.since i see only have this insert image ie., http source.


Please help me to insert the formula column.

Re: I wants the formula column to be implemented [message #431030 is a reply to message #431004] Fri, 13 November 2009 13:03 Go to previous message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Any suggestions welcome..

If you need more details please lemme know.

Also my exact thing is , just wants to add a column right next to the years for each job .and the sum of the matrix field cell values for both the years for same job is in formula column,.

[Updated on: Fri, 13 November 2009 15:42]

Report message to a moderator

Previous Topic: Show parameter field value in margin text box
Next Topic: Cumulative balance of
Goto Forum:
  


Current Time: Fri Dec 02 16:32:14 CST 2016

Total time taken to generate the page: 0.12814 seconds