Custom order of pivot items
Dear Experts,
I have a simple pivot table that is generated by code with a user-chosen data set. Since I want to print the result along with other tables, I need to constrain the number of rows to a fixed number. My strategy is to sort the PT by the single data field (a sum, descending), then group the pivotitems that exceed a certain number of rows into an "other" category, My problem is I'd like to retain the descending sort based on the data field, but force "other" to always be at the bottom row. I know I can set the pivotitem.position, but I can't seem to understand how excel assigns item positions, and their relationship to the ordinal position in the list.
Here's a snippet:
With PT 'ActiveSheet.PivotTables("
iNumSppRows = WorksheetFunction.CountA(R
iLastSppRow = iSumRowStart + iNumSppRows 'store a pointer to the last row
If iNumSppRows > 11 Then
Range(Cells(iSumRowStart + iMaxListSize, 10), Cells(iLastSppRow, 10)).Select '10 is that max spp well accept
'store the list of spp in a range, then group
Selection.Group 'group the >10 spp into an OTHER group
' NB The distinctions between Species and Species2 below.
'Hide Species, collapse species2, rename group1
'And finally, move it to the bottom of the species list
.PivotFields("Species2").S
.PivotFields("Species").Or
.PivotFields("Species2").A
"Sum of BF_Total", ActiveSheet.PivotTables("S
' .PivotFields("Species2").P
.PivotFields("Species2").P
End If
'*************************
End With
I have a simple pivot table that is generated by code with a user-chosen data set. Since I want to print the result along with other tables, I need to constrain the number of rows to a fixed number. My strategy is to sort the PT by the single data field (a sum, descending), then group the pivotitems that exceed a certain number of rows into an "other" category, My problem is I'd like to retain the descending sort based on the data field, but force "other" to always be at the bottom row. I know I can set the pivotitem.position, but I can't seem to understand how excel assigns item positions, and their relationship to the ordinal position in the list.
Here's a snippet:
With PT 'ActiveSheet.PivotTables("
iNumSppRows = WorksheetFunction.CountA(R
iLastSppRow = iSumRowStart + iNumSppRows 'store a pointer to the last row
If iNumSppRows > 11 Then
Range(Cells(iSumRowStart + iMaxListSize, 10), Cells(iLastSppRow, 10)).Select '10 is that max spp well accept
'store the list of spp in a range, then group
Selection.Group 'group the >10 spp into an OTHER group
' NB The distinctions between Species and Species2 below.
'Hide Species, collapse species2, rename group1
'And finally, move it to the bottom of the species list
.PivotFields("Species2").S
.PivotFields("Species").Or
.PivotFields("Species2").A
"Sum of BF_Total", ActiveSheet.PivotTables("S
' .PivotFields("Species2").P
.PivotFields("Species2").P
End If
'*************************
End With
ASKER
I have seen a solution that suggests one can sort all pivotitems in a recordset to reorder the position, but the description was cryptic. Hard to believe you can't replicate what takes two seconds with a mouse!
This needs to be distributed, so qlikview won't work.
This needs to be distributed, so qlikview won't work.
Qlikview is a distributed system but we won't go down that path and too big a learning curve...
If you can do it via the mouse, have you tried recording a macro while you're doing it? I know the number of rows will vary but that's easily fixed.
If you can do it via the mouse, have you tried recording a macro while you're doing it? I know the number of rows will vary but that's easily fixed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Worked like a charm. Thank you for an elegant solution that both solves the problem and adds measurably to my knowledge!
Glad to help. :)
When it comes to data warehousing and business intelligence, excel is very limited. You may be interested in Qlikview (http://qlikview.com) a very simple and easy to use reporting tool.