Solved

Custom order of pivot items

Posted on 2014-01-26
6
1,028 Views
Last Modified: 2014-01-29
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("SR4")
        iNumSppRows = WorksheetFunction.CountA(Range("J" & iSumRowStart & ":J80")) - 2 'remove total row
        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").ShowDetail = False
            .PivotFields("Species").Orientation = xlHidden
            .PivotFields("Species2").AutoSort xlDescending, _
        "Sum of BF_Total", ActiveSheet.PivotTables("SR4").PivotColumnAxis.PivotLines(1) , 1
     '      .PivotFields("Species2").PivotItems("Group1").Position = 16 '  PROBLEM
            .PivotFields("Species2").PivotItems("Group1").Caption = "Other"
     
         End If
   '********************************************
   End With
0
Comment
Question by:RJT_VT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 43

Expert Comment

by:Rob
ID: 39816430
Unfortunately I don't believe this is possible.  have you seen otherwise?

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.
0
 

Author Comment

by:RJT_VT
ID: 39816621
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.
0
 
LVL 43

Expert Comment

by:Rob
ID: 39816783
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 250 total points
ID: 39817336
Probably easier to reverse what you are doing and move each of the original top 10 items back to position 1 in reverse order:
   Dim n As Long
   Dim vSortData

   With PT   'ActiveSheet.PivotTables("SR4")
      .PivotFields("Species").AutoSort xlDescending, .DataFields(1).SourceName, .PivotColumnAxis.PivotLines(1), 1
      With .RowFields(1).DataRange
         vSortData = .Cells(1).Resize(10).Value
         If .Count > 11 Then .Cells(11).Resize(.Cells.Count - 10).Group   'group the >10 spp into an OTHER group
      End With
      ' 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("Species").Orientation = xlHidden
      With .PivotFields("Species2")
         .PivotItems("Group1").Caption = "Other"
         ' move each of the top 10 back to position 1 in reverse order
         For n = UBound(vSortData, 1) To LBound(vSortData, 1) Step -1
            .PivotItems(vSortData(n, 1)).Position = 1
         Next n
            
      End With
      '********************************************
   End With

Open in new window

0
 

Author Closing Comment

by:RJT_VT
ID: 39817648
Worked like a charm. Thank you for an elegant solution that both solves the problem and adds measurably to my knowledge!
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39817822
Glad to help. :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question