looking for Excel-based report generator (SQL)

[ginahoy]

A friend is helping me develop a web-based interactive customer form, sorta like what H&R Block uses for self-service tax forms. The data is stored in MySQL on the hosting server. I need a flexible tool that will allow me to create various custom reports from this data, as needed.

I have some experience creating static reports in Excel and wondered if there are 3rd party tools available that would enable me to write simple scripts to conditionally download data into pre-formated Excel report templates of my design. At least that would save me from learning a new report formatting tool.

Although I'm fairly technical, I have very little experience with SQL statements and scripting languages, so a report generator tool with a graphical front end would be ideal.

OpenSource or freeware would be great, but I'm willing to pay for a product that offers online support via email and/or a dedicated users forum.

Any advice would be greatly appreciated.

BTW, I own MS Access 2k and have looked through a lot of mdb files in a previous job, although I never actually created an Access report. Too be honest, I'm a little intimidated by the prospect of learning Access because it's so much more than I need for this. But I'm open to suggestions.

 

[lalbatros]

I think you should know how to build sql queries.
You can easily learn that in MS Access 2k. But the query will then reside directly in an mdb file on your computer, not on the MySql server.
I think there are also user-friendly tools to build queries directly on MySql.

You can create queries on any excel sheet, based on a table or an existing query or a MSQueryquery.
This generates a table-like list on your sheet, maybe not what you want.

You can also analyse data with pivottables in your excel files, again based on a table or an existing query or a MSQueryquery.
This generates a specific type of report which is extremely powerly to summarize data.
This what I am using the most.
Generally I proceed in two step for more efficiency: first I create a query on an excel sheet, second I create (a lot of) pivot tables based on this query. In this way I reduce the traffic from the server to excel.

More specific reporting is also possible but you probably need to develop your own tool.
I did it myself a few time, but don't like this method.
To do this, again, I buffered the data in a table on an xl sheet by using MSQuery.
Then, it is not so difficult to use lookup excel function to feed specific cells of a report with specific data.
You could even simplify this by writing so excel UDF for this purpose.
Sometimes I did not buffer the data, but used ADO or DAO objects in VBA to retrieve specific data to populate my report. But this is more work unless you develop something re-usable.

I have never heard about tools to help on such a need.
I googled your question and found for example: http://www.freedownloadscenter.com/Business/MS_Office_Add-ins/ExcelReport.html

 

[lalbatros]

postscriptum:

I looked at the sample reports on the web site I mentioned.
If I had to do such kind of reports, I would use the Excel pivot tables.
I have created hundreds of such reports based on pivot tables, and most of them were more complex than those samples.

my preffered examples:
- complex evolution of several storage levels based on simulated stock variations on sqlserver
- occupation of manufacturing equipments as a function of time based on a simulation on sqlserver again
with a possibility to analyse details per products, customers, ... and nice graphics
with color codes for bottlenecks, ...

 

[ginahoy]

I looked at the sample reports on the web site I mentioned.
If I had to do such kind of reports, I would use the Excel pivot tables.

Thanks for your considered advice! I've heard of pivot tables but that's about it. I have Bott & Leonhard's book "Using Office 2000"... I'll do some reading on pivot tables and MS Query. I'm somewhat familiar with query statements, so I should be able to figure this out.

The question is whether it's time well spent, vs. using software that does the work for me. My tendency in past years was to learn whatever I needed to learn achieve an objective. I built my own multi-page website to sell my home, but that experience was enough to let me to know I didn't need to be spending my time learning how to build more complex websites. In this case, I need to be able to create my own reports, rather than rely on my developer every time I come up with a new report.

I found a newer version of ExcelReport, the software you linked to. It's now called XLReportGen, with a newer price of $99! ExcelReport is no longer supported. As you pointed out, it probably doesn't do anything that isn't already possible with Excel.

Unrelated to Excel, someone referred me to Report Manager, an open source program that can do what I need. But it's targeted at developers and is a bit over my head.

 

[lalbatros]

Pivottables is the most useful tool of Excel to learn.