[Solved]Looking for BO Report Generator

[Rhapsody]

Edit: See my 2nd post on using ODBC connector with MS Access

 

I'm looking for a module, add-in or stand alone program that would allow me to generate custom reports using the Mysql database. The intention would be for some custom queries to be built and saved so that an employee could log in the BO and run the structured reports. Ideally, the report building would have a GUI drag and drop interface, similar to building a query in MS Access. Here are some typical report scenarios:

 

1. Run a customer list in alphabetical order that includes mailing address, phone numbers and email for all customers in a Group. Great benefit if the report displayed in multiple columns.

 

2. Run a report that outputs all customers who bought product X. When product X has attributes or combinations, include these in the report. Ideally this would be output to a file (CSV or Excel) for manipulation

 

3. Run a report that compares the sales by product(s) or category between two specified periods of time (e.g. - sales of a product(s) during 1st qarter current year compared to 1st quarter last year.

 

Is anyone aware of a module, add-in or external program that will permit this?

Edited September 20, 2012 by Rhapsody (see edit history)

[Rhapsody]

I'm going to call this solved. I have downloaded the ODBC Connector that allows Microsoft Access to connect with a mysql database. This provides a great capability to do any of the custom queries of the Prestashop data that I want. Here are the basic steps for anyone who would like enhanced reporting capability.

 

1. Setup a read only user account for your Prestashop mysql database. If you have the Linux cPanel interface, you only need to assign "select" access for read only.

2. Enable remote mysql access using your cPanel - I setup a single IP for mapped to my assigned connection at home.

3. Download and install the appropriate ODBC connector for your operating system and version of MS Access. I used 32 bit version for Access running on Windows 7. Note that even though my Windows 7 is 64 bit, the version of Office i use is 32 bit. This took me an entire day to troubleshoot!

4. There are directions available on the same site where you download the ODBC driver on how to configure it and access your remote mysql database

5. Once you have installed and configured the ODBC connector on your local machine, you can run MS Access and make a connection. Depending on your version of Access will determine the menu's you go through to "Get External Data". When setting up to retrieve external data you have the option to either Import or Link tables. I use Link tables and make sure that I save the password for each table in the setup.

6. You can selectively link whatever tables you need in MS Access, then create and save queries and reports. You can also easily export data to Excel or other formats.