Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem inserting calculated pivot fields into Pivot Table (2007) | Excel Discussion (Misc queries) | |||
Pivot Table in Excel 2007 : Problem with % in Table | Excel Discussion (Misc queries) | |||
Pivot table problem | Excel Worksheet Functions | |||
Pivot table problem | Excel Discussion (Misc queries) | |||
Pivot Table Problem | Excel Discussion (Misc queries) |