Re: oracle report matrix

Hi

If the number of columns is variable the type of layout must be matrix.

A query like :

select team, sales_person, to_char(sales_date,'mon-RR') mth, sum(amount)
from sales_record
where sales_date between :P1 and :P2
group by team, sales_person, to_char(sales_date,'mon-RR')

/* where P1 and P2 are the 2 dates that define the accounting period */

gets close to what you are seeking.

The disadvantage of the above query is that it will not report on a month without any data. Also the above query produces rows for Team/sales_persons that are empty.

To avoid this I believe you will have to produce a 3 query matrix report.

Hope this helps you.

eric
charlie_c_at_my-dejanews.com wrote in message <72hh7r$680$1_at_nnrp1.dejanews.com>...
>Hi:
>
>I have a similary problem that Thenard had posted on Nov 3, 1998.
>The difference is that I need to have the report display arbitrary number
of
>months that will be defined by the user. And I want to have the total sale
by
>the team and the total for the report in that month.
>
>The table is defined as:
>table sales_record(
> team varchar2[3],
> sales_person varchar2[25],
> sales_date date,
> amount number)
>
>the data:
> team sales_person sales_date amount
> A salesA 01-sep-98 10
> A saelsA 04-sep-98 3
> A salesA 11-nov-98 5
> A salesB 12-sep-98 6
> A salesB 08-aug-98 7
> B salesC 04-sep-98 1
> B salesC 07-nov-98 6
>...
>
>the report should look like:
> Team 01-aug-98 01-sep-98 01-oct-98 01-nov-98
> sales_person
>------------------------------------------------------------
> A 7 19 0 5
> salesA 0 13 0 5
> salesB 7 6 0 0
> B 0 1 0 6
> salesC 0 1 0 6
> Total 7 20 0 11
>
>if the number of the columns is fixed (last four months), then it can be
>solved with following query:
>
> select team, sales_person,
> sum(decode(trunc(sales_date, 'mon'),
> trunc(add_month(sysdate, -3), 'mon'), amount, 0)) '01-aug-98'
> sum(decode(trunc(sales_date, 'mon'),
> trunc(add_month(sysdate, -2), 'mon'), amount, 0)) '01-sep-98'
> sum(decode(trunc(sales_date, 'mon'),
> trunc(add_month(sysdate, -1), 'mon'), amount, 0)) '01-oct-98'
> sum(decode(trunc(sales_date, 'mon'),
> trunc(sysdate, 'mon'), amount, 0)) '01-nov-98'
> from sales_record
> where sales_date between '01-aug-98' and sysdate
> group by team, sales_person;
>
>I can add summary fields to calculate the total for the team and the
report.
>and use master-detail layout with some modification to produce the report.
>
>However, what if the user want to display sales record in the last 6
months,
>or maybe last year, or an arbitrary months during the runing time, How can
I
>solve it?
>
>Thanks in advance for any help.
>
>
>Charlie Chu
>
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Mon Nov 16 1998 - 23:16:33 CET