How to create a custom Tax report in My Money

From MTHWiki

(Difference between revisions)
Jump to: navigation, search
(New page: 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, cal...)
Current revision (04:52, 22 January 2011) (edit) (undo)
 
(24 intermediate revisions not shown.)
Line 1: Line 1:
 +
== Introduction ==
 +
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
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.
combine all taxable transactions by Category/Sub-Category, calculate their sums and break it down by a month.
Line 10: Line 12:
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.
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 [http://jasperreports.sourceforge.net/ JasperReports] as its Reporting engine. Jasper is open sourced, actively developed, has a large community of users and is generally [http://en.wikipedia.org/wiki/JasperReports 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.
My Money is using [http://jasperreports.sourceforge.net/ JasperReports] as its Reporting engine. Jasper is open sourced, actively developed, has a large community of users and is generally [http://en.wikipedia.org/wiki/JasperReports 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 [http://en.wikipedia.org/wiki/JasperReports#Third_party_tools bunch of 3rd party tools] we prefer [http://jasperforge.org/jaspersoft/opensource/business_intelligence/ireport/ iReport], it is free, relatively fast and seems to be bug free.
To actually design a report for My Money you need a front-end software for Jasper reports, there is a [http://en.wikipedia.org/wiki/JasperReports#Third_party_tools bunch of 3rd party tools] we prefer [http://jasperforge.org/jaspersoft/opensource/business_intelligence/ireport/ iReport], it is free, relatively fast and seems to be bug free.
 +
[[Image:IReport.png]]
[[Image:IReport.png]]
-
Once you have iReport installed, you will need to connect it to My Money database
+
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'''
 +
 
 +
 
 +
 
 +
=== Some common problems that you may encounter while connecting to database from iReport ===
 +
 
 +
 
 +
*'''Driver is not found:''' Please note that iReport may not have the correct JDBC driver in the drop down list of available drivers. If that is the case simply type org.apache.derby.jdbc.EmbeddedDriver as the driver name, as long as the classpath is set properly the driver will be found.
 +
 
 +
 
 +
*'''Unable to start the database:''' If your database is protected by a password you will need to remove it before attempting to connect from an external application. My Money uses much stronger encryption that is provided by the database engine and you will need to disable it temporarily while you are designing your reports. To remove the password please start up My Money, open Preferences-Privacy and click on "Remove all Users" button.
 +
 
 +
 
 +
*'''Database is locked:''' My Money opens database in an exclusive mode, you will not be able to access the same database from My Money and other programs at the same time. Please shut down My Money first, then open the database in iReport, otherwise you will get an error that database is locked. Similarly, shut down iReport before reopening the database in My Money.
 +
 
 +
 
 +
*'''Unable to Boot/Classpath resolution exception''' This is most likely caused by permission problems. Please make sure that iReport is not installed with administrative privileges, as it tries to load a custom derby.jar with lesser permissions and that causes security exception. Please tweak permissions on iReport, for example on Windows systems please install it outside of system protected folders, i.e. instead of Program Files please install it into User Directory\Programs. On a Mac please change permissions so it is globally readable and writable.
 +
 
 +
 
 +
=== 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.
 +
 
 +
 
 +
 
 +
== Report parameters and conventions ==
 +
 
 +
=== 9 Report parameters ===
 +
 
 +
Every report in My Money automatically receives 9 predefined parameters:
 +
 
 +
* reportPayees
 +
* reportAccounts
 +
* reportCategories
 +
* reportSubCategories
 +
* reportHeader
 +
* reportFooter
 +
* reportDateFormat
 +
* reportDateFrom
 +
* reportDateTo
 +
 
 +
Those are the options that user has selected before running any given report, they all are strings, and all are accessible through $P{xxx} notation, for example $P{reportDateFrom} will give you the date the report should run from.
 +
 
 +
For example, user may say "I want to to run a report on my Checking account, with all Income Categories and Sub-Categories,
 +
with some payees, between last month and today, with a custom Date format".
 +
 
 +
These choices are captured in report parameters. reportPayees, Accounts, Categories, SubCategories actually will contain
 +
selected record ids, reportDateFormat will contain a SimpleDatePattern compatible string and Footer and Header may contain
 +
a arbitrary string. reportDateFrom/To are guaranteed to contain strings valid for SQL Date comparisons.
 +
 
 +
If you open SQL Query builder in iReport you will see how these parameters are used to build a dynamic SQL
 +
 
 +
<code><pre>
 +
SELECT DISTINCT
 +
MMY_TRANSACTION."TDATE" AS TDATE,
 +
MMY_PAYEE."PNAME" AS PNAME,
 +
MMY_SPLIT."CHECKNUM" AS CHECKNUM,
 +
MMY_SPLIT."AMOUNT" AS AMOUNT,
 +
MMY_SPLIT."MEMO" AS MEMO,
 +
MMY_ACCOUNT."ANAME" AS ANAME
 +
FROM
 +
"MTHADMIN"."MMY_ACCOUNT" MMY_ACCOUNT,
 +
"MTHADMIN"."MMY_DOUBLEENTRY" MMY_DOUBLEENTRY,
 +
"MTHADMIN"."MMY_TRANSACTION" MMY_TRANSACTION,
 +
"MTHADMIN"."MMY_SPLIT" MMY_SPLIT,
 +
"MTHADMIN"."MMY_PAYEE" MMY_PAYEE,
 +
"MTHADMIN"."MMY_CATEGORY" MMY_CATEGORY
 +
WHERE
 +
MMY_TRANSACTION.tstate != 5
 +
AND MMY_ACCOUNT.ID = MMY_DOUBLEENTRY.LEFTACCNTID
 +
AND MMY_DOUBLEENTRY.LEFTTRANID = MMY_TRANSACTION.ID
 +
AND MMY_TRANSACTION.ID = MMY_SPLIT.TRANID
 +
AND MMY_SPLIT.PAYEEID = MMY_PAYEE.ID
 +
AND MMY_TRANSACTION.TDATE >= $P{reportDateFrom}
 +
AND MMY_TRANSACTION.TDATE <= $P{reportDateTo}
 +
AND ( MMY_SPLIT.CATEGORYID=MMY_CATEGORY.ID
 +
OR
 +
MMY_SPLIT.SUBCATEGORYID=MMY_CATEGORY.ID)
 +
AND
 +
( MMY_CATEGORY.TAXP='Y' OR
 +
MMY_CATEGORY.TAXB='Y' OR
 +
MMY_CATEGORY.TAXVAT='Y')
 +
ORDER BY
 +
MMY_TRANSACTION.TDATE ASC
 +
</pre></code>
 +
 
 +
What the SQL above is doing is selecting Transaction Date, Payee Name, Check Number, Amount, Memo and Account to which transaction was posted. It is a straightforward query that selects this data based on 3 criteria, the transaction should be posted between the dates specified in reportDateFrom and reportDateTo and category of said transaction should be marked as Taxable.
 +
 
 +
In effect all Taxable categories between 2 user selected dates will be retrieved, sorted by date, ascending (meaning earlier dates first) and printed in a report.
 +
 
 +
 
 +
The reportPayees,reportAccounts, reportCategories and reportSubCategories are designed to be used in '''IN''' clauses, for example to filter by user selected account you can use '''AND MMY_ACCOUNT.ID IN ($P!{reportAccounts})'''
 +
 
 +
=== Significance of Report Naming ===
 +
 
 +
My Money recognizes some predefined names in Reports. This is done to simplify report creation
 +
 
 +
<code><pre>
 +
 
 +
// We need those to navigate through different report types, they
 +
// normally have extensions reflecting their date types
 +
//
 +
public static final String ALL_EXT = "_p100";
 +
public static final String CRNT_WEEK_EXT = "_p101";
 +
public static final String CRNT_MONTH_EXT = "_p102";
 +
public static final String CRNT_QUARTER_EXT = "_p103";
 +
public static final String CRNT_YEAR_EXT = "_p104";
 +
public static final String PREV_WEEK_EXT = "_p105";
 +
public static final String PREV_MONTH_EXT = "_p106";
 +
public static final String PREV_QUARTER_EXT = "_p107";
 +
public static final String PREV_YEAR_EXT = "_p108";
 +
public static final String LAST_30DAY_EXT = "_p109";
 +
public static final String LAST_60DAY_EXT = "_p110";
 +
public static final String LAST_3MTH_EXT = "_p111";
 +
public static final String LAST_12MTH_EXT = "_p112";
 +
public static final String LAST_24MTH_EXT = "_p114";
 +
public static final String CUSTOM_EXT = "_p200";
 +
 
 +
</pre></code>
 +
 
 +
Ok, so what it means ? It means that if you name your report myreport'''_p105''' it will automatically receive date range on previous week.Similarly, if you name your report my_report'''_p112''' it will automatically receive date ranges of the last 12 month.
 +
'''_p200''' is a bit different, if you name your report with _p200 you are guaranteed that My Money will popup customization
 +
dialog asking the user to customize all parameters, it is in effect forcing interactive behavior on a report.
 +
 
 +
Having said that, while all these dates and variables are guaranteed to be passed in, it is however, up to the report author to use some of them, all of them or completely ignore them.
 +
 
 +
===Customizing New Report===
 +
 
 +
So at this point we have old report renamed, we understand how the parameters are passed so we can go ahead and customize the tax report.
 +
 
 +
First off, what are we customizing ? Well, the old report combines all taxable transactions by account. This is fine, but sometime it is convenient to have everything grouped by Category, so we can say select all Automotive(Business) transactions without having to go
 +
and comb through accounts. We also would like to keep a tab on the totals, so we can figure out that total amount spent on Automotive(Business) is xxx within specified time range.
 +
 
 +
This is a quite simple change. We need to replace the old SQL query with a new one, because we aren't interested in Account names anymore we can discard them and instead we want to bring in Category names.
 +
 
 +
The new SQL Query will look like this:
 +
 
 +
<code><pre>
 +
SELECT DISTINCT
 +
MMY_PAYEE."PNAME" AS PNAME,
 +
MMY_SPLIT."CHECKNUM" AS CHECKNUM,
 +
MMY_SPLIT."AMOUNT" AS AMOUNT,
 +
MMY_SPLIT."MEMO" AS MEMO,
 +
MMY_TRANSACTION.TDATE,
 +
MMY_CATEGORY."TRANCAT" AS CATTYPE,
 +
MMY_CATEGORY."CATNAME" AS CATNAME
 +
FROM
 +
"MTHADMIN"."MMY_ACCOUNT" MMY_ACCOUNT,
 +
"MTHADMIN"."MMY_DOUBLEENTRY" MMY_DOUBLEENTRY,
 +
"MTHADMIN"."MMY_TRANSACTION" MMY_TRANSACTION,
 +
"MTHADMIN"."MMY_SPLIT" MMY_SPLIT,
 +
"MTHADMIN"."MMY_PAYEE" MMY_PAYEE,
 +
"MTHADMIN"."MMY_CATEGORY" MMY_CATEGORY
 +
WHERE
 +
MMY_TRANSACTION.tstate!= 5
 +
AND MMY_ACCOUNT.ID = MMY_DOUBLEENTRY.LEFTACCNTID
 +
AND MMY_DOUBLEENTRY.LEFTTRANID = MMY_TRANSACTION.ID
 +
AND MMY_TRANSACTION.ID = MMY_SPLIT.TRANID
 +
AND MMY_SPLIT.PAYEEID = MMY_PAYEE.ID
 +
AND MMY_TRANSACTION.TDATE >= $P{reportDateFrom}
 +
AND MMY_TRANSACTION.TDATE <= $P{reportDateTo}
 +
AND (MMY_SPLIT.CATEGORYID = MMY_CATEGORY.ID
 +
OR MMY_SPLIT.SUBCATEGORYID = MMY_CATEGORY.ID)
 +
AND (MMY_CATEGORY.TAXP = 'Y'
 +
OR MMY_CATEGORY.TAXB = 'Y'
 +
OR MMY_CATEGORY.TAXVAT = 'Y')
 +
ORDER BY
 +
MMY_CATEGORY.TRANCAT DESC,
 +
MMY_CATEGORY.CATNAME ASC,
 +
MMY_TRANSACTION.TDATE ASC
 +
</pre></code>
 +
 
 +
Notice that we are also bringing Transaction Category Type into the query (TRANCAT), normally this field is set to 0 when category is of a "Income" type, 1 when it is an "Expense" and 2 when it is of a Special type, for example Transfer.
 +
 
 +
 
 +
At this point we can modify report definition in iReport. Jasper Reporting is designed around notion of bands and groups. Without going into too much detail of how to operate a band in a JasperReport we basically need to replace Account Band with a Category band and add a new field that would calculate the running totals for the Category.
 +
 
 +
This is how the report definition looks after the changes,
 +
 
 +
[[Image:Categorizedtaxfinal.png]]
 +
 
 +
There are quite a few tutorials available on the net on how to work with Jasper Reports, for example [http://www.cise.ufl.edu/~otopsaka/CIS4301/ReportDemo/PrepareAReport.html Sample step-by-step tutorial] on setting up customer list by Department of Computer Science of University of Florida. JasperSoft runs their own [http://www.jasperforge.org/jaspersoft/opensource/business_intelligence/jasperreports/samples.html samples and tutorial website], it is very thorough and detailed but could be a bit advanced and overwhelming for a casual user.
 +
 
 +
 
 +
Obviously you can customize report more at this point, it is very easy to add custom graphics, logo, barcodes etc. However, for our purposes we are almost done.
 +
 
 +
We need to save this report as a _p100 file, _p108 to get us automatic previous year and _p200 so the users can customize it more.
 +
 
 +
So we should have 3 reports
 +
* all_taxes_categorized_p100.jrxml - for ALL data ranges, from the earliest transaction to the latest
 +
* all_taxes_categorized_p108.jrxml - for previous year data ranges
 +
* all_taxes_categorized_p200.jrxml - user will be given a dialog to select Accounts, Categories, etc
 +
 
 +
Please note that we will need to further edit _p200 SQL so it can take full advantage of the passed in parameters, but _p100 and _p108
 +
are identical to each other, the only thing different is the name.
 +
 
 +
 
 +
Once the reports are saved you need to restart My Money, select Reports Tab and run "Rebuild All Reports". This will compile .jrxml files and produce .jasper files that could be run from within My Money.
 +
 
 +
We are going to distribute this categorized tax report with My Money, so you can customize it even further if needed.

Current revision

Contents

Introduction

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


Some common problems that you may encounter while connecting to database from iReport

  • Driver is not found: Please note that iReport may not have the correct JDBC driver in the drop down list of available drivers. If that is the case simply type org.apache.derby.jdbc.EmbeddedDriver as the driver name, as long as the classpath is set properly the driver will be found.


  • Unable to start the database: If your database is protected by a password you will need to remove it before attempting to connect from an external application. My Money uses much stronger encryption that is provided by the database engine and you will need to disable it temporarily while you are designing your reports. To remove the password please start up My Money, open Preferences-Privacy and click on "Remove all Users" button.


  • Database is locked: My Money opens database in an exclusive mode, you will not be able to access the same database from My Money and other programs at the same time. Please shut down My Money first, then open the database in iReport, otherwise you will get an error that database is locked. Similarly, shut down iReport before reopening the database in My Money.


  • Unable to Boot/Classpath resolution exception This is most likely caused by permission problems. Please make sure that iReport is not installed with administrative privileges, as it tries to load a custom derby.jar with lesser permissions and that causes security exception. Please tweak permissions on iReport, for example on Windows systems please install it outside of system protected folders, i.e. instead of Program Files please install it into User Directory\Programs. On a Mac please change permissions so it is globally readable and writable.


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.


Report parameters and conventions

9 Report parameters

Every report in My Money automatically receives 9 predefined parameters:

  • reportPayees
  • reportAccounts
  • reportCategories
  • reportSubCategories
  • reportHeader
  • reportFooter
  • reportDateFormat
  • reportDateFrom
  • reportDateTo

Those are the options that user has selected before running any given report, they all are strings, and all are accessible through $P{xxx} notation, for example $P{reportDateFrom} will give you the date the report should run from.

For example, user may say "I want to to run a report on my Checking account, with all Income Categories and Sub-Categories, with some payees, between last month and today, with a custom Date format".

These choices are captured in report parameters. reportPayees, Accounts, Categories, SubCategories actually will contain selected record ids, reportDateFormat will contain a SimpleDatePattern compatible string and Footer and Header may contain a arbitrary string. reportDateFrom/To are guaranteed to contain strings valid for SQL Date comparisons.

If you open SQL Query builder in iReport you will see how these parameters are used to build a dynamic SQL

SELECT DISTINCT
     MMY_TRANSACTION."TDATE" AS TDATE,
     MMY_PAYEE."PNAME" AS PNAME,
     MMY_SPLIT."CHECKNUM" AS CHECKNUM,
     MMY_SPLIT."AMOUNT" AS AMOUNT,
     MMY_SPLIT."MEMO" AS MEMO,
     MMY_ACCOUNT."ANAME" AS ANAME
FROM
     "MTHADMIN"."MMY_ACCOUNT" MMY_ACCOUNT,
     "MTHADMIN"."MMY_DOUBLEENTRY" MMY_DOUBLEENTRY,
     "MTHADMIN"."MMY_TRANSACTION" MMY_TRANSACTION,
     "MTHADMIN"."MMY_SPLIT" MMY_SPLIT,
     "MTHADMIN"."MMY_PAYEE" MMY_PAYEE,
     "MTHADMIN"."MMY_CATEGORY" MMY_CATEGORY     
WHERE
     MMY_TRANSACTION.tstate != 5
 AND MMY_ACCOUNT.ID = MMY_DOUBLEENTRY.LEFTACCNTID
 AND MMY_DOUBLEENTRY.LEFTTRANID = MMY_TRANSACTION.ID
 AND MMY_TRANSACTION.ID = MMY_SPLIT.TRANID
 AND MMY_SPLIT.PAYEEID = MMY_PAYEE.ID
 AND MMY_TRANSACTION.TDATE >= $P{reportDateFrom}
 AND MMY_TRANSACTION.TDATE <= $P{reportDateTo}
 AND ( MMY_SPLIT.CATEGORYID=MMY_CATEGORY.ID 
       OR 
       MMY_SPLIT.SUBCATEGORYID=MMY_CATEGORY.ID)
 AND 
    ( MMY_CATEGORY.TAXP='Y' OR
      MMY_CATEGORY.TAXB='Y' OR 
      MMY_CATEGORY.TAXVAT='Y')     
ORDER BY
     MMY_TRANSACTION.TDATE ASC

What the SQL above is doing is selecting Transaction Date, Payee Name, Check Number, Amount, Memo and Account to which transaction was posted. It is a straightforward query that selects this data based on 3 criteria, the transaction should be posted between the dates specified in reportDateFrom and reportDateTo and category of said transaction should be marked as Taxable.

In effect all Taxable categories between 2 user selected dates will be retrieved, sorted by date, ascending (meaning earlier dates first) and printed in a report.


The reportPayees,reportAccounts, reportCategories and reportSubCategories are designed to be used in IN clauses, for example to filter by user selected account you can use AND MMY_ACCOUNT.ID IN ($P!{reportAccounts})

Significance of Report Naming

My Money recognizes some predefined names in Reports. This is done to simplify report creation


    // We need those to navigate through different report types, they
    // normally have extensions reflecting their date types
    //
    public static final String ALL_EXT          = "_p100";
    public static final String CRNT_WEEK_EXT    = "_p101";
    public static final String CRNT_MONTH_EXT   = "_p102";
    public static final String CRNT_QUARTER_EXT = "_p103";
    public static final String CRNT_YEAR_EXT    = "_p104";
    public static final String PREV_WEEK_EXT    = "_p105";
    public static final String PREV_MONTH_EXT   = "_p106";
    public static final String PREV_QUARTER_EXT = "_p107";
    public static final String PREV_YEAR_EXT    = "_p108";
    public static final String LAST_30DAY_EXT   = "_p109";
    public static final String LAST_60DAY_EXT   = "_p110";
    public static final String LAST_3MTH_EXT    = "_p111";
    public static final String LAST_12MTH_EXT   = "_p112";
    public static final String LAST_24MTH_EXT   = "_p114";
    public static final String CUSTOM_EXT       = "_p200";

Ok, so what it means ? It means that if you name your report myreport_p105 it will automatically receive date range on previous week.Similarly, if you name your report my_report_p112 it will automatically receive date ranges of the last 12 month. _p200 is a bit different, if you name your report with _p200 you are guaranteed that My Money will popup customization dialog asking the user to customize all parameters, it is in effect forcing interactive behavior on a report.

Having said that, while all these dates and variables are guaranteed to be passed in, it is however, up to the report author to use some of them, all of them or completely ignore them.

Customizing New Report

So at this point we have old report renamed, we understand how the parameters are passed so we can go ahead and customize the tax report.

First off, what are we customizing ? Well, the old report combines all taxable transactions by account. This is fine, but sometime it is convenient to have everything grouped by Category, so we can say select all Automotive(Business) transactions without having to go and comb through accounts. We also would like to keep a tab on the totals, so we can figure out that total amount spent on Automotive(Business) is xxx within specified time range.

This is a quite simple change. We need to replace the old SQL query with a new one, because we aren't interested in Account names anymore we can discard them and instead we want to bring in Category names.

The new SQL Query will look like this:

SELECT DISTINCT
     MMY_PAYEE."PNAME" AS PNAME,
     MMY_SPLIT."CHECKNUM" AS CHECKNUM,
     MMY_SPLIT."AMOUNT" AS AMOUNT,
     MMY_SPLIT."MEMO" AS MEMO,
     MMY_TRANSACTION.TDATE,
     MMY_CATEGORY."TRANCAT" AS CATTYPE,
     MMY_CATEGORY."CATNAME" AS CATNAME
FROM
     "MTHADMIN"."MMY_ACCOUNT" MMY_ACCOUNT,
     "MTHADMIN"."MMY_DOUBLEENTRY" MMY_DOUBLEENTRY,
     "MTHADMIN"."MMY_TRANSACTION" MMY_TRANSACTION,
     "MTHADMIN"."MMY_SPLIT" MMY_SPLIT,
     "MTHADMIN"."MMY_PAYEE" MMY_PAYEE,
     "MTHADMIN"."MMY_CATEGORY" MMY_CATEGORY
WHERE
     MMY_TRANSACTION.tstate!= 5
 AND MMY_ACCOUNT.ID = MMY_DOUBLEENTRY.LEFTACCNTID
 AND MMY_DOUBLEENTRY.LEFTTRANID = MMY_TRANSACTION.ID
 AND MMY_TRANSACTION.ID = MMY_SPLIT.TRANID
 AND MMY_SPLIT.PAYEEID = MMY_PAYEE.ID
 AND MMY_TRANSACTION.TDATE >= $P{reportDateFrom}
 AND MMY_TRANSACTION.TDATE <= $P{reportDateTo}
 AND (MMY_SPLIT.CATEGORYID = MMY_CATEGORY.ID
  OR MMY_SPLIT.SUBCATEGORYID = MMY_CATEGORY.ID)
 AND (MMY_CATEGORY.TAXP = 'Y'
  OR MMY_CATEGORY.TAXB = 'Y'
  OR MMY_CATEGORY.TAXVAT = 'Y')
ORDER BY
          MMY_CATEGORY.TRANCAT DESC,
	  MMY_CATEGORY.CATNAME ASC,
          MMY_TRANSACTION.TDATE ASC

Notice that we are also bringing Transaction Category Type into the query (TRANCAT), normally this field is set to 0 when category is of a "Income" type, 1 when it is an "Expense" and 2 when it is of a Special type, for example Transfer.


At this point we can modify report definition in iReport. Jasper Reporting is designed around notion of bands and groups. Without going into too much detail of how to operate a band in a JasperReport we basically need to replace Account Band with a Category band and add a new field that would calculate the running totals for the Category.

This is how the report definition looks after the changes,

Image:Categorizedtaxfinal.png

There are quite a few tutorials available on the net on how to work with Jasper Reports, for example Sample step-by-step tutorial on setting up customer list by Department of Computer Science of University of Florida. JasperSoft runs their own samples and tutorial website, it is very thorough and detailed but could be a bit advanced and overwhelming for a casual user.


Obviously you can customize report more at this point, it is very easy to add custom graphics, logo, barcodes etc. However, for our purposes we are almost done.

We need to save this report as a _p100 file, _p108 to get us automatic previous year and _p200 so the users can customize it more.

So we should have 3 reports

  • all_taxes_categorized_p100.jrxml - for ALL data ranges, from the earliest transaction to the latest
  • all_taxes_categorized_p108.jrxml - for previous year data ranges
  • all_taxes_categorized_p200.jrxml - user will be given a dialog to select Accounts, Categories, etc

Please note that we will need to further edit _p200 SQL so it can take full advantage of the passed in parameters, but _p100 and _p108 are identical to each other, the only thing different is the name.


Once the reports are saved you need to restart My Money, select Reports Tab and run "Rebuild All Reports". This will compile .jrxml files and produce .jasper files that could be run from within My Money.

We are going to distribute this categorized tax report with My Money, so you can customize it even further if needed.

Personal tools