Re: [SL] Output reports to csv

[Craig I. <cr...@da...>]

EL,

This was very helpful for me, thank you for posting it.  Before I spend a
bunch of time on this - can you tell me how much of an undertaking it would
be to pull monthly P&Ls into a CSV like your example below?  My goal is to
output a report that would show all 12 months individually.

Thanks!
Craig

> -----Original Message-----
> From: sql...@li... [mailto:sql-ledger-
> use...@li...] On Behalf Of Dr Eberhard Lisse
> Sent: Thursday, March 01, 2007 11:23 PM
> To: sql...@li...
> Subject: Re: [SL] Output reports to csv
> 
> Actually this is wrong.
> 
> psql -AtF, databasename < gl.psql > gl.csv will produce CSV.
> 
> and gl.psql looking like
> 
> SELECT  c.accno, c.description, ac.transdate, g.id,
>     round(ac.amount::numeric,2) as Amount,
>     g.reference,
>     g.description, substr(g.notes,1,55) as Notes,
>     ac.memo
> 
> FROM gl g
> JOIN acc_trans ac ON (g.id = ac.trans_id)
> JOIN chart c ON (ac.chart_id = c.id)
> LEFT JOIN department d ON (d.id = g.department_id)
> WHERE 1 = 1
>     AND ac.transdate >= '2005-06-01'
>     AND ac.transdate <= '2006-06-30'
> 
> UNION ALL
> 
> SELECT c.accno,  c.description, ac.transdate, a.id,
>     round(ac.amount::numeric,2) as Amount,
>     a.invnumber,
>     ct.name, substr(a.notes,1,55) as Notes,
>    ac.memo
> FROM ar a
> JOIN acc_trans ac ON (a.id = ac.trans_id)
> JOIN chart c ON (ac.chart_id = c.id)
> JOIN customer ct ON (a.customer_id = ct.id)
> LEFT JOIN department d ON (d.id = a.department_id)
> WHERE 1 = 1
>    AND ac.transdate >= '2005-06-01'
>    AND ac.transdate <= '2006-06-30'
> 
> UNION ALL
> 
> SELECT c.accno,  c.description, ac.transdate,  a.id,
>     round(ac.amount::numeric,2) as Amount,
>     a.invnumber,
>     ct.name, substr(a.notes,1,55) as Notes,
>    ac.memo
> FROM ap a
> JOIN acc_trans ac ON (a.id = ac.trans_id)
> JOIN chart c ON (ac.chart_id = c.id)
> JOIN vendor ct ON (a.vendor_id = ct.id)
> LEFT JOIN department d ON (d.id = a.department_id)
> WHERE 1 = 1
>    AND ac.transdate >= '2005-06-01'
>    AND ac.transdate <= '2006-06-30'
> 
> ORDER BY 1, 3, 4;
> 
> does it for my auditor :-)-O
> 
> el
> 
> on 3/1/07 10:42 PM C. Duncan Hudson said the following:
> > David Boyle wrote:
> >> On Thu, 2007-03-01 at 09:17 -0800, Wray Cason wrote:
> >>
> >>> My bookkeeper wants to be able to crunch some numbers from my SQL
> Ledger
> >>> database in a spreadsheet.  Is there any established way to output
> reports
> >>> to a csv file or something similar?
> >>>
> >> I imagine there is...but copy/paste works pretty well and is easy.
> >>
> >> David Boyle
> >> NAPCO (Northwest Arkansas Paper Company)
> >>
> >>
> > There isn't, actually, a way to output to any kind of file.  The
> > technical solution is cut and paste, which I think leaves a lot to be
> > desired.  I use Postgres SQL Manager to generate reports.  It would be
> > nice if the functionality was built in, as that's not a tool I give to
> > any of my users.
> >
> > Dunc
> 
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share
> your
> opinions on IT & business topics through brief surveys-and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> sql-ledger-users mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sql-ledger-users