Money Manager EX & OpenOffice.org (SQLite ODBC)

[Nikolay]

Hi,

I want to show how to use OpenOffice.org with the odbc connection to mmex DB.

1. Add two representations of your database, as described here: viewtopic.php?f=12&t=533
2. You must install SQLite ODBC Driver from here: http://www.ch-werner.de/sqliteodbc/
3. Then you can configure odbc connection: Control Panel-Administrative Tools-Data Sources (ODBC)-user DSN-Add.
Select from the list SQLite3 ODBC Driver.
Data Source Name: My connection 2009
Database Name: C:\Users\Me\budget2009.mmb
The values of other fields can not be changed.
4. Run OpenOffice Base. Steps: 1 Select Database:Connect to an exiting Database: ODBC. [Next].
2.Select OBDC Connection [Browse] Choose "My connection 2009" [Finish]
Save "New Database" to any folder that you wish.
5. Choose Tables, Right Click on alldata, Database, Properties. Additional Settings: Charicter Set: Unicode (UTF-8) [OK]
6. Choose Queries, Create Query in SQL View...
Copy/Past the code:

Code: Select all

select a.AccountName,  a.id, a."Date", a.Amount, c.currency_symbol,
a.Category, a.Subcategory, a.notes
from alldata a left join currencyformats_v1 c on a.currencyid=c.currencyid
where AccountName='Cash'
and a.TransactionType<>'Transfer'
order by a."date" ,"a"."id"

Change AccountName='RealAccountInYourDB'
Run query to check the reseved data is OK. Then save the Query as Query1. Menu:file-close.
7. Now Right Click on "Query1" in the OpenOffice.org Base window and choose Copy
8. Past the data from clipboard to an OpenOffice.org Calc.
9. The END

Addition info: http://wiki.services.openoffice.org/wik ... Office.org
PS As a result, you can get excellent csv file for import into another program, for example.

[Nikolay]

To see your Income vs Expence on diagram for year:

Create new query:

Code: Select all

SELECT "year", "month", "TransactionType", SUM( "amount" ) FROM "cumulative" WHERE ( "year" = '2009' ) GROUP BY "year", "month", "TransactionType" ORDER BY "TransactionType", "month"

Create new OpenOffice.org Calc file then press F4.
Find the Query1 in the Data Sourses screen, then Drug&Drop it to the A1 cell.

For the cell E2 set =-1*D14 then stretch this value to the cell E13.
Now select area from the cell D2 to E13. Menu:Insert - Chart.
Choose the type of chart to taste.

[Nikolay]

This query returns the names of the months and the total amounts of the deposit and the withdrawal for these months. Used a special syntax for OOO.

Code: Select all

SELECT  y."year", "m"."month_name"
,ifnull(-( select SUM( "a"."amount"*a.BaseConvRate) from alldata a where a."month"=m.seq and a."year"=y."year" and "a"."TransactionType"='Withdrawal'),0) as Withdrawal 
, ifnull(( select SUM( "a"."amount"*a.BaseConvRate) from alldata a where a."month"=m.seq and a."year"=y."year" and "a"."TransactionType"='Deposit'),0) as Deposit
FROM ( SELECT DISTINCT 'Январь' AS "month_name", 1 AS "seq" FROM "alldata"
UNION SELECT DISTINCT 'Февраль', 2 FROM "alldata"
UNION SELECT DISTINCT 'Март', 3 FROM "alldata"
UNION SELECT DISTINCT 'Апрель', 4 FROM "alldata"
UNION SELECT DISTINCT 'Май', 5 FROM "alldata"
UNION SELECT DISTINCT 'Июнь', 6 FROM "alldata"
UNION SELECT DISTINCT 'Июль', 7 FROM "alldata"
UNION SELECT DISTINCT 'Август', 8 FROM "alldata"
UNION SELECT DISTINCT 'Сентябрь', 9 FROM "alldata"
UNION SELECT DISTINCT 'Октябрь', 10 FROM "alldata"
UNION SELECT DISTINCT 'Ноябрь', 11 FROM "alldata"
UNION SELECT DISTINCT 'Декабрь', 12 FROM "alldata" ) m , (select distinct 2009 as "year" from alldata ) y
order by m.seq

[madhan]

Very cool, Thanks!

[omalleypat]

I'd like to add to this the instructions for doing the same thing on a mac.

1. Install ODBC administrator for Mac from Apple.
2. Install Sqlite ODBC Driver for Mac from here. Make sure to follow the README instructions to configure the driver properly.
3. In ODBC administrator, go the the User DSN tab and click add. The driver SQLite should appear. If not, make sure you followed Step 2. For Data Source Name, put mmex and for description, put MMEX DB. Click OK.
4. Next, you need to add the Key/Value pair to point to the DB. For some reason, I could not do this from the ODBC administrator program directly. Edit the file at ~/Library/ODBC/odbc.ini to look like this:
   

   Code: Select all

   [ODBC]
Trace         = 0
TraceAutoStop = 0
TraceFile     =
TraceLibrary  =

[ODBC Data Sources]
mmex = SQLite

[mmex]
Driver      = /usr/lib/libsqlite3odbc-0.83.dylib
Description = MMEX DB
Database = /Users/pom/Library/mmex/database_ooo.mmb


   
   But change Database to the path of your database.
5. From here, you can follow Nikolay's instructions from step 5.

[Nikolay]

sudo apt-get install sqlite3
apt-get install libsqliteodbc
sudo apt-get install unixodbc unixodbc-bin

$> ODBCConfig

[OKOTORA]

The site SQL Reports has a great SQL tutorial. Highly recommended for people just getting started on SQL selects.

Sql tutorial
[url] http://www.sql-reports.net/2011/03/sql- ... rials.html
[/url]

[Nikolay]

OOO/Libre office has some restrictions in sql editor!
We can't use without doudle qoutes:
- t as alias (h, for example, we can). I did'n check other letters.
- date, time - strftime(('%Y-%m-%d should be used instead date function
- case should be used as:
case when something thenresult1 else result2 end

[Jroonk]

I have it all set up correctly, but my file is an encypted emb file with password. How do I open emb files and specify the password? It is connecting but there are no tables visible and that shows up in an error.

Thanks.