Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Matrix Report Headache
On Tue, 03 Nov 1998 14:36:16 GMT, tkyte_at_us.oracle.com (Thomas Kyte)
wrote:
>>i'm working on a report which shows the total sales figures of each
>>sales person for the last 12 months. i want the report to look like this:
>> (current mth)
>>Name 12/1997 01/1998 02/1998 ... ... 11/1998 Total
>>------ ------- ------- ------- ... ... ------- -------
>>SalesA 10 10 20 ... ... 1 999
>>SalesB 10 10 20 ... ... 1 999
>>SalesC 10 10 20 ... ... 1 999
>You don't say what your original table looks like but assuming it looks
>something like:
hi thomas, first i wanna thank u for answering my questions before. :) in the question i sent, i forgot to tell u guys a very very important point - that matrix report is built using ora report builder 2.5. my boss didn't tell it's gonna run in sqlplus. :P
the report is quite complex to me. the info above mainly comes from 2 tables:
table sales type A (extract)
...
super_client_id number, <-- this link to super client table
salesmgr_id number, txn_date date, amount number,
table sales type B (extract)
...
normal_client_code varchar2(10), <-client code's already here
salesman_id number, txn_date date, amount number,
these 2 tables are supposed to joint together with UNION ALL all sales txn are recored when there's a transaction. for some reasons, there can be no txn at all for a whole month.
then there is the real report format (say i'm gonna list the monthly txn for the last 24 months
12/1996 01/1997 02/1997 ... ... 11/1997 sales ------- ------- ------- --- --- ------- person 12/1997 01/1998 02/1998 ... ... 11/1998 Total Average ------ ------- ------- ------- --- --- ------- ----- -------
SalesA 10 10 0 ... ... 10 100 100 <-LY 10 10 0 ... ... 10 100 100 <-TY SalesB 10 10 0 ... ... 10 100 100 <-LY 10 10 0 ... ... 10 100 100 <-TY SalesC 5 5 0 ... ... 10 100 100 <-LY 10 10 0 ... ... 10 100 100 <-TY...
LY=Last Year; TY=This Year
hope this can give u clearer picture. thanx!!!!! :)
rgds,
thenard