How to make a GnuCash custom report using MySQL

If GnuCash got one downside it is the report generation. But you can do your own custom reports using SQL and MySQL.

GnuCash account screen

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.

GnuCash SQL tables

Four of the GnuCash tables

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.

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 expire
using 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 are
placed without
    # the if() and the ascii().

ORDER BY if(ascii(mid(Name,1,1)) in (ascii('Å'),ascii('Ä'),ascii('Ö')),1,0), Name;

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)

GnuCash

GnuCash Database schema

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.

 

Comments

How to make a GnuCash custom report using MySQL — 10 Comments

  1. Anders,

    You have really provided a clear explanation of what GnuCash is all about and offering your insights and alternatives as well. I have truly learned so much by reading about it.

    Thank you for providing this invaluable information for the rest of us.

    Best,
    Jason.

    • Hello Jason

      I am glad that you found the article useful. One article like this can only scratch the surface of what GnuCash and SQL is, but I hope this solution to my report problem can be of help to someone else as well.

  2. Hi Anders, very interesting.

    I am not much of a coder but I do enjoy technical stuff. I understand tables, primary keys, etc. I would always have thought mysql and so on a bit too advanced for me but from reading this, it does seem like it is possible for me to learn on my own with a bit of effort.

    SQL is a very powerful language and it’s a great skill to have to be able to create your own working databases.

    Thanks for the links to the resources too.

    Colm.

    • Hello Colm
      I’m glad you liked the article. I used Microsoft Access for many years and only used the graphical query interface. But after switching to Linux I had to stop being lazy and learn SQL properly ;-). It takes a bit of effort, but it is well worth it.

  3. I cannot believe that more GNUCash users do not use a database back end for more robust reporting. Personally I like a more Graphical approach so I connect to the MySQL (MariaDB) back end with LibreOffice and the JDBC connector. Then I use the query and reporting tools there to create Print ready reports, invoices, deposit slips and so on. It works well because I use a read-only user. This allows others (mainly my wife) to look at the data and print out whatever is needed without the possibility of messing up the data.

    • If this was included into GnuCash it would be a complete package. It’s a bit frustrating that you cannot do it all from within GnuCash, but if you know how you can do it from the “outside.” Even if GnuCash has the option to make custom reports through Scheme, I don’t think it’s as flexible as with SQL. If I have understood things correct, you only build the report preference form with Scheme. Although Lisp and Scheme are very powerful languages to learn.

  4. Thank you for the post!! It reminded me to think outside the box, outside the Gnucash GUI. I thought the database was too complex for me to do SQL queries directly, but now I realised I actually only need 3 tables. Following your tips I successfully exported the transactions in CSV this way, the way I wanted. All in one file.
    The export transactions to CSV function in Gnucash only exports one type of account, either income/expense/etc but not all into one csv file.
    I used the sqlite3 version and with the date being 1 day off, it’s because the when we compare dates without the time, it is comparing the first 8 characters in the date string 20170214105500, and for example with t.post_date <= '20170214', it is looking only up to '20170213235959' which is the previous day.
    With the (s.value_num / s.value_denom), I had to cast it to REAL before dividing, because otherwise it's all rounded into integer. Therefore (s.value_num*1.0 / s.value_denom) works more accurately.

    • As you say, the database looks complex when you look at the chart, but you only need 3 tables for normal use. Thanks for solving the date problem. The Gnucash support said it probably was a bug in the export dll file. It is the same problem in MySQL as well.

      The data reports in GnuCash is almost useless as you can’t change it in any easy way.

  5. Thanks for sharing your work. This is where I found the first reference to the problems of the dates. For the moment, I’ve just changed any date from the first of the month to the second of the month to force my monthly amounts to be the same as in gnucash! I’m definitely an amateur!

    Just for your information, I’m using postgreSQL which has the same problem with the dates, but it enables me to use the python module psycopg2 and automate the input of dates and output of the csv file.

    • Hello John

      I’m glad that you found some use for my post. I reported the bug to GnuCash, and the other week I got a mail that the bug was fixed for the next version. But I have partly giving up on GnuCash. I use Ledger in Ubuntu for the stuff I need detailed reports on. There you have full control of what you get in the report, but not the ease of input you get in GnuCash. Reports in GnuCash is a nightmare, a pity for a otherwise competent accounting program.

Leave a Reply

Your email address will not be published. Required fields are marked *