Creating a large number of charts automatically

Discussion in 'ASP General' started by Alan, Sep 1, 2003.

  1. Alan

    Alan Guest

    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
     
    Alan, Sep 1, 2003
    #1
    1. Advertising

  2. Alan

    Alan Guest

    I'm an idiot - I left a follow-up set. Please respond to the re-post - it
    doesn't have the follow-up set.

    Regards,

    Alan


    "Alan" <> wrote in message
    news:#...
    > 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
    >
    >
     
    Alan, Sep 1, 2003
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?UTF-8?B?0JTQsNC80ZjQsNC9INCT0LXQvtGA0LPQuNC10LLR

    PyQT, automatically creating GUI for configuration options

    =?UTF-8?B?0JTQsNC80ZjQsNC9INCT0LXQvtGA0LPQuNC10LLR, Jun 18, 2004, in forum: Python
    Replies:
    2
    Views:
    395
    David Fraser
    Jun 23, 2004
  2. implicate_order
    Replies:
    3
    Views:
    1,142
    implicate_order
    Aug 18, 2006
  3. Alan
    Replies:
    5
    Views:
    157
    Francine Kubaka
    Sep 21, 2003
  4. Replies:
    3
    Views:
    138
  5. Replies:
    1
    Views:
    127
Loading...

Share This Page