postgresql reporting

[Wm]

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

-- 
Wm...