Home -> Usenet -> comp.databases.oracle -> Re: Matrix Reports

Re: Matrix Reports

From: <fgreene_at_hayes.com>
Date: 7 Jun 93 07:23:42 EDT
Message-ID: <7401.2c12ecfe_at_hayes.com> 







In article <1993Jun03.183047.17903_at_ncrcae.ColumbiaSC.NCR.COM>, rgass_at_yesac.ColumbiaSC.NCR.COM (Roy.Gass) writes:


> Is there a simple way to produce trend matrix reports (for lack


> of a better term) in SQL. SQL*ReportWriter enables simple

> schemes, but I want a little more abstraction.

> 

> 	For example: (Product order rate)

> 

> 			01/93 	02/93	.	.	NN/NN

> 	

> 	Product A 

> 

> 	Product .

> 

> 	Product X

> 

> Thanks.

> 

> roy.

> 

> 

> 

Oracle will support matrix reporting in their new Report tool.  A while ago
I had to develop something along those lines for a client site.  Here is a
frangment of the code I used.  It takes input from one table and spreads
it across a 36 month max span.





 |      Frank Greene                  |          _/_/_/  _/_/_/             |
 |      DELPHI SYSTEMS, Inc.          |           _/_/    _/_/              |
 |      Telephone [615] 458-6032      |          _/_/    _/_/  _/_/_/       |
 |      Compuserve 74200,427          |         _/_/    _/_/    _/_/        |
 |      324 Ootsima Way               |        _/_/    _/_/    _/_/         |
 |      Loudon, TN 37774              |       _/_/_/  _/_/_/  _/_/_/        |
 ----------------------------------------------------------------------------
 |         Of course, any opinions or suggestions are strictly my own       |
 ----------------------------------------------------------------------------

rem	&1	sequence number
rem	&2	source table
rem	&3	vertical column
rem	&4	base date
rem	&5	top date
rem	&6	horizontal column (must be a date)
rem	&7	matrix column





rem insert new values into the matrix table


insert into matrix_work (xcase,	left )
	select distinct	&1,  &3	
	from		&2;
			




rem insert new values into the vertical column cross reference table



declare

	xbase_date 	date := '&4';
	xend_date	date;
	xtop_date	date := '&5';
	loop_control	number := 1;
begin
	while loop_control = 1 loop
		xend_date := add_months(xbase_date, 1);
		xend_date := xend_date - 1;
		--
		insert into vert_col (xcase, base_date, top_date)
		values(1, xbase_date, xend_date);
		--
		xbase_date := add_months(xbase_date, 1);
		--
		if xbase_date > xtop_date then
			loop_control := 0;
		end if;
	end loop;




end;


.


/



rem set the column identifiers to synch matrix_work to vert_col



update vert_col


set    xref = 'C' || rownum;



rem now fill all 36 columns of table matrix_work with values
rem this is one humongus select statement


define mattbl = '&2';
define mathor = '&6';
define matver = '&3';
define matcol = '&7';
define matseq = &1; 





set verify off;



update matrix_work set

	c1  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C1'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c2  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C2'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c3  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C3'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c4  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C4'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c5  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C5'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c6  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C6'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c7  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C7'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c8  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C8'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c9  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C9'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c10  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C10'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c11  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C11'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c12  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C12'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c13  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C13'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c14  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C14'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c15  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C15'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c16  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C16'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c17  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C17'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c18  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C18'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c19  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C19'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c20  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C20'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c21  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C21'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c22  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C22'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c23  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C23'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c24  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C24'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c25  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C25'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c26  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C26'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c27  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C27'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c28  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C28'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c29  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C29'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c30  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C30'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c31  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C31'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c32  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C32'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c33  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C33'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c34  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C34'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c35  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C35'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq), 
	c36  =	(select nvl(sum( &matcol ), 0)
		from	&mattbl a, vert_col b
		where	b.xref	= 'C36'
		and	a.&mathor between b.base_date and b.top_date
		and	matrix_work.left = a.&matver
		and	matrix_work.xcase = &matseq); 





rem end of the program


rem


exit
Received on Mon Jun 07 1993 - 13:23:42 CEST












Original text of this message