MS Access - dynamically/programatically creating Pivot Charts using Office Chart component
Dear experts, I urgently need of some help from someone who has experience programmatically creating Pivot Charts... I have been working on this for almost a week now searching for examples on line, reading through all of my books etc. but not making enough progress. In addition, I'm hoping for some insight for whether I am taking a good approach to this. I really need some guidance and/or insight from someone with more experience.
Here is what I'm trying to do:
Dynamically create a line chart that displays time series of 2 different measures.
- avg age of problems since enter date
- avg age of problems since transaction date.
X axis will show dates for time range and the values for the 2 measures just mentioned will be plotted for each day over the time series.
User specifies time range on a form - ie.3/29/1/06 - 6/1/06 and the program should generate a graph (either as a form within a report, or as a form that pops up in pivot chart view)
sample date:
DATE AVG_OPEN_PROB_AGE_T AVG_OPEN_PROB_AGE_E
3/29/2006 305 10
3/30/2006 305 10
3/31/2006 306 11
4/1/2006 307 12
4/3/2006 309 14
4/4/2006 310 15
4/5/2006 311 16
4/6/2006 140 7
Pseudocode:
- Dynamically create SQL String
- Open ADO Recordset and obtain data for chart and put into strings
- Open a new form
- add chartspace to form
- add series to chart
- add data to the series, specify categories and values
For some reason my code is not showing the dates properly on the x axis (there are large gaps - doesn't show daily), It appears that the points on the graph are possibly getting summed up?
If I do this manually by, the gap between dates and summed up values doesn't occur
- dropping the date field in the Category Field space
- dropping the other 2 fields in the Totals / detail space
Problem here is 1. It's not dynamic 2. there are too many dates ...... cluttering the x axis.
Any suggestions, help, or guidance will be greatly appreciated. I also have code that I can share as needed.... Thanks in advance for any help you can provide on this!
Here is what I'm trying to do:
Dynamically create a line chart that displays time series of 2 different measures.
- avg age of problems since enter date
- avg age of problems since transaction date.
X axis will show dates for time range and the values for the 2 measures just mentioned will be plotted for each day over the time series.
User specifies time range on a form - ie.3/29/1/06 - 6/1/06 and the program should generate a graph (either as a form within a report, or as a form that pops up in pivot chart view)
sample date:
DATE AVG_OPEN_PROB_AGE_T AVG_OPEN_PROB_AGE_E
3/29/2006 305 10
3/30/2006 305 10
3/31/2006 306 11
4/1/2006 307 12
4/3/2006 309 14
4/4/2006 310 15
4/5/2006 311 16
4/6/2006 140 7
Pseudocode:
- Dynamically create SQL String
- Open ADO Recordset and obtain data for chart and put into strings
- Open a new form
- add chartspace to form
- add series to chart
- add data to the series, specify categories and values
For some reason my code is not showing the dates properly on the x axis (there are large gaps - doesn't show daily), It appears that the points on the graph are possibly getting summed up?
If I do this manually by, the gap between dates and summed up values doesn't occur
- dropping the date field in the Category Field space
- dropping the other 2 fields in the Totals / detail space
Problem here is 1. It's not dynamic 2. there are too many dates ...... cluttering the x axis.
Any suggestions, help, or guidance will be greatly appreciated. I also have code that I can share as needed.... Thanks in advance for any help you can provide on this!
Hello lorincha
From the data sample provided, you probably do not need a pivot chart.
If you simply create a chart form/report, the assistant will attempt to have you create a "totals chart", i.e. applying some domain aggregate functions to your data. This is fine if you want to compute your averages at that point. However, you can also skip that part of the wizard and instead directly enter the name of your query (the one returning your sample data) -- or even just its SQL string -- as row source for the chart.
Then open your chart in design view, paste some sample data in the data grid and adjust the properties you need. For example, make sure that the X axis is chronological, and select relevant major units, minor units, and formatting options to avoid having the axis legend "cluttered up".
What I'm trying to say is that your pseudo code really describes what happens when you open a chart form/report, so I don't see any reason for programming. I believe that event the first step -- creating the SQL string dynamically -- can be avoided with a well formed query.
Please tell us if you need help in trying the simple chart approach, and good luck!
(°v°)
From the data sample provided, you probably do not need a pivot chart.
If you simply create a chart form/report, the assistant will attempt to have you create a "totals chart", i.e. applying some domain aggregate functions to your data. This is fine if you want to compute your averages at that point. However, you can also skip that part of the wizard and instead directly enter the name of your query (the one returning your sample data) -- or even just its SQL string -- as row source for the chart.
Then open your chart in design view, paste some sample data in the data grid and adjust the properties you need. For example, make sure that the X axis is chronological, and select relevant major units, minor units, and formatting options to avoid having the axis legend "cluttered up".
What I'm trying to say is that your pseudo code really describes what happens when you open a chart form/report, so I don't see any reason for programming. I believe that event the first step -- creating the SQL string dynamically -- can be avoided with a well formed query.
Please tell us if you need help in trying the simple chart approach, and good luck!
(°v°)
ASKER
Thanks so much for your reply. Perhaps with the requirements of this project, I don't really don't need much customization of my chart (MS Graph objects) using VBA. I think however that I will still need to dynamically create the sql strings which would still be ok in the event that I choose to not use pivot charts. I was considering Pivot charts only because there seems to be a lot written about MS graph being a "dead end". I have never worked with Charts until now and want to choose the best technology upfront to prevent having to go back and recode or reverse engineer in the future.
Books I've reviewed say that if you intend to use VBA to customized MS Graph objects, to be prepared for a long learning curve. "MS Graph is the epitome of obfuscation" and the pivot chart object model is much easier to work with. Also, Pivot Charts are active X controls so they're web-enabled and programmable with vb script, JavaScript, and vba ...., accept xml encoded data (fitting into Microsoft's .Net framework), can manipulate datacubes generated by sql server's analysis services + more enhancemente in subsequent office upgrades.
I'm going to try as you suggested using MSGraph and see if it will meet the requirements of this project.
Thanks again and if you have any further feedback, please feel free to add it. Best Regards,
lorincha
Books I've reviewed say that if you intend to use VBA to customized MS Graph objects, to be prepared for a long learning curve. "MS Graph is the epitome of obfuscation" and the pivot chart object model is much easier to work with. Also, Pivot Charts are active X controls so they're web-enabled and programmable with vb script, JavaScript, and vba ...., accept xml encoded data (fitting into Microsoft's .Net framework), can manipulate datacubes generated by sql server's analysis services + more enhancemente in subsequent office upgrades.
I'm going to try as you suggested using MSGraph and see if it will meet the requirements of this project.
Thanks again and if you have any further feedback, please feel free to add it. Best Regards,
lorincha
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This has been very helpful ... I sincerely appreciate your input and suggestions. Please take care and enjoy the holidays :-)
Same to you! and good luck with your charts!
(°v°)
(°v°)
ASKER
there are perhaps too many values for the X axis (Category fields) Probably there are almost 100 date values returned from the query (30 days per month * 3). Each date has a corresponding series value that is an AVERAGE.
The chart displayed is not showing all of the dates and appears to be summing up the values of all these averages creating an inaccurate graph.
If anyone has insight, please let me know. Thanks.