Using MySQL VIEW and ODBC Connector to let Excel Users Create Custom Reports

Although MySQL 5.1 is in Release Candidate stage and apparently nearly ready for release, I'm only now getting around to looking at the new features in MySQL 5.0 which has been around for a while now. As an old MySQL 3 and 4 user, I just never had the need to use the 5 features until now. I was looking at the Open Source dotProject project management software running on a LAMP architecture (Linux, Apache web server, MySQL, PHP) and wanted to let end-users easily use data from it to generate custom reports. So, I used a feature introduced in MySQL 5 called VIEW (this is a feature that has been long available in most other SQL database engines but is relatively new to MySQL, btw) to create a pre-defined JOINed view of four tables in dotProject. This eliminates the hassle of end-users needing to deal with multiple tables. Then, I installed MySQL Connector/ODBC 3.51.21 on Windows XP and a Windows Vista PCs for testing using Microsoft Excel 2000, 2003, and 2007. I created an ODBC DSN on each PC to access the database using a MySQL user account that just had SELECT and VIEW access (no modification privileges). I created a Microsoft Query file (.DQY file) using Microsoft Excel and verified it worked on that first PC. Then, I took the .DQY (Query) file over to another PC (with a working MySQL ODBC link) and fired it up there. Double-clicking on the query file fired up Excel on that 2nd PC and brought the MySQL data down from the Linux server.

This technique should also work with OpenOffice.org and its millions of forked versions or on Mac OS X, btw. I was pleased to see how well this combination of Open Source (LAMP + dotProject + MySQL Connector/ODBC) worked with proprietary software on the desktop side (Windows + Excel).