A
amit
I have created a pivottable in vba, here is the code for it:
Set pivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
pivotCache.Connection = Array("OLEDB;Provider=MSOLAP.4;Integrated
Security=SSPI;Persist Security Info=True;Initial Catalog=My Model 2008;Data
Source=mymachine;MDX Compatibility=1;Safety Options=2;MDX Missing Member
Mode=Error")
pivotCache.CommandType = xlCmdCube
pivotCache.CommandText = Array("Common Data Model")
pivotCache.MakeConnection
'pivotCache.MaintainConnection = True
Set pt = pivotCache.CreatePivotTable("Sheet4!R1C1",
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12)
I add pivotfields in pivottable in the following way:
Set cField = pt.cubeFields("[Store].[Store ID]")
cField.Orientation = xlRowField
but when i try to add a filter to the pivotfield i get an error
"Application-defined or object-defined error"
This is how I add filters
pf1.ClearAllFilters
pf1.PivotFilters.Add Type:=xlCaptionEquals, Value1:="0006"
Any idea what I am doing wrong?
Set pivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
pivotCache.Connection = Array("OLEDB;Provider=MSOLAP.4;Integrated
Security=SSPI;Persist Security Info=True;Initial Catalog=My Model 2008;Data
Source=mymachine;MDX Compatibility=1;Safety Options=2;MDX Missing Member
Mode=Error")
pivotCache.CommandType = xlCmdCube
pivotCache.CommandText = Array("Common Data Model")
pivotCache.MakeConnection
'pivotCache.MaintainConnection = True
Set pt = pivotCache.CreatePivotTable("Sheet4!R1C1",
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12)
I add pivotfields in pivottable in the following way:
Set cField = pt.cubeFields("[Store].[Store ID]")
cField.Orientation = xlRowField
but when i try to add a filter to the pivotfield i get an error
"Application-defined or object-defined error"
This is how I add filters
pf1.ClearAllFilters
pf1.PivotFilters.Add Type:=xlCaptionEquals, Value1:="0006"
Any idea what I am doing wrong?