Board index » delphi » QuickReport - emulating a 'pivot table' function from Oracle dataset

QuickReport - emulating a 'pivot table' function from Oracle dataset

Hi

From Oracle we are generating yearly data from a group-by query yielding  
a result which looks something like this:

Year ID Count
 ------ --- -------
1996 1 10
1995 2 14
1994 3 22
1993 4 34  etc

in quick report we need to generate a report which looks like this:

year  1996 1995 1994 1993
count  10 14 22 34
ID  1 2 3 4

We're trying to avoid writing numerous SQL statements which populate temporary tables as this could be very
cumbersome.

Any suggestions as to how we might achieve this?

TIA
Lisa Ward
RTI
Auckland
New Zealand

 

Re:QuickReport - emulating a 'pivot table' function from Oracle dataset


Quote
Relational Technology International wrote:

> Hi

> From Oracle we are generating yearly data from a group-by query yielding
> a result which looks something like this:

> Year ID Count
>  ------ --- -------
> 1996 1 10
> 1995 2 14
> 1994 3 22
> 1993 4 34  etc

> in quick report we need to generate a report which looks like this:

> year  1996 1995 1994 1993
> count  10 14 22 34
> ID  1 2 3 4

> We're trying to avoid writing numerous SQL statements which populate temporary tables as this could be very
> cumbersome.

> Any suggestions as to how we might achieve this?

> TIA
> Lisa Ward
> RTI
> Auckland
> New Zealand

I have had the same problem.  My solution was to sum everything up in
code before printing.  But maybee you can use the Columns property of
the QuickReport component, that will limit the layout of your report but
maybee it works for you.  Or you have a hidden detail band that rolls
trough all the data and a visible summary band, you would have to keep
trac of the change in year (use a TQRDBText.OnPrint event for some
DBText on the Detail band) to change where you want the results printed.

BTW do you know of any report generator that handles this (printing
multiple detail records horizonally)?

Hope this helps

Arngrimur Sigmarsson
gri...@hugur.is

Other Threads