oracle report matrix

From: <charlie_c_at_my-dejanews.com>
Date: Fri, 13 Nov 1998 14:54:51 GMT
Message-ID: <72hh7r$680$1_at_nnrp1.dejanews.com>

Hi:

[Quoted] [Quoted] I have a similary problem that Thenard had posted on Nov 3, 1998. [Quoted] The difference is that I need to have the report display arbitrary number of [Quoted] [Quoted] months that will be defined by the user. And I want to have the total sale by [Quoted] the team and the total for the report in that month.

The table is defined as:
[Quoted] 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. [Quoted] [Quoted] 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, [Quoted] [Quoted] or maybe last year, or an arbitrary months during the runing time, How can I solve it?

[Quoted] Thanks in advance for any help.

[Quoted] Charlie Chu

[Quoted] -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Nov 13 1998 - 15:54:51 CET