?
Solved

Custom order of pivot items

Posted on 2014-01-26
6
Medium Priority
?
1,069 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1000 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

777 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