Solved

Custom order of pivot items

Posted on 2014-01-26
6
973 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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.

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now