How to create a custom Tax report in My Money

From MTHWiki

Revision as of 02:01, 12 April 2008 by Wikiadmin (Talk | contribs)
Jump to: navigation, search

The point of this article is to illustrate how to create a custom Tax report. We would like to create a new report that would combine all taxable transactions by Category/Sub-Category, calculate their sums and break it down by a month. For example we should be able to run it and see how much was spent on Business Meals in January, February, March, etc., provided that Business Meals is marked as a Taxable category.


This is an advanced exercise, we strongly suggest both backing up your My Money database and My Money installation folder.

Also, you need to have basic understanding of databases, Structured Query Language (SQL) and not be afraid of installing and configuring some 3rd party database and reporting software.


Jasper Reports

My Money is using JasperReports as its Reporting engine. Jasper is open sourced, actively developed, has a large community of users and is generally generally well understood (wikipedia) piece of software code. We at MTH Software like it and trust it enough to embed it into our own software.

To actually design a report for My Money you need a front-end software for Jasper reports, there is a bunch of 3rd party tools we prefer iReport, it is free, relatively fast and seems to be bug free.

Image:IReport.png


Once you have iReport installed, you will need to connect it to My Money database. As explained in How to connect to My Money Database, My Money is using Derby DBMS, so you will need to add Derby drivers to iReport for connection to the database.

You need to configure iReport classpath, you can use Option/Classpath/Add Jar to add derby jars, for example, on Windows, derby jars are kept in C:\Program Files\MTH\My Money\lib directory, you need to add 2 jars: derby.jar and derbytools.jar On a Mac these jar files are kept inside of a MyMoney.app bundle and on Linux there are inside of the default installation directory MyMoney-2.0/lib

Once the classpath is set-up you need to set-up connection to the My Money database, to do se you need to set up a new JDBC connection. Please select "New JDBC Connection" from the list of available data sources, your window at this point should look like the following:

Image:Ireportjdbc.png

The important parts here:

JDBC Driver: org.apache.derby.jdbc.EmbeddedDriver

JDBC URL: this should be location to your database, for example jdbc:derby:C:\FinancialData\testDb\moneyDB;create=false


If your database is protected by a password you obviously need to specify user name and a password in the options.


Creating New Report

The easiest way to create a new report is to simply copy an existing report under a new name. All My Money reports are kept in system/reports/built-in directory. For example on windows this would be in C:\Program Files\MTH\My Money\system\reports\built-in. There are generally 2 kinds of files there, files with extensions .jrxml are open-sourced report definitions and files with extension .jasper are precompiled binary files that are actually run by My Money when user selects a report. You will notice that reports have uniform names, they all end with _pxxx, for example there is a report all_vattaxes_p100.jrxml. These _pxxx names are important, more on them later.

We are going to create a new folder called categorized inside of the taxes subfolder, and copy all_taxes_p100.jrxml into it. We are going to rename this new file into all_taxes_categorized_p100.jrxml so it is a bit different from the old one.

At this point your directory structure should look like the following:

Image:Categorizedtaxes.png


Once the file is created, please go back to the iReport and open it through File-Open

You should see something like this

Image:Categorizedtaxesdesign.png

At this point you should try to run this report just to see whether the connection is working and you are bringing in the data. Select Build/Execute with Active connection, this should compile the report and run it. If the report comes out with no data then most likely you don't have any taxable transactions, you will need to go back to My Money and set up some categories as Business or personal taxable. Because when database is open it is automatically locked into into an exclusive mode you will need to close iReport first, then open My Money, edit the categories, close My money and then re-open the iReport.

Personal tools