postgresql reporting

[Gregory]

On Sat, 2015-05-23 at 02:03 +0100, Wm wrote:
> Tue, 19 May 2015 12:25:12 <1432052712.1415.52.camel at fastmail.net> 
> Gregory Gincley <rollenwiese at fastmail.net>
> 
> > Hi there,
> 
> Hello <fx:me waves>
> 
> > I've posted my postgresql reporting and JasperReport files to 
> > gitlab.
> > 
> > https://gitlab.com/rollenwiese/gnucash_reporting/
> 
> I d/l the zip this evening and I've been having an enjoyable read of 
> the 
> postgresql parts (Jasper will be new to me).  There is some lovely 
> stuff 
> there, thank you very much for sharing.
> 
> > I also have a small java program that compiles/fills the reports 
> > and
> > combines them into a single PDF file. This is not yet in the
> > repository, although it is one of the most useful pieces of the 
> > setup.
> 
> I've been reading shell & SQL mainly so I'm not missing that yet.
> 
> > I use postgres 9.4.1 on Arch Linux, and JasperStudio 6.0.4 within a
> > windows VM.
> 
> I'm ditto under Win and I have Lubuntu native and VM handy.  No 
> Jasper.
> 
> > You will need Postgres >= 9.3 due to the use of
> > materialized views.
> 
> I can sort of see why you might want materialized views but I'm not 
> convinced about dblink due to the fact that postgres is the least 
> tested 
> of the backends and I'm still getting some transactions occurring on 
> the 
> wrong day [1] type of error, i.e. your safety level exceeds the 
> gnc+postgres confidence level if that makes sense.
> 
> [1] not reported as they don't repeat easily, seasonal time change 
> isn't 
> the problem, times that are OK in xml and sqlite are sometimes wrong 
> in 
> postgres, not postgres's fault, just a fact of the backend being 
> least 
> tested
> 
> > This is just an example for anyone to check out, to see what's
> > possible, or use as a reference for their own work. The 
> > JapserReports
> > library definitely allows for the creation of beautiful PDF/HTML 
> > etc
> > reports and it was worth the time spent to create everything. Maybe
> > someone else can gain from that as well.
> 
> At the moment I am admiring your SQL, presentation has always been an 
> 
> afterthought for me ... that is what meetings are for :)
> 
> > The reports are somewhat generic, but there are certainly 
> > assumptions
> > within the code that are specific to my account hierarchy...(or 
> > else I
> > would not be using SQL).
> 
> Yes, unfortunately for that reason I don't think you should expect 
> general acceptance.
> 
> > For example in my account structure any account with a depth of 
> > four in
> > hierarchy is considered a 'group' of sorts. I could and will 
> > eventually
> > move this logic into a separate view so it can be made more 
> > generic.
> > 
> > The fuel expenses view looks for a specific account and tries to 
> > parse
> > the transaction description for ':' separated values in order to
> > calculate the gas mileage.
> > 
> > I'm very careful about the account codes, and sorting by account 
> > code
> > ascending will list the accounts in the order they appear in the
> > hierarchy.
> 
> I don't use account codes at all.
> 
> > There are others I'm forgetting.
> 
> too many to mention (some people don't rent, etc.)  I also say ignore 
> 
> this, you have produced good work.
> 
> > *Also, I do not use trading accounts, they are ignored throughout*
> 
> I use them a lot and so (for that and other reasons) your postgres 
> can't 
> be generally used directly by anyone else.
> 
> However, I'm presuming you are familiar with Peter Keen ?  If we can 
> get 
> this all into sqlite (most of postgres advanced views are available 
> in 
> sqlite) then we have a presentable reporting solution for more people 
> to 
> play with.
> 
> > -
> > 
> > It's been accurate compared to the reporting in the application for 
> > my
> > purposes, and I have many years of data in my gnucash database.
> 
> I still get weird dates being out in postgres compared to XML and 
> SQLite.
> 
> > I do
> > periodically check the SQL results against the application reports. 
> > The
> > only real difference I've noted within the reporting database, is 
> > the
> > unrealized gain calculation is off by a few cents for some reason.
> > Probably rounding issue somewhere.
> 
> do you have deep decimals in any currency or commodity ?
> 
> > Again, no guarantees this will work for you at all, in fact some of 
> > it
> > will definitely not work for you but it shouldn't hurt your 
> > existing
> > database. Always proceed with caution, backup, etc.
> 
> I think you have been over cautious but at least one person has done 
> a 
> bit more than read it.
> 
> Thank you
> 

Hi Wm, appreciate the compliments. I've had the scripts in on form or
another for a few years. Went from mySQL>MS SQL Server>postgres.
Finally putting them in version control gave me a reason to church them
up a bit.

I certainly don't expect any level of general acceptance. However I
have little interest in converting anything to sqlite. I don't know who
ayPeter Keen is, but if he has any gnucash sql work I'm always
interested in checking something like that out. Also, It's probably
possible to still use all of this using postgres foreign data wrappers
to somehow read from sqlite.

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 did add the two java classes for compiling/aggregating the JRXML
files into a single PDF. 

I'll have to add some documentation for them.

Main program:
https://gitlab.com/rollenwiese/GnucashJasperReporting

A customizer class for one of the Jasper reports:
https://gitlab.com/rollenwiese/JasperReportCustomizerClasses


Anyway maybe getting a bit off topic. I'm happy to discuss further off
list.

Thanks,
Greg