If GnuCash got one downside it is the report generation. But you can do your own custom reports using SQL and MySQL.
GnuCash and the list of members
I am using GnuCash to book-keep financial transactions for a sports association. It’s a non-profit organization for weight training much like a gym. The members can use the equipment and facilities while they got a valid membership subscription.
The problem is as follow. The subscription is extended one, six or 12 months from the date of the purchase. That is, the subscription often goes from one year into the next.
The subscription fee differs for youths and adults. Adults pay 750, 375 or 115 SEK for a year, six month or one month. The youths pay 450, 225 or 115 SEK for a year, six months or one month.
The one year subscription is recorded under an account with the code 3001. The account code 3002 is used for six months and the account code 3003 is used for one month subscription. The adults and the youths are both recorded under the same accounts above.
To compile a list of valid members you have to search these three accounts above, but all with different date criteria. You have to search 12, six or one month back from the current date.
The problem with GnuCash reports
GnuCash is an excellent and free software to keep your finances in control. It has several useful features that help you with your money. But I don’t like is the reports in GnuCash. You can print them to printer or pdf, or you can export them to html, that’s it. You have no easy way to control the formatting. On my printer the text is printed without any left margin at all. If you export to html, open the report in a browser and then print you get a decent margin.
Another thing that annoys me is that you can’t print a single transaction on one row in most reports. You can’t print the debit and the credit account on the same line, they are often printed on separate lines. In this example this is not the problem, but for other reports it can be. You have to have a lot of paper to print a large GnuCash report.
In this example you can without problem find each of the accounts individually within GnuCash. But when you try to combine them in one report you get problem. This is because you can only set one date criteria per report.
You can find the records with a search and refine search to remove the expired ones. But as far as I know, you can’t put that result into a report.
The alternative to GnuCash reports
The solution to this example is to save the GnuCash file as a SQL database, here I use MySQL. To export to MySQL you first have to install and setup MySQL on your computer. You find instructions on how to do this on the MySQL site. You find a link at the end of the page.
When this is done and after a little study of SQL you soon can produce reports faster than you can say Ulan Bator.
I have tried this in SQLite as well, but SQLite don’t support the timestamp format used in GnuCash and MySQL. It gets very awkward to do calculations on date and time in SQLite.
The GnuCash database
To find the records for the membership list you need to know in what tables and what columns to look. For this example we use the tables transactions, splits and accounts.
From the transactions table we use guid, post_date and description columns. guid is the primary key in the transactions table.
From the accounts table we use the code column.
From the splits table we use tx_guid and account_guid.
tx_guid is the foreign key into the transactions table.
account_guid is the foreign key into the accounts table.
Not used in this example is the slots table. Here you can find a lot of different unrelated data. If you use the double row feature in GnuCash, that note end in this table. File references and action also end
in this table. I have not investigated how things are related in the table, but we don’t need it for this example.
There are a lot more tables than these in the database, but for a solution to this example we only need three. You find a link to the GnuCash database schema at the end of the page.
One peculiarity when exporting to a SQL database is that the dates in the post_date in the transactions table all are one day off. In GnuCash they all show the correct date, but in the MySQL database they are one day less. This is also the case when you export to a SQLite database.
The SQL list of members code
You find the code to the solution below. It is commented throughout, but if something is unclear you can leave a comment and I try to help you out the best I can.
The formatting of the code can be a bit askew with the automatic line break.
You can find the original sql text file here: Member list.txt
GnuCash and SQL resources and books
If you want to find more information about GnuCash, SQL or MySQL, here are some resources for you.
Stanford online – A video Introduction to SQL
MySQL – A wealth of information on MySQL
MySQL Workbench (recommended tool)
Database System Concepts (6th edition). Over 1000 pages on database concepts. It has a large section on SQL and query writing. This is one of the book recommended by Stanford (the link above).
MySQL (5th Edition). Everything you need to know about MySQL. Another brick on over 1000 pages.
Gnucash 2.4 Small Business Accounting. From beginner to advanced user.
If you have any suggestions or questions on the subject, please leave a comment.