Re: Matrix Reports

In article 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.
>
>
>

We do something like this to create a matrix report in SQL*PLUS:

define yymm1 = '01/93'
.
.
define yymm12 = '12/93'

column yymm1 heading '&YYMM1' null 'N/A' .
.
column yymm12 heading '&YYMM12' null 'N/A' column ytd heading 'Y-T-D' null ' '

select a.product,

       sum(decode(b.yymm, '&YYMM1' , b.product_orate, null)) yymm1,
       sum(decode(b.yymm, '&YYMM2' , b.product_orate, null)) yymm2,
       .
       .
       sum(decode(b.yymm, '&YYMM12', b.product_orate, null)) yymm12,
       avg(b.product_orate) ytd
from products a,
     product_order_rates b
where a.product = b.product(+)                /* outer join to return all products */
  and b.yymm(+) between '&YYMM1' and '&YYMM2' /* even if no rate.                  */

product   01/93  02/93                            12/93  Y-T-D
-------   -----  -----                            -----  -----
widgets     .50    .55                              N/A   .525
apples      .44    .33                              N/A   .385

Using the sum group function 'compresses' 12 rows of product data into 1 row. You could return a '0' instead of 'null' at the end of the decode, but 'null' allows you to return a character value (such as N/A, or blanks).  

Obviously, the number of months 'across' the report is pre-determined, but you can get clever with setting up your variables and build in some flexibility.

Hope this helps.

Tony Damon Received on Wed Jun 09 1993 - 02:19:42 CEST