postgresql reporting

[Wm]

Mon, 25 May 2015 19:27:10 <1432596430.5453.21.camel at fastmail.net> 
Gregory <rollenwiese at fastmail.net>

>I just wanted to share in case someone could take away bits and pieces
>and use for themselves. If there any any suggestions I'm happy to
>review and incorporate them.

I got around to trying this out yesterday and today.

Suggestion re: build-db.sh and the base_views scripts

my pgsql didn't like some or all of
===
ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'
===
so I just left it out (I'm in the UK) and haven't noticed any ill 
effects.

in the views/reporting.xxx.sql scripts I used
===
dba.dblink('dbname=gnucash_production password=postgres 
port=5432'::text,
===
it might make it a bit easier for the next person if the dbname, 
password and port were parameters in build-db.sh

System
postgres 9.4 under win 8.1 x64
ran build-db.sh from a Lubuntu 14.04 x64 VirtualBox VM with postgres 9.3 
(the version difference didn't cause any problems apart from a warning).

d/l and installed JaspersoftStudio 6.1.0 for win
as mentioned before this was completely new to me
added a JDBC connection to the db [1] containing the reporting views
and I have just produced a balance sheet that looks about right!

So, yay!  Well done you and thanks.

I'm going to explore your reports and when I get around to it see if I 
can do the whole thing in sqlite3 as that is the way forward for gnc in 
general.  I can't see any stoppers from my reading of the scripts, etc. 
(postgres and sqlite3 are better friends in terms of sql capabilities 
than MySQL) and Jasper can JDBC to sqlite3 so I'm not foreseeing any 
problems.

[1] I was feeling unimaginative so my db is called greg at the moment :)

-- 
Wm...