Home » Developer & Programmer » Reports & Discoverer » Matrix report (Oracle report 10g.)
Matrix report [message #543986] Sat, 18 February 2012 21:44 Go to next message
dophuong_cs
Messages: 92
Registered: May 2011
Location: Viet Nam
Member
Hi all, i must create 1 report/forum/fa/9859/0/
, however i feel very difficult when create sql command. I have two tables below:
-- Create table
create table MODEL
(
  model       VARCHAR2(90) not null,
  merchandise VARCHAR2(90)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table MODEL
  add constraint PRI_MODEL primary key (MODEL)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

with date of this table is:
insert into model (MODEL, MERCHANDISE)
values ('A', 'ABC1');

insert into model (MODEL, MERCHANDISE)
values ('B', 'ABC2');

insert into model (MODEL, MERCHANDISE)
values ('C', 'ABC3');

insert into model (MODEL, MERCHANDISE)
values ('D', 'ABC4');

insert into model (MODEL, MERCHANDISE)
values ('E', 'ABC5');

insert into model (MODEL, MERCHANDISE)
values ('F', 'ABC6');


The second table is
-- Create table
create table PALLET
(
  merchandise   VARCHAR2(90),
  slip_no       VARCHAR2(90) not null,
  date_entry    DATE,
  from_loc      VARCHAR2(90),
  to_loc        VARCHAR2(90),
  quarter       VARCHAR2(90),
  qty           NUMBER,
  pallet_id     NUMBER,
  pallet_status VARCHAR2(9),
  line_no       VARCHAR2(10)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table PALLET
  add constraint PRM_SLIP_NO primary key (SLIP_NO)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table PALLET
  add constraint UNI_PALLET_ID unique (PALLET_ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

with data this table is:
insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC1', 'SLIP1', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D1', 400, 20, 'S01', '1C');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC1', 'SLIP2', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'PDC', 'WH1', 'D2', 400, 21, 'S04', '1C');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC1', 'SLIP3', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D3', 400, 22, 'S04', '1B');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC1', 'SLIP4', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'PDC', 'WH2', 'D4', 400, 23, 'S01', '1D');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC1', 'SLIP5', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D5', 400, 24, 'S05', '2A');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC1', 'SLIP6', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'ASSY', 'WH3', 'D5', 400, 25, 'S01', '1A');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC1', 'SLIP7', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D4', 400, 26, 'S06', '1A');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC1', 'SLIP8', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D3', 400, 27, 'S01', '1D');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC1', 'SLIP9', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D1', 400, 28, 'S01', '1B');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC1', 'SLIP10', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D2', 400, 1, 'S01', '1A');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC1', 'SLIP11', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D4', 400, 2, 'S01', '1A');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC1', 'SLIP12', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D2', 400, 3, 'S01', '1A');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC1', 'SLIP13', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', '', 'D1', 400, 4, 'S01', '2A');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC1', 'SLIP14', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', '', 'D1', 400, 5, 'S01', '2A');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC2', 'SLIP15', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D1', 400, 6, 'S01', '1A');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC3', 'SLIP16', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'PDC', 'WH1', 'D2', 400, 7, 'S04', '1A');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC3', 'SLIP17', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D3', 400, 8, 'S04', '1B');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC2', 'SLIP18', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'PDC', 'WH2', 'D4', 400, 9, 'S01', '1C');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC2', 'SLIP19', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D5', 400, 10, 'S05', '1D');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC4', 'SLIP20', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'ASSY', 'WH3', 'D5', 400, 11, 'S01', '1A');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC4', 'SLIP21', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D6', 400, 12, 'S06', '1A');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC5', 'SLIP22', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D7', 400, 13, 'S01', '1A');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC4', 'SLIP23', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D8', 400, 14, 'S01', '1A');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC4', 'SLIP24', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D9', 400, 15, 'S01', '1B');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC3', 'SLIP25', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D10', 400, 16, 'S01', '1D');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC5', 'SLIP26', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D6', 400, 17, 'S01', '1C');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC5', 'SLIP27', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D7', 400, 18, 'S01', '2A');

insert into pallet (MERCHANDISE, SLIP_NO, DATE_ENTRY, FROM_LOC, TO_LOC, QUARTER, QTY, PALLET_ID, PALLET_STATUS, LINE_NO)
values ('ABC4', 'SLIP28', to_date('07-01-2012 11:59:00', 'dd-mm-yyyy hh24:mi:ss'), 'WH1', 'NYK', 'D8', 400, 19, 'S01', '2A');

With table "pallet" field "pallet.quarter" is " Shift DAY" if this field value in('D1','D2','D3','D4','D5'), else "pallet.quarter" is "Shift Night" if this field value in('D6','D7','D8','D9','D10').
Field "shift" on Report equivalent with field "pallet.quarter" above.
Field "quantity day" or "quantity night" on report equivalent with field "pallet.qty" in table "Pallet".
Now, i want to make report above, how to create command sql from two tables above.
Please help me.
  • Attachment: report.jpg
    (Size: 37.55KB, Downloaded 2481 times)

[Updated on: Sat, 18 February 2012 21:52]

Report message to a moderator

Re: Matrix report [message #544028 is a reply to message #543986] Sun, 19 February 2012 07:49 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, it doesn't look like a matrix report to me, but an ordinary tabular report (with a group above, having date & shift information as group columns).

So, try to write an ordinary query that will select values you need. Once it does it correctly, copy/paste it into Reports Builder, choose "Group above" layout and ... follow the wizard.

Report message to a moderator

Re: Matrix report [message #544031 is a reply to message #544028] Sun, 19 February 2012 08:11 Go to previous messageGo to next message
dophuong_cs
Messages: 92
Registered: May 2011
Location: Viet Nam
Member
Thank you already reply me, you can write 1 example for me clearly.
Thanks.

[EDITED by LF: removed unnecessary quote of the whole previous message]

[Updated on: Sun, 19 February 2012 08:24] by Moderator

Report message to a moderator

Re: Matrix report [message #544034 is a reply to message #544031] Sun, 19 February 2012 08:23 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I could try, but - why would I do that? Why wouldn't YOU do it? Try the best you can. If it still doesn't work, return back here, post what you did and someone will assist.

Report message to a moderator

Re: Matrix report [message #544270 is a reply to message #543986] Tue, 21 February 2012 00:30 Go to previous message
dophuong_cs
Messages: 92
Registered: May 2011
Location: Viet Nam
Member
Hi all, i already coding same below:
Select p.merchandise, p.line_no, p.old_loc, p.current_loc, p.pallet_qt,
       decode(p.quarter_id,
               '11',
               'Day',
               '12',
               'Day',
               '13',
               'Day',
               '14',
               'Day',
               '15',
               'Day',
               '21',
               'Night',
               '22',
               'Night',
               '24',
               'Night',
               '24',
               'Night',
               '25',
               'Night',
               'Unidentified'
               ) shift, p.pallet_status
From crg_tbl_pallet p
Where trunc(p.date_entry) = to_date(&Day, 'dd/mm/rrrr')

Result of command above follow:
/forum/fa/9867/0/
With "pallet_status"='S04' equivalent "SHIPPING".
Now i want to display report same template, how do i do?
  • Attachment: result.jpg
    (Size: 81.64KB, Downloaded 2475 times)

Report message to a moderator

Previous Topic: dates as columns
Next Topic: Week day starts from monday
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 30 05:08:25 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.