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 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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 #564575 is a reply to message #564574] Sun, 26 August 2012 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is your desired output?

Also, PLEASE, put a ";" at the end of your statements.

Regards
Michel

[Updated on: Sun, 26 August 2012 02:03]

Report message to a moderator

Re: Detail and Summary in One report [message #564577 is a reply to message #564575] Sun, 26 August 2012 02:16 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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 #564579 is a reply to message #564577] Sun, 26 August 2012 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
well i need both the outputs to be displayed in one report


Both what? Put the result you want for the data you gave.

Regards
Michel
Re: Detail and Summary in One report [message #564586 is a reply to message #564579] Sun, 26 August 2012 05:18 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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 #564587 is a reply to message #564586] Sun, 26 August 2012 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again, what should be the final result you want? Is it so difficult t post it as you posted the following?
MACH_CODE    BATCH_CODE      MAX(A)                                                                 
------------ ------------ ---------                                                                 
L2           01                   4                                                                 
L1           01                   6     

How can we produce a query for a result we don't know how it is?
Note that you cannot merge 2 queries in the same result when they have not the same number of columns.

Regards
Michel
Re: Detail and Summary in One report [message #564590 is a reply to message #564587] Sun, 26 August 2012 05:38 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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 #564591 is a reply to message #564543] Sun, 26 August 2012 06:25 Go to previous messageGo to next message
ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member

For Summary you need to add another layout for this use additional Layout shown below screenshot.

/forum/fa/10388/0/

Hope it'll help you.


Regards,
Irfan
Re: Detail and Summary in One report [message #564592 is a reply to message #564591] Sun, 26 August 2012 06:29 Go to previous messageGo to next message
ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member

Report Output

/forum/fa/10389/0/

Regards,
Irfan
  • Attachment: Output.PNG
    (Size: 23.96KB, Downloaded 1750 times)
Re: Detail and Summary in One report [message #564594 is a reply to message #564590] Sun, 26 August 2012 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't know what you want we can't tell you how to do it.

Regards
Michel
Re: Detail and Summary in One report [message #564596 is a reply to message #564594] Sun, 26 August 2012 07:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And do NOT multipost your question in several topics across severak forums otherwise it is a mess.

Regards
Michel
Re: Detail and Summary in One report [message #564598 is a reply to message #564596] Sun, 26 August 2012 08:28 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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.
Re: Detail and Summary in One report [message #564601 is a reply to message #564592] Sun, 26 August 2012 08:38 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks irfan
Re: Detail and Summary in One report [message #564604 is a reply to message #564601] Sun, 26 August 2012 08:56 Go to previous message
ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member

Thanks for your feedback.
Previous Topic: rep-0108
Next Topic: Live Previewer
Goto Forum:
  


Current Time: Thu Mar 28 11:36:07 CDT 2024