Reference PivotChart view properties?

[Wyze]

I can not find anything online about how to reference PivotChart view
properties.

In Microsoft Access 2007 you can open a form or even a query in PivotChart
view.

The four primary properties I would like to set programmatically using VBA
are:

1) Filter
2) Data
3) Series
4) Category

Is this something we have to wait for in the next release of Access?

 

[Krzysztof Naworyta]

Juzer Wyze <[email protected]> napisaÅ‚
| I can not find anything online about how to reference PivotChart view
| properties.
|
| In Microsoft Access 2007 you can open a form or even a query in
| PivotChart view.
|
| The four primary properties I would like to set programmatically using
| VBA are:
|
| 1) Filter
| 2) Data
| 3) Series
| 4) Category
|
| Is this something we have to wait for in the next release of Access?


http://msdn.microsoft.com/en-us/library/aa139960(office.10).aspx

Check reference for "Microsoft Web Components": OWC10.dll, OWC11.dll, ...
(For example: C:\Program Files\Common Files\Microsoft Shared\Web
Components\10\OWC10.DLL)

Not sure if such library exists for access'2007...

 

[Wyze]

OWC is not the solution. Thanks for trying.

FYI you can still go find OWC and reference it if you want to use OWC in
2007, however, I do not recommend developing new code using this.

At this point I am pretty sure Microsoft for whatever reasons did not
provide us with the ability to utilize this object via VBA. The hidden class
or structure does not appear to be made available at this point in time
either by oversight or design.

I can see where developers could get a lot of milage out of access to this
hidden mystery of a class / structure.

Simply write SQL to a Querydef based on user input and then open that
Querydef in PivotChart view using the users default chart of choice with the
data already prepared in the desired "Drag and Drop" area of the chart all
nice and neat via VBA.

Hopefully in future releases it will be included.

Thanks.
Wyze

_______________________________________________________

 

[Krzysztof Naworyta]

Juzer Wyze <[email protected]> napisaÅ‚
| OWC is not the solution. Thanks for trying.
|
| FYI you can still go find OWC and reference it if you want to use OWC in
| 2007, however, I do not recommend developing new code using this.

Why? AFAIK Form object has its property PivotTable that gives you hidden
reference to some OWC...
You need OWC reference only for viewing objects and its properties/methods
in ObjectBrowser while developing.
After that you can remove that reference and replace all declarations to
OWC objects with simple:

Dim pivT as Object 'PivotTable
Dim pivV as Object 'PivotView

 

[Wyze]

My immediate thought about using OWC is that The Office Web Components are
being discontinued because Microsoft needs a more flexible technology to help
customers address the following challenges they face with OWC. This is why
the Office Web Components are no longer installed when installing Microsoft
Office 2007. However, if you are telling me that I can use OWC temporarily to
gain access to the hidden class of PivotView in order to write code then
remove the reference and the code will continue to work by simply making a
couple of minor changes from PivotView to Object etc... then I will certainly
be testing that today as I was not aware of that. The thought that comes to
mind then is when the next upgrade comes will this code continue to function
in 2010 or 2012 etc... If microsoft wanted us to access this hidden class why
would they not simply add that functionality into Access to begin with? Thank
you again in any case for responding. I have been searching for an answer for
some time. I will report back if this works out for me.

 

[Wyze]

Here is a solution to my situation that I have been able to piece together
and get to a rough state using a single form rather than multiples and I do
NOT have to use the OWC reference to make it work. It is incomplete and still
under construction but it may help somebody someplace as it would have helped
me had I been able to find anything closely resembling the following. I do
not know if I will ever be able to get it to work directly with a query in
PivotChart view but I do have it working with a form in PivotChart view
created at run time or it could be used with a previously created form used
over and over. At the very least it is a great starting place for anybody
trying to acomplish what I have spent so many hours searching for. Enjoy!

Note: I am currently researching how to set the Filter "DropZone" data
column value and Series label text value.

Private Sub RunSolution()

Dim objOBJ As AccessObject

For Each objOBJ In CodeProject.AllForms
If objOBJ.Name = "Form1" Then
DoCmd.DeleteObject acForm, "Form1"
Exit For
End If
Next

Dim frmFRM As Form
Set frmFRM = CreateForm
frmFRM.DefaultView = 4
frmFRM.RecordSource = "qryTEST"
DoCmd.Close acForm, frmFRM.Name, acSaveYes
Set frmFRM = Nothing
Set objOBJ = Nothing

Call CreatePivotChart("Form1", "qryTEST", "Category", "Total")

End Sub

Public Function CreatePivotChart( _
ByRef strFormName As String, _
ByRef strQueryName As String, _
ByRef strColumnCats As String, _
ByRef strColumnVals As String)

On Error GoTo CreatePivotChart_Error

Dim objCHR As Object 'ChartSpace
Dim objSER As Object 'Series
Dim objSEG As Object 'Segment
Dim objCON As Object 'Constants
Dim objDZN As Object 'DropZone
Dim objPLA As Object 'PlotArea

DoCmd.OpenForm strFormName, acFormPivotChart, , , , acWindowNormal

Set objCHR = Forms(strFormName).ChartSpace
Set objCON = objCHR.Constants
Set objPLA = objCHR.Charts(0).PlotArea
Set objSER = objCHR.Charts(0).SeriesCollection(0)
Set objSEG = objSER.FormatMap.Segments.Add
Set objDZN = objCHR.DropZones(objCON.chDropZoneSeries)

'*** DIFFERENT CHART OPTIONS ***
'chChartTypeArea
'chChartTypeArea3D
'chChartTypeAreaOverlapped3D
'chChartTypeAreaStacked
'chChartTypeAreaStacked100
'chChartTypeAreaStacked1003D
'chChartTypeAreaStacked3D
'chChartTypeBar3D
'chChartTypeBarClustered
'chChartTypeBarClustered3D
'chChartTypeBarStacked
'chChartTypeBarStacked100
'chChartTypeBarStacked1003D
'chChartTypeBarStacked3D
'chChartTypeBubble
'chChartTypeBubbleLine
'chChartTypeColumn3D
'chChartTypeColumnClustered
'chChartTypeColumnClustered3D
'chChartTypeColumnStacked
'chChartTypeColumnStacked100
'chChartTypeColumnStacked1003D
'chChartTypeColumnStacked3D
'chChartTypeCombo
'chChartTypeCombo3D
'chChartTypeDoughnut
'chChartTypeDoughnutExploded
'chChartTypeLine
'chChartTypeLine3D
'chChartTypeLineMarkers
'chChartTypeLineOverlapped3D
'chChartTypeLineStacked
'chChartTypeLineStacked100
'chChartTypeLineStacked1003D
'chChartTypeLineStacked100Markers
'chChartTypeLineStacked3D
'chChartTypeLineStackedMarkers
'chChartTypePie
'chChartTypePie3D
'chChartTypePieExploded
'chChartTypePieExploded3D
'chChartTypePieStacked
'chChartTypePolarLine
'chChartTypePolarLineMarkers
'chChartTypePolarMarkers
'chChartTypePolarSmoothLine
'chChartTypePolarSmoothLineMarkers
'chChartTypeRadarLine
'chChartTypeRadarLineFilled
'chChartTypeRadarLineMarkers
'chChartTypeRadarSmoothLine
'chChartTypeRadarSmoothLineMarkers
'chChartTypeScatterLine
'chChartTypeScatterLineFilled
'chChartTypeScatterLineMarkers
'chChartTypeScatterMarkers
'chChartTypeScatterSmoothLine
'chChartTypeScatterSmoothLineMarkers
'chChartTypeSmoothLine
'chChartTypeSmoothLineMarkers
'chChartTypeSmoothLineStacked
'chChartTypeSmoothLineStacked100
'chChartTypeSmoothLineStacked100Markers
'chChartTypeSmoothLineStackedMarkers
'chChartTypeStockHLC
'chChartTypeStockOHLC
'****************************************

objCHR.Charts(0).Type = objCON.chChartTypeColumnClustered3D

objPLA.BackWall.Interior.SetSolid "LightGray"
objPLA.BackWall.Thickness = 5
objPLA.SideWall.Interior.SetSolid "LightGray"
objPLA.SideWall.Thickness = 5
objPLA.Floor.Interior.SetSolid "LightGray"
objPLA.Floor.Thickness = 5

' Legend
objCHR.HasChartSpaceLegend = True
objCHR.ChartSpaceLegend.Position = objCON.chLegendPositionTop

' Axes Title (No thanks!)
objCHR.Charts(0).Axes(0).HasTitle = False
objCHR.Charts(0).Axes(1).HasTitle = False

' Categories
objCHR.SetData objCON.chDimCategories, objCON.chDataBound, strColumnCats

' Values
objCHR.SetData objCON.chDimValues, objCON.chDataBound, strColumnVals
'objCHR.SetData objCON.chDimFormatValues, objCON.chDataBound,
strColumnVals

' Add Segment and create divisions in formatting automatically
' with the segment boundaries measured using percentage between
' 0 & 1 and displaying shades of Blue through shades of Red
objSEG.HasAutoDivisions = True
objSEG.Begin.ValueType = objCON.chBoundaryValuePercent
objSEG.End.ValueType = objCON.chBoundaryValuePercent
objSEG.Begin.Value = 0
objSEG.End.Value = 1
objSEG.Begin.Interior.Color = "Blue"
objSEG.End.Interior.Color = "Red"

' Custom color the Dropzone
objDZN.ButtonBorder.Weight = objCON.owcLineWeightMedium
objDZN.ButtonInterior.SetSolid "LightGray"
objDZN.ButtonFont.Size = 14
objDZN.WatermarkBorder.Color = "LightGray"
objDZN.WatermarkFont.Color = "LightGray"
objDZN.WatermarkInterior.SetSolid "Blue"

Set objCHR = Nothing
Set objSER = Nothing
Set objSEG = Nothing
Set objCON = Nothing
Set objPLA = Nothing

CreatePivotChart_Exit:
Exit Function

CreatePivotChart_Error:
MsgBox Err.Description
Debug.Print Err.Description
Err.Clear
Resume CreatePivotChart_Exit

End Function

 

[Wyze]

OK So now I have a few more items to research for this code to do what I want
it to do:

1) How to load the Field List values?
2) How to set the Filter "Drop Zone" column names?
3) How to manipulate that series dropzone better so that it is not just the
value data?

Private Sub CreatePivotChart()

On Error GoTo CreatePivotChart_Error

Dim objCHR As Object 'ChartSpace Object
Dim objSER As Object 'Series Object
Dim objSEG As Object 'Segment Object
Dim objCON As Object 'Constants Object
Dim objDZN As Object 'DropZone Object
Dim objPLA As Object 'PlotArea Object

Dim strFRM As String 'Form Name
Dim strQRY As String 'Query Name
Dim strCAT As String 'Category Column Name
Dim strVAL As String 'Value Column Name
Dim strFIL As String 'Filter Column Name

Dim frmFRM As Form 'Temp Form
Dim objOBJ As AccessObject

strFRM = "Form1" 'Form Name
strQRY = "qryTEST" 'Query Name
strCAT = "Category" 'Category Column Name
strFIL = "Group" 'Filter Column Name
strVAL = "Total" 'Value Column Name

' If temp form exists delete it!
For Each objOBJ In CodeProject.AllForms
If objOBJ.Name = strFRM Then
DoCmd.DeleteObject acForm, strFRM
Exit For
End If
Next

' Create new temp form
Set frmFRM = CreateForm
frmFRM.DefaultView = 4
frmFRM.RecordSource = strQRY
DoCmd.Close acForm, strFRM, acSaveYes
Set frmFRM = Nothing
Set objOBJ = Nothing

' Open new temp form
DoCmd.OpenForm strFRM, acFormPivotChart, , , , acWindowNormal

' Work with the ChartSpace object for our PivotChart view!
Set objCHR = Forms(strFRM).ChartSpace
Set objCON = objCHR.Constants

' Category
objCHR.SetData objCON.chDimCategories, objCON.chDataBound, strCAT

' Value
objCHR.SetData objCON.chDimValues, objCON.chDataBound, strVAL

' Filter???
'Need a line of code here to set filter column value(s)

Set objSER = objCHR.Charts(0).SeriesCollection(0)
Set objSEG = objSER.FormatMap.Segments.Add
Set objPLA = objCHR.Charts(0).PlotArea
Set objDZN = objCHR.DropZones(objCON.chDropZoneSeries)

' Chart Type
objCHR.Charts(0).Type = objCON.chChartTypeColumn3D

' Show Legend
objCHR.HasChartSpaceLegend = True
objCHR.ChartSpaceLegend.Position = objCON.chLegendPositionTop

' Load Field List
'Need a line of code here to load Field List column names

' Hide Field List
objCHR.DisplayFieldList = False

' Hide Axes Title
objCHR.Charts(0).Axes(0).HasTitle = False
objCHR.Charts(0).Axes(1).HasTitle = False

' Hide Series Dropzone Label / Button
objDZN.ButtonInterior.SetSolid "White"
objDZN.WatermarkBorder.Color = "White"
objDZN.WatermarkFont.Color = "White"
objDZN.WatermarkInterior.SetSolid "White"

' Add Segment and create divisions in formatting automatically
' with the segment boundaries measured using percentage between
' 0 & 1 and displaying shades of Blue through shades of Red
objSEG.HasAutoDivisions = True
objSEG.Begin.ValueType = objCON.chBoundaryValuePercent
objSEG.End.ValueType = objCON.chBoundaryValuePercent
objSEG.Begin.Value = 0
objSEG.End.Value = 1
objSEG.Begin.Interior.Color = "Blue"
objSEG.End.Interior.Color = "Red"

CreatePivotChart_Exit:
Set objCHR = Nothing
Set objSER = Nothing
Set objSEG = Nothing
Set objCON = Nothing
Set objPLA = Nothing
Exit Sub

CreatePivotChart_Error:
MsgBox Err.Description
Debug.Print Err.Description
Err.Clear
Resume CreatePivotChart_Exit

End Sub