PivotTable filter ordering

[Michael Anderson]

Hi There

I have a pivot table based on a cube. At the moment the database behind the
cube has dates until the end of this year in descending order. When the data
is loaded into a pivottable and the dates are selected as a report filter,
they are not in descending order. I would also like a way to stop the dates
in the future from being displayed in the report filter.

1. Timothy Zapawa’s book “Excel 2007 Advanced Report Development†pp276 said
I could drag that field into the row labels or column labels area, sort it
there, then drag it back to the report filter. This hasn’t worked.

2. Another option was to apply filters by manually adding the cube field to
the pivottable (see http://msdn2.microsoft.com.en-us/library/bb256319.aspx
and http://ssas-info.com/RamunasBalukonisBlog/?p=25)

First of all, I’ve needed to get a list of all of the programmatic field
names for the pivottable. This didn’t work because the cube based fields
aren’t referred to as PivotFields …(Application Error 1004)

Sub List_PvtFields()
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim objNewSheet As Object
Dim intRow As Integer

Set objNewSheet = Worksheets.Add
objNewSheet.Activate
intRow = 1

Set pvtTable = ActiveSheet.PivotTables(1)
For Each pvtField In pvtTable.PivotFields
objNewSheet.Cells(intRow, 1).Value = pvtField.Name
intRow = intRow + 1
Next pvtField

End Sub

The following did work…

Sub list_cube_fields()
Dim objNewSheet As Object
Dim intRow As Integer
Dim objCubeFld As Object

Set objNewSheet = Worksheets.Add
objNewSheet.Activate
intRow = 1
For Each objCubeFld In Worksheets("DailyGraph").PivotTables(1).CubeFields
objNewSheet.Cells(intRow, 1).Value = objCubeFld.Name
intRow = intRow + 1
Next objCubeFld
End Sub

Next I followed (see http://msdn2.microsoft.com.en-us/library/bb256319.aspx)
andI think that the following line worked….


ActiveSheet.PivotTables("ptDailyGraph").CubeFields("[Date].[Year]").CreatePivotFields

So this should have added this cube field as a pivottable field. But this
next line doen’t work…(I get “RunTime Error 438 – Object does not support
this property or methodâ€)


ActiveSheet.PivotTables("ptDailyGraph").PivotField("[Date].[Year]").VisibleItemsList = Array("[Date].[Year].&[2006]", "[Date].[Year].&[2007]")

I’ve also tried this line…and got the same error message…


ActiveSheet.PivotTables("ptDailyGraph").PivotField("[Date].[Year]").AutoSort
xlDescending

Any ideas?