Money Manager EX & OpenOffice.org (SQLite ODBC)

11 posts
Money Manager EX & OpenOffice.org (SQLite ODBC)

Nikolay
MMEX Developer

Posts: 2282
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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.
Attachments
Query1.png
(17.94 KiB) Downloaded 15413 times
Re: Money Manager EX & OpenOffice.org

Nikolay
MMEX Developer

Posts: 2282
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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.
Attachments
Query2.png
(3.59 KiB) Downloaded 15390 times
Re: Money Manager EX & OpenOffice.org (SQLite ODBC)

Nikolay
MMEX Developer

Posts: 2282
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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
Re: Money Manager EX & OpenOffice.org (SQLite ODBC)

madhan
Site Admin

Posts: 247
Joined: Sun Nov 30, 2008 2:06 pm
Very cool, Thanks!
Re: Money Manager EX & OpenOffice.org (SQLite ODBC)

omalleypat
MMEX Developer

Posts: 164
Joined: Tue Jul 28, 2009 4:34 pm
Location: Atchison, KS
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.
Ubuntu SQLite ODBC Driver

Nikolay
MMEX Developer

Posts: 2282
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
sudo apt-get install sqlite3
apt-get install libsqliteodbc
sudo apt-get install unixodbc unixodbc-bin

$> ODBCConfig
Re: Ubuntu SQLite ODBC Driver

OKOTORA
New MMEX User

Posts: 2
Joined: Mon Mar 28, 2011 1:53 pm
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]
Re: Money Manager EX & OpenOffice.org (SQLite ODBC)

Nikolay
MMEX Developer

Posts: 2282
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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
Re: Money Manager EX & OpenOffice.org (SQLite ODBC)

Jroonk
New MMEX User

Posts: 4
Joined: Wed Feb 16, 2011 1:12 pm
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.
Re: Money Manager EX & OpenOffice.org (SQLite ODBC)

FreddieGlover
New MMEX User

Posts: 1
Joined: Mon Jul 08, 2013 10:47 pm
Location: 50 Felix Lane,Shorne Ridgeway.
If you having problem problem opening .emb file then you should firstly try to recognize the program that can open the file and Check your computer system for invalid registry entries and other speed issues. If this doesn't work then your should look experts help.

cheap business phone calls
Who is online

Users browsing this forum: No registered users and 1 guest