View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Sajith Sajith is offline
external usenet poster
 
Posts: 3
Default Pivot Table problem

hi,

I want to programattically un select some of the items in
the child items of a dimension in pivot Table. So for this
I have written a query. But unselecting part, visible
property is not working.

Tried to update "pi.Visible" = False when even year =
2003 . But it is not updating. Seems like visible
property of pivot items is read only. Always the value is
true. When i checked microsoft site, its written visible
type is read/write boolean. where I am going wrong ?

Or Visible property of Pivot items will not work for OLAP
data source (my data source is poiting to analysis
Manager) ??

Thanks in advance for any help regarding this.

Sajith (code is below...)



Dim pf As PivotField
Dim pfmonth As PivotField
Dim pi As PivotItem
Dim Pimonth As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pf = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]")

Set pfmonth = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Month]")
Worksheets("Sheet2").PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]").PivotItems
("22/09/2003").Visible = False


'For Each Pimonth In pfmonth.ChildItems
' If Pimonth.ChildItems = "01/01/2004" Then
' End If

'Next Pimonth
Application.ScreenUpdating = True
Application.DisplayAlerts = True

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If IsDate(pi.Caption) Then
If Year(CDate(pi.Caption)) = 2004 Then
pi.Visible = True

Else
pi.Visible = False

End If
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True
ActiveSheet.PivotTables("PivotTable1").RefreshTabl e