Rich Internet Applications [RIA] Blog

I just proved that ClearBI (from Farata Systems), an amazing new web-based report generation tool, that delivers reports to Adobe® Flash Player, is fully compatible with MYOB – out of the box. So now, I can confidently say “ClearBI makes it easy to create and share dynamic MYOB reports and charts on the web. Users can simply log in via their favourite web browser to browse, create, modify and share any MYOB accounting data, within 15 minutes of installing ClearBI.”

The Report UI is presented in Adobe® Flash® Player, which delivers an immersing experience (its Flash®!) and superior functionality (client side RIA) and speed (via AMF binary data transfer), to any web browser. Users never have to refresh their browser while working with report creation, viewing, modification, et al, as its all viewed in Flash®.

To set it up you need ClearBI and a J2EE Application Server, which is typically a PC (or Server) running Apache® Tomcat® and Adobe® LiveCycle® Data Services. (there are free licenses, trials or otherwise, for all the software you need. see respective links)

To make it work you create an ODBC Data Source (DSN) for your MYOB data file (using MYOB-ODBC Driver) and then configure ClearBI via the familiar dialogue box. ClearBI then automatically produces the Adobe® Flash® files (.swf) and places them on your J2EE Server ready for immediate use. Users can log in and create new derivative reports and charts literally out of the box.

15 Minutes to MYOB Reports and Charts in your Web Browser:
Adobe Flex developers can set up ClearBI to work with MYOB by following this routine:

This assumes you have the system requirements in place - see J2EE requirements below

1. Crate a new ODBC Data source using MYOB-ODBC drivers.
2. Configure the Sun JDBC-ODBC Driver in Eclipse > Preferences > Farata > Datasource <New Database Type> as follows:Name: Sun
   Description: Sun
   Driver classname: sun.jdbc.odbc.JdbcOdbcDriver
   Default URL: jdbc:odbc:{MYOB data source name};UID={me};PWD={secret}
   
   
3. Create a new Java project in Adobe Flex®.
4. Invoke Clear Data Builder on the new Java Project.
5. Add the standard project configuration settings as described in the ClearBI documentation
6. In Clear Data Builder > Configure Build, add Database Profiles for the report Meta-Data and for the MYOB data file: Create Datasource 1:
   name: jdbc/clearbi
   Database: MySQL5
   ConnectionURL: jdbc:mysql://locahost:3306/clearbi
   User: {me}
   Password: {secret}

   Create Datasource 2:
   name: jdbc/myob
   Database: Sun
   ConnectionURL: jdbc:odbc:myob (where myob is your MYOB-ODBC DSN name)
   User: Administrator
   Password: {blank }

6. Create new Java Class assemblers with simple SQL statements, a method and a destination for all of the MYOB tables you want to query e.g. Sales, Accounts, etc (see ClearBI Documenation for examples)(note: you can ‘discover’ the MYOB ‘table name’ easily by using a desktop application such as MS Access® to connect to the MYOB DSN and create an MS Access® database from the MYOB database. Then simply identify all the MYOB ‘table names’ and their corresponding Indexes (Key Columns).
   

Here is my example MYOBAccounts.java (class) - It’s just a simple ClearBI RAD assembler statement for my MYOB Accounts table.

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

package com.st.project;
import java.util.List;

/**
* @daoflex:webservice pool=jdbc/myob
*/
public abstract class MYOBAccounts {
/**
* @daoflex:sql
* sql=:: select * from Accounts
* ::
* transferType=MYOBAccountsDTO[]
* keyColumns=AccountID
* updateTable=Accounts
*/

public abstract List getMYOBAccounts();
}

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

7. Create a new Flex® Project in Eclipse and link it to your Java Project (see ClearBI documentation for how to do this).
8. Select Build All in Clear Data Builder menu.

That’s it! Now you can preview and edit all your MYOB reports in ClearBI, create charts, beautify the reports, and then publish them as Templates to your J2EE Server. Users can log in using their web browser and create derivative works from those templates and publish them down the line.

MYOB Accounts table shown above in Adobe® Flash Player

Here are the Development and J2EE Server System Requirements:

• J2EE Application Server (e.g. Tomcat®, Weblogic®, JRun®, etc)
• Sun® JDK 1.5.0_11
• Adobe® LiveCycle® Data Services
• ClearBI Developer or Server Edition withClear Data Builder
• Adobe® Flex® (ideally using Eclipse® SDK)
• MYOB-ODBC Driver (documentation) (MYOB-ODBC driver ships with MYOB CD)
• MYOB (data file)
• Flash® Player 9
  

Next week I will be developing a standard UI reporting interface for MYOB using ClearBI that will hopefully include a bunch of standard reports and charts for any standard MYOB install. ®