IReport on a Mac
From MTHWiki
Line 77: | Line 77: | ||
- | To setup new Datasource please click on " | + | Please '''restart''' Studio at this point |
+ | |||
+ | |||
+ | |||
+ | |||
+ | To setup new Datasource please click on "Repository Explorer", right click on Data Adapters and add a new Data Adapter | ||
Line 86: | Line 91: | ||
Name: MoneyDB | Name: MoneyDB | ||
- | JDBC Driver: org.apache.derby.jdbc.EmbeddedDriver | + | JDBC Driver: Derby DB (Embedded) - org.apache.derby.jdbc.EmbeddedDriver |
JDBC URL: this should be location to your database, for example jdbc:derby:/Users/username/Desktop/Test/Test/moneyDB | JDBC URL: this should be location to your database, for example jdbc:derby:/Users/username/Desktop/Test/Test/moneyDB | ||
Line 94: | Line 99: | ||
- | [[Image: | + | [[Image:Studio_MyMoney_Adaptor.png|thumb|center|600px]] |
Press Test, leave User Name and Password blank, your connection should be successful. | Press Test, leave User Name and Password blank, your connection should be successful. | ||
- | If you can't connect to the database please see this list of common | + | If you can't connect to the database please see this list of common problems and resolutions |
[[How_to_create_a_custom_Tax_report_in_My_Money#Some_common_problems_that_you_may_encounter_while_connecting_to_database_from_iReport]] | [[How_to_create_a_custom_Tax_report_in_My_Money#Some_common_problems_that_you_may_encounter_while_connecting_to_database_from_iReport]] |
Revision as of 02:51, 27 January 2015
Contents |
Introduction
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 are few 3rd party tools we prefer JasperSoft Studio, it is free, relatively fast and seems to be bug free.
JasperSoft Studio on Mac
Prerequisites
- Java 8 JDK For Mac: Please download and install Java 8 JDK for Mac, for example Java 8.0.31 is available from Oracle direcly.
- JasperSoft Studio: Please download and install JasperSoft Studio for Mac, you can get it from JasperForge, please get the version 6.0 or later
Before starting to use JasperSoft Studio please create a folder on your system where you will be keeping your custom reports, for the purposes of this article lets call it CustomReports .
Please locate MyMoney on your system (it will most likely reside in Applications folder) and right click on it to see "Show Package Contents" menu. Please navigate inside of the package and copy a system report that you would like to edit, for example to edit report that categorizes transactions please locate categorized_p100.jrxml under Contents/Resources/system/reports/built-in
Please download styles.jrtx and place it in the same folder
You also need to point Studio to the required MyMoneys class files - the easiest way of doing so is to create a link - please open a console and type the following commands:
1) cd /Users/xxx/CustomReports/ 2) ln -s /Applications/MyMoney.app/Contents/Java/ ./Java
Please change xxx above to the location of your CustomReports folder
At this point your CustomReports folder should look like the following:
Starting JasperSoft Studio
Before starting JasperSoft Studio you will need to point it to the Java 8 installation, please do the following:
Please locate Studio in your applications folder and right click on the Jaspersoft Studio.app then select "Show Package Content", then under Contents/MacOS/ select JasperSoft Studio.ini
Please open JasperSoft Studio.ini in a text editor
Please locate -vm setting and change it so it points to your Java 8 installation root
for example, if Java 8 was installed in /Library/Java/JavaVirtualMachines/jdk1.8.0_25.jdk the file should look like
@noDefault --launcher.defaultAction openFile -vm /Library/Java/JavaVirtualMachines/jdk1.8.0_25.jdk/Contents/Home/bin -vmargs -Xms128m -Xmx1024m
Please start the Studio, it will ask you to create a default workspace, you can accept a default location or point it to a new location
Once studio is up please open menu JasperSoft Studio - Preferences then Java then Build Path then User Variables
Click New to create a new Library, please call it MyMoney
Once library is created please click on Add External Jars and navigate to linked /Users/xxx/CustomReports/Java folder, please select everything inside this folder (you can press Control-A or Command-A) and add everything to the classpath
You classpath screen should look like the following:
Please restart Studio at this point
To setup new Datasource please click on "Repository Explorer", right click on Data Adapters and add a new Data Adapter
When prompted please make the following choices:
DataSource Type: Database JDBC Connection
Name: MoneyDB
JDBC Driver: Derby DB (Embedded) - org.apache.derby.jdbc.EmbeddedDriver
JDBC URL: this should be location to your database, for example jdbc:derby:/Users/username/Desktop/Test/Test/moneyDB
Your Datasource should look like the following:
Press Test, leave User Name and Password blank, your connection should be successful.
If you can't connect to the database please see this list of common problems and resolutions
Once the datasource is setup you can open categorized_p100.jrxml through File-Open menu in iReport, your screen should look like the following:
Changing reports
Lets try to change the report somewhat, for the purposes of this article lets assume that we want to:
- Sort Transactions by their check number first and,
- Categorize Transactions by their check number, i.e. transactions sharing the same check number should be grouped together
Resorting Report
To apply different sorting you will need to change the default SQL query a bit, please open SQL Query Editor in iReport, ckecl in Report Query, and locate "Order By Section".
Please add new sorting order by adding MMY_SPLIT.CHECKNUM to the list of the sorting conditions. This tells the SQL Processor to order the results first by the field CHECKNUM which contains check numbers, then by other conditions, such as categories, transaction dates, etc.
You can always test the editing by running reports in place -- if you switch into the "Preview" mode and your database connection is defined to open your Money file you will see the results right away
Adding a new Report Group
To categorize and summarize report on a new criteria you will need to create a new Report group, in this case we will be creating a group where all checks sharing the same number are grouped together and their combined amount is calculated and displayed on the report.
To get started please right click somewhere on the report and select "Add New Group" menu
Please specify that the new group needs to operate on CHECKNUM field and give it a name, for example CHECK. You probably will want to have group headers and footers if you want to show relevant group information, so you can leave rest of the settings as it is.
Once the group is created you will need to manually create a new Variable to hold the results of calculations: Please expand Variables set in the Reports Inspector, create a new Variable and in the Properties specify that its type is a Group and you want to summarize the amounts. Please also specify what to summarize, in this case it would be AMOUNT, i.e. for each element in the group your new variable will be incremented by transactions AMOUNT.
Lastly, please create (or copy through Command-C/Command-V) a text box on the report and place it inside the CHECK Group Footer area.
This will be printed once per new Group. Please specify that the text in the textbox refers to your new variable ${CheckAmount}
At this point your new report should be operational, if you click on preview you should see that the Transactions with the same check number are grouped together and their combined amounts are printed underneath.
At this point the two objectives are complete, to streamline the report a bit you can remove categorization by Transaction Categories if it is not needed, change the report Headers, Footers, Fonts, etc.
Once you are done with the report please change the name so it is meaningful for you, for example categorized_check_p100.jrxml
You can copy the new report back into MyMoney system directory and you will see it next time you restart MyMoney.
Please note that if you re-install MyMoney you will lose all custom reports installed in MyMoneys system areas, so please keep a backup.
Persisting User Defined Report
MyMoney has a special location for user-defined reports. If you place your report there it will not disappear if you reinstall or upgrade MyMoney.
print(SwingMoneyView.singleInstance.environment.getUserApplicationHome());
/Users/xxx/Library/Containers/com.mth.money/Data/Library/.mymoney
Once you located this folder you need to create a report folder within it, to do so please open a Finder window and use Go-Go to folder to navigate to the user data (/Users/xxx/Library/Containers/com.mth.money/Data/Library/.mymoney)
While in user data folder please change to system, if you don't have a 'reports' folder please create it, and then please create 'user'folder so your structure looks like /Users/xxx/Library/Containers/com.mth.money/Data/Library/.mymoney/system/reports/user
Please copy your reports into this system/reports/user folder
Please start MyMoney.app, switch to Reports tab - if you did everything correctly you will see User Reports section that will contain your newly copied report. If you update MyMoney in the future your User defined report will survive