Unchecking pivot rowfileld's items dropdown filter ?

narendramaruti

New Member
Joined
Dec 21, 2009
Messages
4
Hi,
I have a pivot table that extract data from OLAP Cube. In the RowFields ,there is a dropdown list contains DATE values. I am trying to hide dates that are not in current year. But when i am using to find the list of values that are in DropDown choice , the following code acually returning the values that are actually displaying on the sheet rather than the values in the dropdown.

Code:

For Each pvtField In pvtTable.RowFields
Debug.Print pvtField.PivotItems.Count
'Debug.Print pvtField.Name
For Each itm In pvtField.PivotItems
strCompareString = Replace(Replace(Right(itm, Len(itm) - InStr(1, itm, "&", vbTextCompare)), "[", ""), "]", "")
For jYears = 2003 To 2008 ' DataAvailableYear to CurrentYear - 1
For jMonths = 3 To 14
DtSerial = DateSerial(jYears, jMonths - 1, 1 - 1)
InputDate = DtSerial '"2009-01-31"
cntDiff = DateDiff("d", InputDate, DateValueOfConstant)

subStr = CStr(DateConstant - cntDiff)
'myStr = "[SOLD-DATE].[SOLD-DATE].&[" + subStr + "]"
'Debug.Print InputDate & " ---" & subStr
'Hide old years data
If (subStr = strCompareString) Then
ReDim Preserve arrSoldEndOfMonth(iTmp)
Debug.Print InputDate & " ---" & subStr
arrSoldEndOfMonth(iTmp) = "[SOLD-DATE].[SOLD-DATE].&[" & subStr & "]"
iTmp = iTmp + 1
End If
Next jMonths
Next jYears
Next itm
Next pvtField

On Error Resume Next
Debug.Print iTmp
'pvTable.ManualUpdate = True
pvtField.CubeField.EnableMultiplePageItems = True

ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[SOLD-DATE].[SOLD-DATE].[SOLD-DATE]").HiddenItemsList = _
arrSoldEndOfMonth()

I want to hide (uncheck) the dates in the DATE drop down which are not in current year.
Please help.
 

Forum statistics

Threads
1,082,441
Messages
5,365,539
Members
400,837
Latest member
ELMST616

Some videos you may like

This Week's Hot Topics

Top