Re: oracle report matrix

Just an idea:

Why not construct the SQL dynamically using a lexical parameter within the SQL that you construct in the AFter Parameter trigger code?

Then, depending on the users selection, you can build varying sql to solve your users' needs.

Email me if you need more detail in an explanation, Noah Wollowick
IT Consultant
noahclerk_at_aol.com

On Fri, 13 Nov 1998 charlie_c_at_my-dejanews.com wrote:

> 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 Fri Nov 13 1998 - 16:20:32 CET