Re: Reports 2.5, how to do this please?

From: Paul Dorsey <pdorsey.dulcian_at_worldnet.att.net>
Date: 28 Aug 1998 03:10:19 GMT
Message-ID: <6s572r$g7n_at_bgtnsc02.worldnet.att.net>


You can do it much easier in Reports.
It is a no brainer matrix report.
Just follow the examples in the book.

You can also do it with 2 breaks:
1 for customer and one for period
your query will look like:

query
|
period
|
customer
|
sale

put sale totals in both of the break groups. cust repeats down, period across

Reports is such a powerful tool that if you use temporary tables more than 1% of the time, you do not understand the product.

--
Paul Dorsey
Dulcian, Inc.
www.dulcian.com
212 595 7223
Belakimem wrote in message
<1998082717522100.NAA12364_at_ladder03.news.aol.com>...

>Hi all, I am still frantically trying to learn Reports 2.5 / Oracle in
general!
>
>I was thinking to myself what relatively complicated thing could I do in
>Informix that would be nice to know how to do in Oracle?
>
>So, a simple example/thought :
>
>table
>-----
>
>my_table
>
>columns
>-------
>
>customer
>year
>period
>sales
>
>(possibility of multiple entries for same period)
>
>A report showing sales in a year across periods for a customer.
>
>Year : xx
>
> period
>
>customer 1 2 3 .......... 12 total
>
>IBM 29 0 56 3 535
>dec 24 15 40 0 648
>
>total xx xx xx xx xxx
>
>
>In Informix I would have done it like this :
>
>declare a cursor for
>
>select customer, year, period, sum(sales)
>from my_table where year = 97
>group by customer, year, period
>order by customer, year, period
>
>create a temporary table temp_table ( ** do they exist in oracle? ** )
>
>with columns
>
>customer,period_1_sales,period_2_sales......,period_12_sales
>
>and add an index to the customer field
>
>then I would read through the cursor in a loop until notfound
>
>and before every group of customer I would insert a row into temp_table
with
>values of customer and zeros for all the sales figures.
>
>for every row in the cursor I would update the corresponding customer row
in
>the temporary table with the appropriate sales figure for that period.
>Sometimes there would be no sales for that period and the column would
remain
>zero. There would only be one update per column because of the grouping.
>
>After processing the cursor I would then generate a report, as above, based
on
>the temporary table.
>
>Obviously a nice addition would be to be able to enter a year as a run time
>parameter.
>
>Phew!, get it? Now, how to do it in Oracle! I don't expect complete
answers -
>just a few pointers.
>
>Thanks :)
>
>
>
Received on Fri Aug 28 1998 - 05:10:19 CEST

Original text of this message