Thread: record fields as columns in reports
Hi everybody,
I have a script that runs every night and produces a list of a few error
conditions and the number.
My manager would like it in a form where he can produce some charts in a
spreadsheet (that's probably why he's a manager ";-).
So the table I store the errormessages in has a format like this:
CREATE TABLE repport_history
( rundate date, errordescription character varying(255), number bigint
)
And I would like an output with something like:
06/22 06/23 06/24 06/25
ERROR1 10 10 9 8
ERROR2 250 300 220 200
ERROR3 4 2 0 0
(probably in csv or something like that but that is the easy part ";-)
The problems are:
- how to create a dynamic result type? (is that possible in a stored
procedure?)
- how to make sure that missing records are reported as 0 (some errors might not have entries on some dates)
Has somebody ever made a generic solution for something like this? (or do
I need a reporting tool?)
Thanks in advance,
Reinoud
--
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen reinoud.v@n.leeuwen.net
http://www.xs4all.nl/~reinoud
__________________________________________________
On Thu, Jun 28, 2007 at 04:46:15PM +0200, Reinoud van Leeuwen wrote: > And I would like an output with something like: > > 06/22 06/23 06/24 06/25 > ERROR1 10 10 9 8 > ERROR2 250 300 220 200 > ERROR3 4 2 0 0 I think this should be possible with the "crosstab" functionality delivered in Joe Conway's tablefunc package, in contrib/. A -- Andrew Sullivan | ajs@crankycanuck.ca I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin
On 6/28/07, Reinoud van Leeuwen <reinoud.v@n.leeuwen.net> wrote: > So the table I store the errormessages in has a format like this: > > CREATE TABLE repport_history > ( > rundate date, > errordescription character varying(255), > number bigint > ) > > > And I would like an output with something like: > > 06/22 06/23 06/24 06/25 > ERROR1 10 10 9 8 > ERROR2 250 300 220 200 > ERROR3 4 2 0 0 1. See contrib/tablefunc. 2. PivotTables (OpenOffice.org or Excel).
am Thu, dem 28.06.2007, um 16:46:15 +0200 mailte Reinoud van Leeuwen folgendes: > Hi everybody, > > I have a script that runs every night and produces a list of a few error > conditions and the number. > > My manager would like it in a form where he can produce some charts in a > spreadsheet (that's probably why he's a manager ";-). > > So the table I store the errormessages in has a format like this: > > CREATE TABLE repport_history > ( > rundate date, > errordescription character varying(255), > number bigint > ) > > > And I would like an output with something like: > > 06/22 06/23 06/24 06/25 > ERROR1 10 10 9 8 > ERROR2 250 300 220 200 > ERROR3 4 2 0 0 > > > (probably in csv or something like that but that is the easy part ";-) > > The problems are: > - how to create a dynamic result type? (is that possible in a stored > procedure?) > - how to make sure that missing records are reported as 0 > (some errors might not have entries on some dates) You can do it with conditionals. Circa: select errordescription, sum (case when rundate = '2007-06-22'::date then number else 0 end) as "06/22", sum(case when rundate = '2007-06-23'::date then number else 0 end) as "06/23" ... group by errordescription order by errordescription; and you can use a VIEW and change the fix conditions to expressions with current_date. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net