Recommended way to include a pie chart and table in a report
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
However between the time I posted the question and the time I got your response I played around and developed a way to take the table data out of the clipboard into an Access table and then I created a report which included the exploded pie chart I needed (in MS Access format). The pie chart is not quite as good as the MS Excel version (because I cant rotate it) but I think it's good enough for what I want to achieve.
On giving it further thought I am starting to think that what I really need to do is actually put it into a Word document rather than a report so that the user can fine tune the finished product if necessary. Tha't probably going to require another question to EE.
Thanks again for your comment. It helped me confirm that my thinking was not a silly solution.