How do I get the subset filter string from a pivotchart?

  • Thread starter Thread starter jpimp169
  • Start date Start date
J

jpimp169

I have a form with a bunch of checkboxes representing fields in a table.
Next to this form is a subform restricted to a pivotchart view. When a user
clicks up to 5 of these categories, the categories are inserted into the
filter dropzone in the pivotchart using an "apply filter" button. In the
filter dropzone area, one can select or deselect members of the value list
associated with each category/field. These selections will affect the 3
Min/Ave/Max areas locked in the series dropzone.

What I need to get via VBA code, is the string expression (or however it's
stored) representing the unique filter applied in the filter dropzone area.
(including subset of categories bound by what values in each value list the
user has selected) If I can't get this, I at least need to know the specific
range the applied filters are working on. (this range is used to calculate a
statistical Z-Table for use in percentile calculations since the data does
not follow a standard distribution)

some of my current code is:
Global subFrm As Access.SubForm
Global objChartSpace As chartSpace
Global chConstants
Global dzFilterDropZone

Set objChartSpace = subFrm.Form.chartSpace
Set chConstants = objChartSpace.Constants
Set dzFilterDropZone = objChartSpace.DropZones(chConstants.chDropZoneFilter)
objChartSpace.DisplayFieldButtons = True

'dropZoneArray is calculated via which categories are passed to the subform
'it looks something like ("education", "job title", etc)
objChartSpace.SetData chConstants.chDimFilter, 0, dropZoneArray

segAveSal =
objChartSpace.Charts(0).SeriesCollection(2).Points(0).GetValue(chDimValues)

stdSal =
objChartSpace.Charts(0).SeriesCollection(5).Points(0).GetValue(chDimValues)

thanks in advance
 
As an update...I found out how to get to the selectable value list in each
filter dropzone with the following code as an example. (in particular, this
is the first value in the first filter of the dropzone) However, I see no
way to determine whether the value is enabled (checked) or disabled
(unchecked)

The closest possible thing that may work is utilizing the IncludedMembers
and ExcludedMembers properties, but I have yet to get it to work. (keep
getting type mismatch errors and I'm not sure if there's even anything in
these properties) I'm assuming these should get filled in by the pivotchart
once the AllIncludeExclude property = 1. (meaning that something has been
deselected in the list)

Hope someone out there can help...this seems to be a rather obscure topic
through google and microsoft. (btw, I'm using OWC10)

Dim sName

sName =
objChartSpace.Charts(0).SeriesCollection.PivotAxis.Data.FilterAxis.FieldSets(0).Member.ChildMembers(0)
 
Back
Top