Home » Developer & Programmer » Reports & Discoverer » Detail and Summary in One report (2 merged) (Oracle 10.2.0.1)
| Detail and Summary in One report (2 merged) [message #564543] |
Sat, 25 August 2012 06:17  |
|
|
I have one group wise report and my requirement is i need to print both the Outputs in one single report first, is group with details and the summary in the last page showing machine code and summary of qty as totals ,I have given below test case .
create table ot_cut_detail (mach_code varchar2(12),batch_code varchar2(12),pos_no varchar2(12),pos_qty number)
insert into ot_cut_detail values ('L1','01','1001',2);
insert into ot_cut_detail values ('L1','01','1002',2);
insert into ot_cut_detail values ('L1','01','1003',2);
insert into ot_cut_detail values ('L2','01','1004',2);
insert into ot_cut_detail values ('L2','01','1005',2);
SQL> SELECT * FROM OT_CUT_DETAIL;
MACH_CODE BATCH_CODE POS_NO POS_QTY
------------ ------------ ------------ ---------
L1 01 1001 2
L1 01 1002 2
L1 01 1003 2
L2 01 1004 2
L2 01 1005 2
--Detailed output is like this as below
MACH_CODE BATCH_CODE POS_NO A
------------ ------------ ------------ ---------
L1 01 1001 2
1002 2
1003 2
L1 Total 6
L2 01 1004 2
1005 2
L2 Total 4
Grand 10
--Summary report will be as below both in one report.
MACH_CODE BATCH_CODE totals
------------ ------------ ---------
L2 01 4
L1 01 6
|
|
|
|
| Detail and Summary in One report [message #564574 is a reply to message #564543] |
Sun, 26 August 2012 01:29  |
|
|
I have one table from which i need to display the output in both detail format with groupings and in the last page i need the summary per each group, do i need to run the same query as below twice to get my desired output.
CREATE TABLE ot_cut_detail (mach_code VARCHAR2(12),batch_code VARCHAR2(12),pos_no VARCHAR2(12),pos_qty NUMBER)
INSERT INTO ot_cut_detail VALUES ('L1', '01', '1001', 2)
insert into ot_cut_detail values ('L1','01','1002',2)
insert into ot_cut_detail values ('L1','01','1003',2)
insert into ot_cut_detail values ('L2','01','1004',2)
insert into ot_cut_detail values ('L2','01','1005',2)
select * from ot_cut_detail
SQL> SELECT * FROM OT_CUT_DETAIL;
MACH_CODE BATCH_CODE POS_NO POS_QTY
------------ ------------ ------------ ---------
L1 01 1001 2
L1 01 1002 2
L1 01 1003 2
L2 01 1004 2
L2 01 1005 2
SQL> select mach_code,batch_code,pos_no,sum(pos_qty) a from ot_cut_detail
2 group by rollup(mach_code,batch_code,pos_no)
3 /
MACH_CODE BATCH_CODE POS_NO A
------------ ------------ ------------ ---------
L1 01 1001 2
L1 01 1002 2
L1 01 1003 2
L1 01 6
L1 6
L2 01 1004 2
L2 01 1005 2
L2 01 4
L2 4
10
10 rows selected.
SQL> select a.mach_code ,a.batch_code,max(a) from (
2 select mach_code,batch_code , sum (pos_qty) over ( partition by mach_code order by mach_code) a from ot_cut_detail
3 )a
4 group by a.mach_code,a.batch_code;
MACH_CODE BATCH_CODE MAX(A)
------------ ------------ ---------
L2 01 4
L1 01 6
|
|
|
|
|
|
| Re: Detail and Summary in One report [message #564577 is a reply to message #564575] |
Sun, 26 August 2012 02:16 |
|
|
Thanks michel for the response , well i need both the outputs to be displayed in one report , i am using reports 6i, at present what i am doing is i am adding this second query as seperate data model and displaying in last page , meaning the report is running the same query twice .Is there a way to dispaly both of them at same time.
|
|
|
|
|
|
| Re: Detail and Summary in One report [message #564586 is a reply to message #564579] |
Sun, 26 August 2012 05:18 |
|
|
Michel please find below the completed statements, is there a better way to display both the outputs in one report.
CREATE TABLE ot_cut_detail (mach_code VARCHAR2(12),batch_code VARCHAR2(12),pos_no VARCHAR2(12),pos_qty NUMBER);
INSERT INTO ot_cut_detail VALUES ('L1', '01', '1001', 2);
insert into ot_cut_detail values ('L1','01','1002',2);
insert into ot_cut_detail values ('L1','01','1003',2);
insert into ot_cut_detail values ('L2','01','1004',2);
insert into ot_cut_detail values ('L2','01','1005',2);
SQL> SELECT * FROM OT_CUT_DETAIL;
MACH_CODE BATCH_CODE POS_NO POS_QTY
------------ ------------ ------------ ---------
L1 01 1001 2
L1 01 1002 2
L1 01 1003 2
L2 01 1004 2
L2 01 1005 2
SQL> select mach_code,batch_code,pos_no,sum(pos_qty) a from ot_cut_detail
2 group by rollup(mach_code,batch_code,pos_no)
3 /
--output 1
MACH_CODE BATCH_CODE POS_NO A
------------ ------------ ------------ ---------
L1 01 1001 2
L1 01 1002 2
L1 01 1003 2
L1 01 6
L1 6
L2 01 1004 2
L2 01 1005 2
L2 01 4
L2 4
10
10 rows selected.
--output 2
SQL> select a.mach_code ,a.batch_code,max(a) from (
2 select mach_code,batch_code , sum (pos_qty) over ( partition by mach_code order by mach_code) a from ot_cut_detail
3 )a
4 group by a.mach_code,a.batch_code;
MACH_CODE BATCH_CODE MAX(A)
------------ ------------ ---------
L2 01 4
L1 01 6
|
|
|
|
|
|
| Re: Detail and Summary in One report [message #564590 is a reply to message #564587] |
Sun, 26 August 2012 05:38 |
|
|
|
Dear Michel , thanks for the respons , actually i dont want any exact output , i have two queries if you see my post and both returns same result set like one is displaying details and one in summary , i want a way to show both of them in one report that is in Reports 6i .
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Detail and Summary in One report [message #564598 is a reply to message #564596] |
Sun, 26 August 2012 08:28 |
|
|
|
Thanks michel , its more of Reports 6i solution which i need, and i got it clarified from other posts, which i will make sure that i dont do it hereafter as i was confused where to post it exactly.Thanks ranamirfan, i will do the same.i will add a seperate layout in the same report.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 30 06:47:00 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
