Using MS Excel charts with mmex DB mini howto

Page 1 of 1
4 posts
Using MS Excel charts with mmex DB mini howto

Nikolay
MMEX Developer

Posts: 2303
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Hi,

I want to show how to use Excel 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: E:\Developing\budget2009.mmb
The values of other fields can not be changed.
4. Run the program Excel. Data - Import external data - New Database Query
Choose Data Source - My connection 2009* - OK
(Perhaps the message: This data source contains no visible tables.) OK
Query Wizard - Choose Columns - Options... Tick System tables
Choose Cumulative view in "Available Tables and Column:" window (see picture1), then add it to "Columns in your query:" by pressing [>] button.
[Next]
Choose year in "Column to filter" and set - equals
[Next] [Next] [Finish]
[Edit Query]
Microsoft Query should be appears.
View - SQL..
Now replace the SQL Statement to:
Code: Select all
SELECT year, month, TransactionType, sum(amount)
FROM cumulative
WHERE (year='2009')
group by year, month, TransactionType
order by TransactionType, month

(Perhaps the message: SQL Query can't be represented graphically. Continue anyway?) OK
File - Return Data to Microsoft Office Excel
Ok
5. Then in MS Excel add lines for the missing months with zero values.
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.
Chart Type: Bar, Char Subtype: Clustered bar with a 3-D visual effect. [Next] [Next] [Finish]
The result is shown in the figure below.


The result was a graphical representation of income and expenditure for each month of the year.
Attachments
picture1.png
(13.56 KiB) Downloaded 7455 times
picture2.png
(3.38 KiB) Downloaded 7455 times
Re: Using MS Excel charts with mmex DB mini howto

madhan
Site Admin

Posts: 247
Joined: Sun Nov 30, 2008 2:06 pm
This is pretty neat stuff. Way to go Nikolay!
Re: Using MS Excel charts with mmex DB mini howto

zlatan24
New MMEX User

Posts: 1
Joined: Fri Dec 04, 2009 7:44 am
Yesterday I was discovering on the net and saw a lot of things.I acccidentally saw next tool- how to repair xls files of office 2003.To my surprise program recovered all my old damaged excel files.Moreover software made it for free and in seconds.Tool showed how it helps to prevent a broad range of issues that lead to the corruption of Microsoft Excel worksheets.
Re: Using MS Excel charts with mmex DB mini howto

reachalex
Super MMEX User

Posts: 27
Joined: Tue Jul 06, 2010 6:37 pm
Do the above steps continue to work in the latest version of MMEX.
If not what are the additional steps I have to do.

Please note, the first step
"1. Add two representations of your database, as described here: viewtopic.php?f=12&t=533" does not work.
Error the "table alldata already exists"

Do let me know.
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 1 guest

cron