I'm trying to programmatically create a stacked bar pivot chart.
Using "Programming Microsoft Office Access 2003" by Rick Dobson, I've
created the chart. However, it doesn't distinguish between the
different values for theseries. Does anyone have any suggestions on
how to create a chart using one column containing three values for theseries?
Essentially, this is a continuation of a previous post:
http://groups.google.com/group/microsoft.public.access.formscoding/br..."
Any help is greatly appreciated.
I figured it out. For those that may be interested, the code (from
the link in the above post) sets data using chDimCategories and
chDimValues. The different values were being pulled, but they weren't
shown. By adding chDimSeriesNames for the chDimValues, I was able to
display the names of the different values from that column in the
chart's legend. Also, I was able to set the desired colors for the
various series by using the seriescollection property.
Using the code from the above link, the below code is an example of
the changes made:
With frm1.ChartSpace
'Open PiovtChart without drop areas, and set its categories and
values
.SetData chDimCategories, chDataBound, "[YearMonth]"
.SetData chDimValues, chDataBound, "ExtPrice"
.SetData chDimSeriesName, chDataBound, "ExtPrice" <---- added
to show names of values
.Charts(0).Type = chChartTypeLineMarkers <---in my case, this
was a stacked bar chart
.ChartspaceLegend = False <---not exact, but something like this
to avoid having 2 legends (chartspace and chart)
'Assign and format titles to axes and overall chart
With .Charts(0)
.Axes(1).Title.Caption = "Sales ($)"
.Axes(0).Title.Caption = "Dates"
.HasTitle = True
.Title.Caption = "Sales By Month"
.Title.Font.Size = 14
.HasLegend = True <---- added to show legend
.SeriesCollection("series 1 name").Interior.Color = vbRed
<--- this isn't exact..just something to give an idea
.SeriesCollection("series 2 name").Interior.Color = vbYellow
<--- this isn't exact..just something to give an idea
End With
End With
That's it. Hopefully, this helps someone and saves time.
