Set Pivot filter to first available value

[Vivek D]

Looking for VBA code that can help set the filter for a field in a pivot filter to the first available value.
Not sure if it matters, but the pivot field is based on a PowerPivot Model (actually an SSAS model but I guess the syntax would be the same).

When I record a macro, this is what I get where ABC is the first element in the pivot filter. I'm not sure how to make it generic.

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Org].[Account].[Account]").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Org].[Account].[Account]").CurrentPage = _
        "[Org].[Account].&[ABC]"
End Sub

 

[Luke M]

The names may be different for you, but should still be able to use idea of grabbing the 1st PivotItem.

Sub PivotSet()
Dim pt As PivotTable
Dim pField As PivotField
Dim firstItem As String

'What are we working with?
Set pt = ActiveSheet.PivotTables(1)
Set pField = pt.PivotFields("Name")

'Clear filter
pField.ClearAllFilters

'Get first item
firstItem = pField.PivotItems(1)

'Apply filter
pField.CurrentPage = firstItem

End Sub

 

[Vivek D]

Looks like for Power Pivot based pivot tables, the structure is different. The above code gives an error.
"Unable to get PivotFields property of PivotTable class"
Attached a sample workbook.

 

[Luke M]

Hmm. First macro indicates that it does know about PivotFields...we probably just have the wrong name. Try changing the macro like so:

Sub PivotSet()
Dim pt As PivotTable
Dim pField As PivotField
Dim firstItem As String

'What are we working with?
Set pt = ActiveSheet.PivotTables(1)
'UPDATE -- changed named of PivotField
Set pField = pt.PivotFields("[Org].[Account].[Account]")

'Clear filter
pField.ClearAllFilters

'Get first item
firstItem = pField.PivotItems(1)

'Apply filter
pField.CurrentPage = firstItem

End Sub

 

[Vivek D]

Yeah. I did try that but it didn't recognize PivotItems.
"Error: Unable to get the PivotItems property of the PivotField class"

Added a watch on pfield and checked the object details but couldn't find anything that could help get the first item.

 

[Luke M]

Hmm. I'm afraid I'm not sure how to proceed, as I haven't dealt with PowerPivot. Paging @Hui and @NARAYANK991 in case they know the PowerPivot model.

 

[NARAYANK991]

Hi Luke ,

It would help if Vivek could upload his complete workbook saved as an Excel 2010 compatible workbook. Otherwise , a file saved under Excel 2013 cannot be used in Excel 2010.

I don't remember offhand , and having the workbook would have made it easier to get the syntax right.

Narayan

 

[HAMADOU]

On dirait que pour les tableaux croisés dynamiques basés sur Power Pivot, la structure est différente. Le code ci-dessus donne une erreur.
"Impossible d'obtenir la propriété PivotFields de la classe PivotTable"
Ci-joint un exemple de classeur.

Bonjour, merci à tous, le code fonctionne parfaitement. Désolé pour l'absence, j'ai eu un problème avec mon appareil

 

[HAMADOU]

Hmm. J'ai bien peur de ne pas savoir comment procéder, car je n'ai pas traité avec PowerPivot. Paging @Hui et @NARAYANK991 au cas où ils connaîtraient le modèle PowerPivot.

Bonjour, merci à tous, le code fonctionne parfaitement. Désolé pour l'absence, j'ai eu un problème avec mon appareil.
Par rapport au langue je sais comment faire ce Google qui traduit automatiquement mes conversations en français alors que j'ai l'écrit en anglais

 

[vletm]

HAMADOU​

You can always translate those back to English.
Did You comment this time something same as Your previous thread ... Pivottable which was PowerPivot?
... with Crossposting.