Re: Cross-tab Reports

[Ray Ontko <rayo@ONTKO.COM>]

>      Ray's suggestion is a good one for complex reports, but one thing you
> may want to be aware of is an Oracle 7.3 feature (assuming you have Oracle)
> that makes for pretty simple cross-tab reporting.  Most of the work is done
> in the SQL statement, observe:
>
> begin-select
> tot_gross_amt,
> tot_freight
> from (select sum(gross_amt) as tot_gross_amt,
>   sum(freight_amt) as tot_freight
>      from ps_voucher)
> end-select
>
>      This will give you two numbers in a horizontal (cross tab) fashion.
> Of course, this is a simple example and you could further enhance the sql
> with group by, min(), max() yada yada yada.  Other databases may have a
> similar technique.

Bob,

How is your program different from this?

   begin-select
   sum(gross_amt) &tot_gross_amt
   sum(freight_amt) &tot_freight
   from ps_voucher
   end-select

I'm not understanding how this technique could be used to
give a variable number of columns.

I think what we're looking for is the ability to do:

   select
      vendor ,
      to_char(ship_date,'yyyy') ,
      sum(gross_amt)
      sum(freight_amt)
   from
      ps_voucher
   group by
      vendor , to_char(ship_date,'yyyy')
   order by
      vendor , to_char(ship_date,'yyyy')

for example, and produce a report that lists vendors down the
left, years across the top, and two numbers in each 'cell' (the
gross and freight amounts for that vendor in that year).

I'll admit that I haven't done a lot with the computed table
feature of oracle (I usually create a view), but I can't
think of a way to get oracle to return a variable number of
columns (or how SQR would handle it even if oracle could).

Did I misunderstand your example, or am I confused about
what "from ( select ...)" means?

Ray
----------------------------------------------------------------------
Ray Ontko       |  Ray Ontko & Co  |  "Time for a new signature line."
rayo@ontko.com  |  Richmond, In    |  See us at http://www.ontko.com/