Re: Cross-tab Reports

[Bob Hearon <Robert_B._Hearon@AEP.COM>]

To:       SQR-USERS @ list.iex.net
cc:        (bcc: Robert B. Hearon/American Electric Power)
From:     rayo @ ONTKO.COM
Date:     09/18/97 11:36:21 AM
Subject:  Re: Cross-tab Reports

Ray, you are absolutely right about Oracle returning a dynamic number of
columns.  The only way around that might be to have a table with column
names in it from which to build the cross-tab report.  And I think Sam was
talking about something like that.  As for the differences between your SQL
and mine, you're right, there aren't any.  This was my fault for picking
such a simple example.  I did this about 1 year ago and it had unions and
subselects and a whole bunch of stuff in it and worked fairly well this was
what I was talking about when I said yada yada yada.  Unfortunately, I
can't find the code, but when I do, I'll send it to you.  The dynamic
select feature is just a bit more flexible than creating a view and allows
for more complex reporting.



>      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/