A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Creating a large number of charts automatically (repost)



 
 
Thread Tools Display Modes
  #1  
Old September 21st, 2003, 09:16 PM
Francine Kubaka
external usenet poster
 
Posts: n/a
Default Creating a large number of charts automatically (repost)

While I may be missing some points, it seems the easiest way would be:

A) Get all normalized data into a SQL server table;
B) Produce datasets with 'select transform' queries;
C) Invoke Excel from SQL server and produce charts;
D) Export charts as bitmaps.

BY maximizing the data processing on the SQL server side and having Excel to
produce the charts only you will save yourself a lot of grief and processing
time.

Cheers,

FK



"Alan" wrote in message
...
Hi there,

Are there Excel charting gurus here?? If so then please read on...

Sorry for the cross-post but I'm not familiar with the Excel groups. I've
posted to asp.general because if I have to code a solution to this it'll
probably be done in ASP on a web server, unless there's a significantly
better way.

I'm looking for a way to create over 100 Excel *charts* programmatically.
We've just run a large online survey and the data is being fed back to us

in
a single spreadsheet - each respondent on a row and their dimensions
(division, department, etc) and each of their responses in columns.

This data has to be sliced by a number of dimensions - this will require
that just over 100 separate charts will have to be created. I'm looking

for
a way to create these charts in the most efficient way. I'm happy to
normalise the data and load it into SQL Server, and can then easily

extract
each required subset. The output of the extract process will probably be
spreadsheets in TSV format.

Last time I had to graphically report this sort of data I loaded it into a
mart and used PivotCharts. The owners of they survey don't need the
flexibility of Analysis Services so I'm happy to pre-define the charting
requirements if it'll mean I'm able to create these charts automatically.
Manipulating 100 PivotCharts was too much for them. Each chart will
ultimately be fed back to users in a Word document or Powerpoint
presentation with the appropriate analyses.

How do I then create a chart for each subset? I've thought about (but not
yet investigated) the following options:

1. Try to create a chart based on named-ranges in Excel, and just open

each
extract and copy and paste the data across. The chart will spring into

life
as the data is pasted. I'm not too sure what will happen if the size of

the
cut and paste areas are different.
2. Write some sort of module in Excel VBA to either access the database

and
create charts for all the subsets automatically, or create a chart for an
individual subset after accepting some sort of input from the user.
3. Use Office Web Components to do the charting on-line. Never used OWC

but
might give it a go if I thought I could create even one web page with

every
chart on it. I'd have to look at the format of the chart though, and how

I'd
get each chart back into Word and/or Powerpoint.
4. Another mart, but perhaps using something like ThinSlicer to create the
charts online.
5. Pay a student $15/hr to manually create a chart for each subset.
6. Include some code in each Word document that manages to create the

chart
object automatically when the report is opened on the network. This would
require the entry of custom parameters for each unit, or a custom query

for
each, and unless the chart is somehow cached, would only display if the
report is opened with access to the network/database. OTT?
7. Something else I haven't yet thought of.

Any ideas? I be really grateful for thoughts, links to tutes/HowTos etc.

Regards,

Alan






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.