Recommended way to include a pie chart and table in a report
Posted on 2011-05-08
I have been asked to prepare a report from a MS Access database that takes data from the database and include a table and pie chart from an external source and I am seeking suggestions on the best way to handle it.
The table data comes from a government web site where I can enter my parameters (suburb) and generate the table. The web page generates a one line header followed by a table with 14 rows and 7 columns, perferctly formatted. It is easy to copy the entire page, paste it into a spreadsheet and then I just select the first row (headings) and last row (12 month totals) to generate a pie chart. I then explode one slice of the pie chart, rotate the pie so that my exploded slice is in the right position and it's ready to use.
Once I have generated the table and pie chart (which covers an entire suburb) I want to keep it so that I can use it for any ad-hoc reports generated for other customers who come from that suburb. The table data only needs to be updated every 12 months or so.
The way I see it I can:
1) try to save the data in the MS Access database and try to get MS to generate the Pie Chart, thought I don't think MS Access 2007 is particularly good at generating pie charts; or
2) I could save the table data and pie chart in a workbook and then write some VBA in my database to open the workbook, search for the required spreadsheet, then somehow copy that data table and chart into the report; or
3) save the data table and chart as images on the computer hard disk and then include a hyperlink to those images in a suburb data base and include those images in my report.
In this question I am not asking HOW to do it - I will ask separate questions if required later - I just want to know what you think would be the best way to approach the project - one of the above 3 options or some other option.