How to make a GnuCash curtom reort using MySQL
The downside of GnuCash
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 follows. 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
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.
The compiled list
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 under 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_guidis the foreign key into the transactions table.
account_guidis the foreign key into the accounts table.
The slots 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 up in this table. File references and action also end up 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.
SELECT # Correct the -1 day off in the t.post_date with # date_add(date,interval expr unit) # Format to show only the date with date_format(date,format). date_format(date_add(t.post_date, interval + 1 day), '%Y-%m-%d') as 'From Date', # The members are booked as Fname Lname in t.description # To show the last name first in the result # we have to reverse them. # concat(Lname,' ',Fname) # LName: mid(t.description, instr(t.description,' ')+1) # Start from ' ' + 1 to the end # FName: mid(t.description, 1, # instr(t.description,' ')-1) # Start from beginning up to ' ' - 1 concat( mid(t.description, instr(t.description,' ')+1),' ', mid(t.description, 1, instr(t.description,' ')-1) ) as Name, # Amount: The fee the member paid, the amount. # Locale formatting using subtags. # Language subtag registry # Here I'm using no decimals and Swedish formatting and # I use contat() to add ' kr' to the end of the sum. # The amount is stored without decimals, so you have to # divide value_num with value_denom to get the correct amount. # Since this is an income account the value is negative, # so I reverse it with abs() CONCAT(FORMAT(abs(s.value_num / s.value_denom),0,'se_SE'), ' kr') as Amount, # Type: The type of membership fee. # One year (1/1), # six months (1/2) or # one month (m) # if(expr=true, do this, else do this) # Account Codes: # 3000 = Membership fees # (unspecified, account no longer used) # 3001 = One year # 3002 = 6 months # 3003 = one month # Flag for error if not one of the last three accounts if(a.code='3001','1/1',if(a.code='3002','1/2', if(a.code='3003','m','Error'))) as Type, # @to_date: Variable to hold the date # when the membership expires. # @to_date is also used to add a one month reminder # (see below) # I use date_add() to calculate the expiration date. # 750 is the one year fee for adults, 18 and above. # 450 is the one year fee for youths, 0 to 17 years. # 375 is the 6 month fee for adults, 18 and above. # 225 is the 6 month fee for youths, 0 to 17 years. # 115 is the monthly fee. # If the amount is not as above, set the date to NULL # Some members pay the wrong fee so you have to calculate # the NULL dates by hand # This construct also catch fees with the wrong account code. # We don't have to correct the -1 day error here. # Now the membership expires one day prior the # t.post_date + interval @to_date:=if(a.code='3001', date_format(date_add(t.post_date, interval + if(abs(s.value_num / s.value_denom) = 750 || abs(s.value_num / s.value_denom) = 450, 1, NULL) year), '%Y-%m-%d'), if(a.code = '3002', date_format(date_add(t.post_date, interval + if(abs(s.value_num / s.value_denom) = 375 || abs(s.value_num / s.value_denom)= 225, 6, NULL) month), '%Y-%m-%d'), if(a.code = 'm' && abs(s.value_num / s.value_denom) = 115, date_format(date_add(t.post_date, interval + 31 day),'%Y-%m-%d'), null))) as 'To Date', # '*' = The reminder column # Add a one month (31 days) reminder when the membership # is about to expireusing datediff(date1,date2) if(DATEDIFF(@to_date, curdate()) <= 31, '*', '') as '*' FROM # I'm using inner join to get the correct records. # t.guid is the primary key in transactions. # One transactions have two or more splits records. # s.tx_guid is the foreign key for the t.guid # in the splits table. # a.guid is the primary key in accounts. # s.account_guid is the foreign key for a.guid in # the splits table. # I use t, s and a as table variables for a # more compact code. transactions as t INNER JOIN splits as s inner join accounts as a ON t.guid = s.tx_guid && s.account_guid = a.guid WHERE # Here we set up the conditions for t.post_date and a.code # I'm using the current date with curdate() # For account '3001' find records up to one year old. # For account '3002' find records up to 6 month old. # For account '3003' find records up to one moth old. (((t.post_date >= date_add(curdate(), interval - 1 year)) AND (a.code = '3001')) or ((t.post_date >= date_add(curdate(), interval - 6 month)) AND (a.code = '3002')) or ((t.post_date >= date_add(curdate(), interval - 1 month)) AND (a.code= '3003'))) # I choose to sort by description (Name) as it is easier # to skim the list for a name that way. # I use the variable Name for the sorting (LName FName) # The if() and ascii() is used for the Swedish # characters Å, Ä and Ö. # They belong at the end of the list, not at # the beginnig where they areplaced without # the if() and the ascii(). ORDER BY if(ascii(mid(Name,1,1)) in (ascii('Å'),ascii('Ä'),ascii('Ö')),1,0), Name;`
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 - A 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.
If you have any suggestions or questions on the subject, please leave a comment.comments powered by Disqus