Re: Matrix Report Headache

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/11/03
Message-ID: <364851d6.20623354@192.86.155.100>#1/1

A copy of this was sent to Thenardier_at_pk.POBoxes.com (ªü´ö Thenardier) (if that email address didn't require changing) On Tue, 03 Nov 1998 15:58:58 GMT, you wrote:

>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,
>...
>

Well, if you union all the tables together, then the query would be:

SQL> select person,
  2 sum(decode( trunc(salesdate,'mon'), trunc(add_months(sysdate,-06),'mon'), amount, 0 ))"6m ago",   3 sum(decode( trunc(salesdate,'mon'), trunc(add_months(sysdate,-05),'mon'), amount, 0 ))"5m ago",   4 sum(decode( trunc(salesdate,'mon'), trunc(add_months(sysdate,-04),'mon'), amount, 0 ))"4m ago",   5 sum(decode( trunc(salesdate,'mon'), trunc(add_months(sysdate,-03),'mon'), amount, 0 ))"3m ago",   6 sum(decode( trunc(salesdate,'mon'), trunc(add_months(sysdate,-02),'mon'), amount, 0 ))"2m ago",   7 sum(decode( trunc(salesdate,'mon'), trunc(add_months(sysdate,-01),'mon'), amount, 0 ))"1m ago",   8 sum(decode( trunc(salesdate,'mon'), trunc(add_months(sysdate,-00),'mon'), amount, 0 ))"This month"   9 from ( select salesmgr_id PERSON, txn_date SALESDATE, amount from salesA

            union all
            select salesman_id PERSON, txn_date SALESDATE, amount from salesB)

 10 group by person
 11 /

That will generate that entire matrix report. Just add more columns subtracting months from sysdate to go back further and further in time...

>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
>...
>...
>------ ------- ------- ------- --- --- ------- ----- -------
>Total ....
>
>LY=Last Year; TY=This Year
>
>hope this can give u clearer picture. thanx!!!!! :)
>
>
>rgds,
>thenard
>-------
>pls remove pk. in reply, thanx.
>¦^ÂЮɽЧâpk. K ±¼, ÁÂÁÂ.
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

Received on Tue Nov 03 1998 - 00:00:00 CST