postgresql reporting

[Gregory Gincley]

Hi there,

I've posted my postgresql reporting and JasperReport files to gitlab.

https://gitlab.com/rollenwiese/gnucash_reporting/

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 use postgres 9.4.1 on Arch Linux, and JasperStudio 6.0.4 within a
windows VM. You will need Postgres >= 9.3 due to the use of
materialized views.

-

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.

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).

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.

There are others I'm forgetting.

*Also, I do not use trading accounts, they are ignored throughout*

-

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 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.

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.


-Greg